Application Paging vs Database Paging

Recently I had to investigate some performance issues on a legacy ASP.NET 2.0 application. Curiously, the performance issues were mostly client related, not server related – it appeared that the browser was crashing for no apparent reason to the customer. However at the corresponding time, there was a slight spike in memory usage for IIS. What would cause performance problems on both a client browser and a web server?

Before we go any further, let’s do a quick demo. Imagine a plain old POCO class like the one below that represents a Person.

Wow, this code means the user can actually turn off paging by passing in “false” in the querystring. This isn’t really a problem with a few dozen records, but what if we have thousands in our system?

I populated the SQL Server table with 100,000 records, and refreshed the page. The result? Well, nothing different really, the page still loaded – paging is still enabled. The problem the client experienced was client side, so let’s take a look at the browsers memory usage.

Danger Will Robinson, danger! 370MB of memory used. There lies our problem. After a little investigation of the live application, it seemed that a particular user bypassed the paging on a search page to export all the data to an Excel document – a mere 250K rows. No wonder the browser crashed!

The simplest solution here of course is to simply block the user from turning off paging, but it’s not the right solution. This is a problem inherent with application paging – why are we bringing back 100K records from the database when we only need 10?

Let’s do a quick check on the size of the 100K records in memory. There’s no clean way to do this in .NET (no way of accessing object sizes), so I’m cheating here slightly by serializing the list of people to a MemoryStream, and getting the MemoryStream’s length. I know it’s not accurate, but it’s the best option I found for getting the size of reference types.

The number of bytes found is 4,878,187 – almost 4.9 MB of data. This means every time the page is executed, 4.9MB of data is returned even though only 10 records are required. Let’s use database paging to reduce this, utilising the ROW_NUMBER feature found in SQL Server 2005 & 2008.

First of all, we need to declare a Stored Procedure in our database (you don’t have to, but it’s easier to work with). The SP will take in two parameters: PageIndex, the page that you want to return, and PageSize, the number of records you want to return.

Just like we did above, let’s do a check on the size of the 10 records in memory – 939 bytes, compared to 4,878,187. Imagine if the first query was executed 1000 times in a day. That’s over 4GB worth of data transferred between web server and database server.

Now let’s replace that with our efficient query and execute it 1000 times – a mere 1MB of data transfered between a web server and database server.

The difference is staggering really, especially if you have a problem like this on multiple pages. It’s really amazing how developers don’t test a system for scaling issues – even simply filling each section of your database with thousands (or millions) of dummy records can give you a better understanding of any bottlenecks in your application.