Export (0) Print
Expand All

Improving Application Performance by Implementing Paginated Lists

 

Chuck Cooper
Director of Software Development
Paylocity Payroll

May 2005

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)

Background

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.

Paging Records

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:

  1. A simple pagination control for small lists, which we've defined as lists that typically contain fewer than 100 rows per company.
  2. 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.
  3. 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.
  4. 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.

Click here for larger image

If desired, the control can hide the VCR buttons if the results fit on one page.

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.

ms978330.ameripaypaging02(en-us,MSDN.10).gif

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.

ms978330.ameripaypaging03(en-us,MSDN.10).gif

As the user types more letters, we redraw the list with the records that match:

ms978330.ameripaypaging04(en-us,MSDN.10).gif

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.

Click here for larger image

Paginated Navigation Control Bar

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.

Click here for larger image

You can select different sorting criteria (name, ID, department, and so on) that will determine the order in which records will be displayed when you search the next or prior record. The Sort By drop-down list lets you apply the filter criteria that were entered on the filtered search list page to restrict which employees are shown. For example, if you specified employees for the software development department, only members of that department will be included in the navigation. The drop-down list in the middle of the control lets you type in a string to search for a specific record. For example, typing S will find the first match,

Click here for larger image

Sc will find the next match, etc.

Click here for larger image

You can look through the list of matches with the columns ordered based on the Sort By you selected (Name, ID, Department, and so on). When there are more matches than can be displayed users can click a Get More button. This is all managed by an XML HTTP interface so that the entire page doesn't have to be redrawn (until you select a match and navigate to that record).

Click here for larger image

Results

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.

Show:
© 2014 Microsoft