Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

More on Returning a Subset of a Recordset

By Jon Winer,

More on returning a subset of a recordset
Last week's tip created quite a discussion on different techniques for returning a subset of a recordset:  (Discussion 1, Discussion 2). In this article, I want to briefly discuss these solutions as it relates to their performance and effectiveness.

Here is the solution mentioned in the tip that generated so much discussion - Original Soution

Click here to view the stats for the above solution using Query Analyzer's Server Trace.

You can see that the bulk of the time and resources used by this approach are during the declaration and opening of the cursor. This approach is by far the least efficient of the ones offered, but aside from that, it does possess some good qualities. One good quality is that it works. Sometimes just getting the job done is what matters. This approach is also intuitive. Logically, our brain works in the order that the first solution is presented in. We naturally think in a 'loop' when we try to solve paging problems or repetitive tasks. This solution works in that manner.

This next approach is slightly modified. There is no cursor, and if you examine the trace statistics , you can see that is it much less resource intensive. Another difference is that it builds the sql string dynamically. This isn't bad, but it doesn't take advantage of the precompile benefits that a stored procedure offers. Another point (offered by one of our readers), is that when @BatchesToSkip = 0, the query will return nothing. Despite some of the limitations presented in this method, it offers good insight into reworking an initial solution into one that is cleaner and more robust.

Alternative Solution #1

Create Proc usp_GetSubsetOfOrders

@BatchSize int = 10, @BatchesToSkip as int = 0, @OrderBy as varchar(100)='orderdate'

As

--Alternate method of returning a subset of records, typically used in 'paging' operations
--where you only show x records per page and do not want to return the entire recordset
--to the client.

declare @RowsToIgnore int
declare @Sql varchar(1000)

set @RowsToIgnore = @BatchesToSkip * @BatchSize

set @sql='select top ' + convert(varchar(10), @BatchSize) +
     ' OrderDate, ShipName, ShipCity from orders where orderid > ' +
     '(select max(orderid) from (select top '      + convert(varchar(10), @RowsToIgnore) +
     ' orderid from orders order by ' + @OrderBy + ') A) order by ' + @OrderBy

exec (@Sql)

This last solution offered below, at first glance seems to do the trick...and it does. It is a very elegant solution, and often times they prove to be the best. My experience has taught me that if an approach looks unappetizing, then there is probably a better, more efficient way of doing it. Just take a look at the stats. Here we have a a pure SQL method. There is no need to declare any variables and for the most part, the SQL code is static.

Alternative Solution #2

SELECT TOP 200 col1, col2 ... FROM table WHERE idcol NOT IN (SELECT TOP 2000 idcol FROM table ORDER BY somecol) ORDER BY somecol

Just a quick thanks to everyone who participated in the related discussions. These forums create great discussions and cultivate a fantastic learning environment. I hope those who have participated continue and those who haven't soon will.
Total article views: 8852 | Views in the last 30 days: 0
 
Related Articles
BLOG

The Kimball Approach

The Kimball Approach There are a lot of misconceptions about dimensional modeling and the Kimball...

BLOG

DevConnections Offers DBAs Many Options for Training

As we have done for the last several years, SQLServerCentral.com will be offering its own track at S...

ARTICLE

Some Comments about our Discussion Area

Sometimes as hard as you look you just can't find the answer or idea you're looking for - that's whe...

FORUM

NOLOCK Discussion

Do's Don'ts of WITH (NOLOCK)

ARTICLE

Business Continuity for SQL Servers With a Standby Approach

Ensuring the availability of your database servers is becoming more important all the time. Fortunat...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones