Improving Application Performance by Implementing Paginated Lists
Director of Software Development
Summary: Describes how Paylocity has implemented the "How To: Page Records in .NET Applications" section of the Microsoft patterns & practices book Improving .NET Application Performance and Scalability. (8 printed pages)
I've worked on Web applications that target the Global 2000, so my software has had to support from 50,000 to 100,000 employees at a given implementation. However, each company was responsible for hosting their application. The Payroll and Human Resources application I'm working on now for Paylocity employs the ASP model and is hosted at our main office for all of our clients that hire us as a Service Bureau.
Although we target small- to mid-size companies, because we have more than 2,000 clients we have additional pressure from a performance point of view. Small lists that would typically consist of 10 to 100 rows (and hence present little overhead from a performance perspective even if you do a table scan because all of the rows fit into 1 block in the database, so an index won't help) are in a table with 20,000 to 200,000 rows (which can result in a noticeable delay if you do a table scan). Large lists that average 1000 items are suddenly 2 million rows in the database. And items like paychecks—we generate well over a quarter of a million paychecks a month, but each check may have 10 earnings and deduction codes associated with it—generate millions of rows each month.
The last item that I have had to address in my performance and scalability plans is our continued rapid growth. We've been averaging over 750 percent growth for the past 8 years, which is why we made the Inc list of fastest growing privately held firms the past 2 years (our first 2 years of eligibility). When I started a year ago we had 1,500 client companies, today we have 2,000, and we are continuing to be successful. In January 2005 we set a new record for the number of new customers. So when I'm designing my system I have to design a system that will handle growth of 5,000 to 10,000 customers before long.
Given these general concerns, it is imperative to me that I squeeze out every ounce of additional performance anywhere and everywhere I can—which is why I have found the Microsoft patterns & practices book Improving .NET Application Performance and Scalability to be so useful.
With that background in mind, I was determined to do everything possible to keep all result sets down to a minimum, and thus we've implemented the concept of paging throughout the application. The genesis of our code was the sample code provided in the topic titled How To: Page Records in .NET Applications.
We've extended the sample code in this How To so it can handle four very different use cases:
- A simple pagination control for small lists, which we've defined as lists that typically contain fewer than 100 rows per company.
- A paginated drop-down combo box that is to be used anywhere you would normally have a drop-down list box but the number of rows may exceed 100 rows since every record included increases the page size and transmission times for an Internet application.
- A more complex filtered search capability with pagination for medium-to-large lists (medium is considered 100 to 1000 rows per company and large is anything that may contain over 1,000 rows per company)—and includes tables with tens of millions of rows.
- An advanced pagination control that acts as a navigation control bar that allows users to type in characters to find the next match based on the specified sort order or select from a drop-down list. It uses XML HTTP to post back to the page small sets of data and dynamically update the control without redrawing the entire page.
Remarkably, all four use cases are handled by our BasePaging stored proc with exceptional response time. This proc was derived from the Microsoft patterns & practices app_temp example (although we had to extend it to support concepts like sorting in ascending or descending order and being able to apply the same search on different tables with different search criteria).
Simple Pagination Control
For small lists we created a simple pagination control that allows you to select a numbered page, or click on a set of arrows to display the next page, or next set of pages, or skip to the last set of pages.
Including the control is as simple as adding the control:
<Escher:Paging id="paging" PageId="coSetup.coPositions" HidePaginationForSinglePageResults="True" runat="server"></Escher:Paging>
and then calling a generic stored proc (like the app_temp proc example, but ours is dynamic so it an be used for multiple tables) to get the number or rows, pages, and the appropriate set of data associated with the desired page. We calculate the number of rows to return with each page based on the user's screen resolution.
Paginated Drop-Down Combo Box
The amount of data contained on a page can have a big impact on how long it takes to download a page over the Internet, especially when some users are still on 56K dial-up modems. Although drop-down lists are great for providing the user with a list of choices, when you have very many drop-down lists the page size can become huge. For medium to large lists we created a paginated drop-down combo list that only shows the matching records.
By default, all matches will be displayed and a simple pagination control appears.
But as the user types in the text field, only the matching records are displayed. For example typing S will display the list of employees whose last name begins with S as shown below.
As the user types more letters, we redraw the list with the records that match:
A Paginated Filtered Search
To find a record for a list with medium to large data, we enable the user to filter the data based on the most relevant fields. This filtered lookup is used for lists of companies, employees, invoices, and so on. The actual results will be based on whatever search criteria are entered in the search and sorted based on the selected column header, and may include wildcards; for example, users can type S to find last names that begins with S or type %S to find names that contain the letter S). It is also cumulative across fields so that, for instance, you can find someone with a last name that begins with S and reports to employee 119.
Although the paginated search list page is great for finding a record, users don't always like having to return to the list to search for another match. Therefore we combined the three prior concepts into a paginated navigation control bar, as shown in the diagram below.
Sc will find the next match, etc.
By extending the concept of paginated lists throughout the application, we've been able to greatly reduce the amount of data transferred between the server and client. This, in turn, has helped us to dramatically improve performance.
Since I took over as Director of Paylocity's software development we rewrote our architecture from scratch because the prior system would not perform and scale. Our IT staff has reported a significant reduction in CPU utilization on our servers. More importantly, customers running the old system side by side with the new system report anywhere from 10 to 100 times better performance. We had some operations that used to routinely time out for our largest customers that now respond in fewer than 10 seconds.