Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Returning a Subset of a Recordset Expand / Collapse
Author
Message
Posted Thursday, August 9, 2001 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:58 AM
Points: 115, Visits: 17
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jwiner/returningasubsetofarecordset.asp


Post #734
Posted Tuesday, September 4, 2001 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2003 12:00 AM
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
Post #20613
Posted Tuesday, September 4, 2001 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:58 AM
Points: 115, Visits: 17
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.




Post #20614
Posted Tuesday, September 4, 2001 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2003 12:00 AM
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.




Post #20618
Posted Wednesday, September 5, 2001 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:58 AM
Points: 115, Visits: 17
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.




Post #20619
Posted Thursday, September 6, 2001 5:26 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, August 12, 2014 10:53 AM
Points: 6,783, Visits: 1,876
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
Post #20620
Posted Friday, September 7, 2001 5:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:58 AM
Points: 115, Visits: 17
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.




Post #20621
Posted Saturday, September 8, 2001 8:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, August 12, 2014 10:53 AM
Points: 6,783, Visits: 1,876
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
Post #20616
Posted Tuesday, September 11, 2001 4:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:58 AM
Points: 115, Visits: 17
Andy,

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




Post #20617
Posted Friday, September 14, 2001 10:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 9, 2004 9:43 PM
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
Post #20615
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse