The application I am working on at the moment has a large number of complex search screens that allow the user to define either very narrow or very broad query's. A great deal of effort has been spent in optimising the queries in order to ensure they are performant. However, if a user decided to execute a very broad query, the result set that could be returned could be many thousands of records. Let’s take a look at a broad query using this simple SQL.
Select ID, Forename, Surname
FROM Person Where Surname like '%DA%'
In this query we want to return everything from our person table where the surname contains the letter “DA”. Running this query gives us the following 99 results. I’ve randomized the data in our person data for privacy reasons.
Now this query is running on some test data but on a live system it might return 20,000+ records. Transferring this data from our DB server to our application tier, then into business entities and serialising them via soap to our client is going to be demanding process. This is not going to be a particulary great idea, a better approach would be to return results in pages so that we can maintain a responsive UI and reduced long running network calls.
Taking this example further, we lets say that we would like our page sizes to be 5 records each. The first page will return record 1-5 and when the user clicks next we require records 6-10 to be displayed. In order for us to be able to achieve this functionality we need to implement an index on our result set so that we can locate a particular page of data within the full results. To do this SQL server provides a handy function called which ROW_NUMBER()provides an incremental index for each record in our result set. If we implement the ROW_NUMBER()as part of our results we can see the results below.
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as [Index], ID, Forename, Surname
FROM Person Where Surname like '%DA%'
We can now see from executed query above that our results contain and INDEX column which uniquely identifies each row returned from our results. By adding this index to our results we now have a way of navigating batches of records within our results set. To do this is not as simple as just adding a WHERE to our SQL statement, instead we must execute the full query in order to determine our full set of indexes, then apply the WHERE on top of that. We can doing this using a sub-query.
SELECT ID, Forename, Surname
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))
AS [Index], ID, Forename, Surname From Person
Where Surname like '%DA%')
AS ResultSet
WHERE [Index] >= 6 AND [Index] <= 10
And the results
As you can see our results reflect only the range of records that we require from our overall result set. It’s important to note that the full query is still being executed in the background but we are reducing the burden on the results that are being sent to the client. So I hope this provides an alternative way to managing large result sets over the internet.