Thursday, November 20, 2008
 
   
 
Welcome to my site

First let me say thanks for stopping by my site. My name is David Hanson-Graville and I am a IT consultant working in the UK. Let me make it clear, I am passionate about technology and specifically .net and its various forms. I've programmed in a range of langages, but I can say, I am now at my happiest when coding with c#. I hope my blog is an enjoyable & educational read and please feel free to email me at David.Hanson@OnTheBlog.net if you have any questions. 

Archive Minimize
Print  
Contact me Minimize
Print  
Microsoft Certs Minimize







Print  
Silverlight News Minimize
silverlight - Google News
  1. Major League Baseball Switches From Silverlight to Flash - PC World

    Published Thu, 20 Nov 2008 16:53:44 GMT by
  2. Review: Silverlight, for real this time - Computerworld

    Published Wed, 19 Nov 2008 21:07:45 GMT by
  3. Where to find good Silverlight applications - InfoWorld

    Published Thu, 20 Nov 2008 15:27:23 GMT by
  4. MLB.com drops Silverlight for Adobe Flash - CNET News

    Published Mon, 17 Nov 2008 22:42:14 GMT by
  5. Adobe Flash Hits Home Run With MLB - InformationWeek

    Published Thu, 20 Nov 2008 10:22:39 GMT by
  6. Silverlight, for real this time - NetworkWorld.com

    Published Tue, 18 Nov 2008 21:11:49 GMT by
  7. .NET similarities prove golden for Silverlight - SDTimes.com

    Published Wed, 19 Nov 2008 20:11:43 GMT by
  8. Novell and Microsoft sanction Silverlight work-alike for Linux - BetaNews

    Published Wed, 19 Nov 2008 20:25:22 GMT by
  9. Silverlight Beta for Linux on the Road to Silverlight 3 on Windows - Softpedia

    Published Wed, 19 Nov 2008 16:19:45 GMT by
  10. Microsoft crashes Adobe RIA party - Register

    Published Mon, 17 Nov 2008 22:19:52 GMT by
Print  
SQL Server: How to page query results Minimize
Location: BlogsOnTheBlog    
Posted by: David Hanson Thu, 31 Jul 2008 17:20:11 GMT

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.

Permalink |  Trackback

Your name:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel