When we launched our new Lists screens to everyone last month, we mentioned how much "behind the scenes" work went into improving the performance of these critical screens. With many of the new Lists loading 10 times faster than the old ones even though they now have the ability to show so many more columns of data, we thought it would be worth sharing a little more technical detail about it, as well as what's next.
Like any database driven system, Accelo needs to provide our users with the information they want as quickly as possible. With many of our users having tens of thousands of clients, contacts and tasks, along with millions of timesheet entries and custom field values, getting the information a user wants fast means running well-tuned and performant database queries.
When we first started working on the new Lists feature - with the ability to filter and view thousands of different but related fields and columns, we realized that the old way of doing things - specifically crafted queries - wasn't going to work.
Our old List screens had limits on what the user could ask for - mostly they could ask for built-in and custom fields for whatever object they were "listing" (such as the value of a Sale or the due date of a project) but the ability to layer in additional filters for related objects (like custom fields for a Company so you only get results for "VIP" clients) wasn't previously possible.
To provide flexibility while also abstracting the core database structure from the object structures our developers need to know about - allowing for much better flexibility into the future - we created a new object-focused query engine. By focusing atomically on specific objects, we were able to create a query engine which was a lot faster and less computationally wasteful than our "build a query gradually and run for everything" approach. This also allowed for almost complete re-use, so the object to query mapping and tuning we did for, say, the Divisions that a Company is connected to in your Accelo account could be re-used on every list screen that includes a company relationship.
The first part of the new engine was a new way for our developers to access data using abstraction to support the filtering and mapping of characteristics and relationships (the elements to the left of the SQL boundary in the diagram below).
The second part of the new database engine was the completely rebuilt query building, running and returning engineering. This was necessary to allow almost anything to be filtered/selected with lots of complex relationships while remaining highly performant. The diagram below shows how much goes into this, focusing on the right hand side of the SQL interface.
Lastly, to ensure high performance with flexibility, we needed to add new smarts to how objects were related - and accessed - across the application. This major initiative was the creation of an "any to any" mapping model, enabling us to connect together the wide and diverse relationships inherent to a product that runs a service business from quote to cash with incredible flexibility and performance - and critically, ensuring that these relationships remain up to date even while users move around projects, relocate activities or merge records together (to name just a few challenges to gracefully handle).
For more details, see the Mapping part of our Performance Update & Database Improvements blog post.
Our previous lists made heavy use of private REST APIs to provide data to our front end. This approach had some advantages, but it had one major drawback - flexibility. With the move to allowing our users to customize the columns they wanted to see in their list screens, and thus allowing for a lot more options to be presented to users, we needed to take a completely different approach.
The solution we chose is to use GraphQL, a "query language for your API". GraphQL makes it possible to have a very flexible, structured representation of object data and relationships. This makes it much easier to maintain a "user" object structure - with things like firstname, lastname, email and avatar - and then use and enforce this structure in multiple places (eg, on a custom field on a project for "QA Lead" which itself is a user object) within the API experience for the front end that consumes the data. Additionally, and critically for performance on a screen as flexible as our new list screens, is that GraphQL only returns the data the user actually wants to use - unlike the RESTful API approach where objects are returned in high fidelity (and high waste).
By presenting structured data in a way which is flexible and self-documenting, we make it easy for our front-end developers to consume the information - and to interrogate/filter/request the data - without needing to know anything about how to get the data.
The last piece of the new technology stack that supports the new List screens is the front end interface. While we started moving all of our new engineering to ReactJS last year (with the release of our upgraded Navigation), the Lists project was the first really intensive use of ReactJS with a very data heavy and interactive aspect of the product.
Through tight coupling of ReactJS and GraphQL, we have been able to accelerate both the rate of development - across our 7 list screens we now have hundreds and hundreds of filters and columns available - as well as the performance of how that information is presented to users through the browser.
We're excited to continue improving and making use of this new technology stack throughout the Accelo product in the quarters ahead.
While we've been continually improving the Lists experience since we launched a month ago (and we'll continue to do so - we want to have both flexibility with color and visual candy), we're also working on two other initiatives to deliver a better user experience throughout the product using this new technology.
The first is the upgrading of our other List screens to use the new technology; things like Invoices, Activities, Assets and Expenses all need to have the full stack treatment with their own Database Engine and GraphQL representations on the back end, and their own ReactJS driving front-end screens.
The second is that we want to upgrade all of our screens to use the new technology stack. This is a major project, and we'll be focusing our attention on the various View screens in Accelo which collectively account for over 40% of our user's interactions with the product any given day.