Verso System stores and handles records of tickets created for products to manage their warranty. Some companies sell a lot of products and therefore need a system capable of displaying all those records. Additionally, the records should be able to be sorted and filtered by 20 possible fields. Verso had to guarantee that it was up to the task of handling over 150000 for a new company that was going to migrate to Verso.
At first Verso wasn’t up to the task and with over 150000 records of dummy data it took more than two minutes to go from one page of ten records to another using the worst sorting/filtering scenario. There was also a dropdown option that had all the number of pages in it and by choosing the last page and waiting for over 3 minutes the records would appear, letting us know that the situation only got worse the further the page was from the initial one.
Because the paging system has a lot of filters and sortings that can be applied to it, the query had to be made dynamically by the programing language. But the real problem was that the query made to the database would return all 150000 records and then they were processed on the front-end. All the filters and ordering statements were handled in the user interface, which was overheating the front-end and making it extremely slow.
The first solution in mind was to throw the problem to the database and just send the records needed to be displayed to the frontend. This helped a little but now the query was terribly slow. Using temporal tables to store the records in a separate way, adding columns with the index number of the record or number of page corresponding to that record, where some of the solutions the development team came up with. Problem was the dynamic filters and sortings methods needed in the web page.
After trying with multiple solutions and making some optimizations to the query that pulled the data, the best solution found was transforming the query to Common Table Expression Form, or CTE Form for short.
CTE Form queries have the advantage that they are recursive and can reference themselves, giving them the ability to work with subsets of data until it completes the query and has all the data requested.
After the implementation of the CTE Form query we uploaded the solution to the web page and still noticed that the rendering of the records took about one minute long. This was unacceptable and a solution had to be found.
At the end, we noticed that the dropdown list that had stored all 15000 page numbers wasn’t rendering fast enough and opted to change it to a simple textbox where the user would type the number of the page it would like to see.
Changing a query to CTE Form is quite simple, given the following example, and assuming you have more than 150000 records on some of those tables:
Running that query would normally take from two to three minutes and it would return all the records to the front-end.
On the other hand, if we change it to CTE Form, we would have it look like this:
As you can see, the important part is the top section:
The logic of a CTE Form query is to take only the IDs of the records needed and apply all the joins just to those records. Having this in mind, on this top section all the filter parameters and order by statements should be put, since this is going to return the IDs of the ten records for which the data is needed. We also highly recommend ordering the records again at the end of the query that performs the joins because the joins could mess up the ordering brought from the top section.
To further explain the top section of the query, the OFFSET instruction tells the query to avoid the number of records sent as a parameter. So for instance if you have 100 records and send OFFSET 10 ROWS that would return records from 20 to 100.
The FETCH instruction tells the query how many rows it should return, so if you have FETCH NEXT 10 ROWS ONLY the top section would only return 10 rows.
CTE Form queries work better if the query has JOINS instead of nested SELECTs, so given the following query:
It would be more efficient to write it as:
- The paging process went from two minutes to one second for any page the viewer would chose, giving the confidence to Verso to finally start negotiations with the company that wanted to migrate.
- User’s experience speed up and henceforth better engagement with the web page.