Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning a Subset of a Recordset


Returning a Subset of a Recordset

Author
Message
jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 18
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jwiner/returningasubsetofarecordset.asp



Doo
Doo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1
This is ludicrous...the same thing can be accomplished without the use of a cursor.

PlanetJam Media Group


PlanetJam Media Group
jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 18
I appreciate your input, but I dont believe that this approach is ludicrous. It is merely one way to solve the problem. If you would like to contribute by sending in another approach, please do so. I'd love to offer it as a supplement to the article. As a programmer you should realize that there are many ways to solve the same problem, and some ways are better than other. But never is a solution ludicrous or stupid. This discussion area is a forum for developers to discuss problems and solutions in a friendly, non-threatening environment. Please consider this the next time you offer you opinions. Thank you.



Grumpybear
Grumpybear
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
I'm trying to wrap my mind around this one. Can you please give a example of where someone can use this.



jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 18
In this example, my friend was attempting to limit the number of records returned from a recordset consisting of up to 50,000 records. This method allowed him to return a subset of that recordset and paginate through it easily and quickly without having to eat up resources by returning all 50,000 records. (Another note...the recordset (data reader) was being returned to a .NET webpage). Hope that helps.



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679
Hey Jon,

I dont think I'd call this ludicrous. Unusual maybe! Typically I'd look to solve any problem with a set based solution first, then move to a cursor as a last resort.

I'd guess that you actually dont take a really bad performance hit on this, especially if the concurrency is low. Any solution has to take into account how much it will be used - if you called this one once a day or whatever, pretty much who cares how it runs?

Rather than guess, lets find out - how about we try to come up with a couple other methods, then benchmark - I think would make a good companion article.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 18
Good idea Andy...Give me some time to come up with alternatives and I'll put something together...If anyone has some other methods they'd like to offer, I can publish them too.



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7187 Visits: 2679
Hey Jon,

Here is my first attempt:

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

--9/8/01 Andy Warren
--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)

Just profiling using QA this appears to be a little faster than your solution.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
jwiner
jwiner
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 18
Andy,

Thanks for you input. I'll test it out. I'll try and write it up as a supplement to this article.



jmadren
jmadren
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 1
Andy,

The goal of the original procedure was to present a list (or browse as I call it) of certain columns that can be sorted by any of the columns, and be able to page through the records without returning the entire recordset at once.

There are several things wrong with your alternative. One (easy to fix) is that it will not work with @BatchesToSkip=0 (the default) since TOP 0 will return nothing.

But more importantly is that it will not work when ordering by any column other than those that "happen" to be in orderid order, such as the orderdate column you're using by default. The solution to this problem is made more difficult when you attempt to order by a column like ShipName where there could be duplicates within that column, and even worse yet if the column you're ordering by could contain nulls. When you consider all of these possibilities and all the intricate code needed to handle them (I know, I've done it), the cursor approach starts looking like the better solution - for the intended goal as I stated to begin with.

Jay Madren


Jay Madren
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search