best way to return a paged result set from a stored procedure 
Author Message
 best way to return a paged result set from a stored procedure

i'm looking for the best way to return a paged result set from a stored
procedure. There are many articles that discuss custom paging, and they
almost all rely on temporary tables, I'm looking for alternatives which
overcome the performance problems associated with temporary tables. The
scenario is pretty simple, it's to return search results to an asp.net
application, based on criteria passed into the stored procedures.

As mentioned, most articles handle this via temporary tables. While
temporary tables work about 100x faster than implementing the paging in
ASP.Net (since all records must be returned, only though 10, 20 or xx are
displayed) it would be nice to get better performance. What the articles
mention (for example:
http://www.***.com/ )
is to set up a temporary table that contains the columns you want to return
IN ADDITION to an identity column and dump ALL the filtered/ordered records
into the temporary table. With the temporary table having a perfectly
sequential seed for each row, you can return the right records simply by
knowing what page you want and how many records per page, ala:


It works great! But for our scenario, these are the slowest functions, and
unfortunetly the most frequently accessed. The sproc would be called around
50-75x per minutes, and needs to be scallable to allow for more. That means
that we are creating a large number of temporary tables and populating them
with large chunks of data, only to return a small subset.



Sun, 27 Nov 2005 17:20:14 GMT
 best way to return a paged result set from a stored procedure

In a client/server system, the front end handles the display and the
backend handles the database.  You are returning to the way we wrote
code in the 1950's --monolithic programs that could never port to new
platfroms, instead of a tiered architecture.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 27 Nov 2005 17:59:28 GMT
 best way to return a paged result set from a stored procedure
Stephane,

Quote:
> i'm looking for the best way to return a paged result set
> from a stored
> procedure. There are many articles that discuss custom
> paging, and they
> almost all rely on temporary tables, I'm looking for
> alternatives which
> overcome the performance problems associated with temporary
> tables. The
> scenario is pretty simple, it's to return search results to
> an asp.net
> application, based on criteria passed into the stored
> procedures.

> It works great! But for our scenario, these are the slowest
> functions, and unfortunetly the most frequently accessed. The
> sproc would be called around 50-75x per minutes, and needs to
> be scallable to allow for more. That means
> that we are creating a large number of temporary tables and
> populating them
> with large chunks of data, only to return a small subset.

I don't know if this is the best way, but it is fast, assuming
your table is appropriately indexed.

use Northwind
go
create procedure ListContacts (

) as

select top 10 * from Customers

order by ContactName
go

exec ListContacts null
exec ListContacts 'Art Braunschweiger'
exec ListContacts 'Eduardo Saavedra'
exec ListContacts 'Hanna Moos'
go
drop procedure ListContacts

Linda



Sun, 27 Nov 2005 18:39:20 GMT
 best way to return a paged result set from a stored procedure
Linda your code is fast, but only works when you are trying to page against
the same column you are ordering. For example, changing your query to:

select top 10 * from Customers

order by CompanyName

no longer returns the corrext next 10 records.

Karl


Quote:
> Stephane,

> > i'm looking for the best way to return a paged result set
> > from a stored
> > procedure. There are many articles that discuss custom
> > paging, and they
> > almost all rely on temporary tables, I'm looking for
> > alternatives which
> > overcome the performance problems associated with temporary
> > tables. The
> > scenario is pretty simple, it's to return search results to
> > an asp.net
> > application, based on criteria passed into the stored
> > procedures.

> > It works great! But for our scenario, these are the slowest
> > functions, and unfortunetly the most frequently accessed. The
> > sproc would be called around 50-75x per minutes, and needs to
> > be scallable to allow for more. That means
> > that we are creating a large number of temporary tables and
> > populating them
> > with large chunks of data, only to return a small subset.

> I don't know if this is the best way, but it is fast, assuming
> your table is appropriately indexed.

> use Northwind
> go
> create procedure ListContacts (

> ) as

> select top 10 * from Customers

> order by ContactName
> go

> exec ListContacts null
> exec ListContacts 'Art Braunschweiger'
> exec ListContacts 'Eduardo Saavedra'
> exec ListContacts 'Hanna Moos'
> go
> drop procedure ListContacts

> Linda



Sun, 27 Nov 2005 19:23:43 GMT
 best way to return a paged result set from a stored procedure




Quote:
> Linda your code is fast, but only works when you are trying to page
against
> the same column you are ordering. For example, changing your query to:

> select top 10 * from Customers

> order by CompanyName

> no longer returns the corrext next 10 records.

> Karl



> > Stephane,

> > > i'm looking for the best way to return a paged result set
> > > from a stored
> > > procedure. There are many articles that discuss custom
> > > paging, and they
> > > almost all rely on temporary tables, I'm looking for
> > > alternatives which
> > > overcome the performance problems associated with temporary
> > > tables. The
> > > scenario is pretty simple, it's to return search results to
> > > an asp.net
> > > application, based on criteria passed into the stored
> > > procedures.

> > > It works great! But for our scenario, these are the slowest
> > > functions, and unfortunetly the most frequently accessed. The
> > > sproc would be called around 50-75x per minutes, and needs to
> > > be scallable to allow for more. That means
> > > that we are creating a large number of temporary tables and
> > > populating them
> > > with large chunks of data, only to return a small subset.

> > I don't know if this is the best way, but it is fast, assuming
> > your table is appropriately indexed.

> > use Northwind
> > go
> > create procedure ListContacts (

> > ) as

> > select top 10 * from Customers

> > order by ContactName
> > go

> > exec ListContacts null
> > exec ListContacts 'Art Braunschweiger'
> > exec ListContacts 'Eduardo Saavedra'
> > exec ListContacts 'Hanna Moos'
> > go
> > drop procedure ListContacts

> > Linda



Sun, 27 Nov 2005 19:38:19 GMT
 best way to return a paged result set from a stored procedure
Karl,

Quote:
> Linda your code is fast, but only works when you are trying
> to page against the same column you are ordering. For
> example, changing your query to:

> select top 10 * from Customers

> order by CompanyName

> no longer returns the corrext next 10 records.

Well, then you need a query that restricts on CompanyName:

select top 10 * from Customers

order by CompanyName

Any kind of paging solution depends on a known fixed order of
results that can be filtered on to get the "next" n rows.

Your temp table solution also depends on a fixed order. If your
table is populated so that the order of TempID corresponds to
the order of CompanyName, you will have the same problem if you
try to get the next n rows by ContactName.

Linda



Sun, 27 Nov 2005 19:53:54 GMT
 best way to return a paged result set from a stored procedure
Yes that is obvious.  I was going to say that it isn't very practical
however, since there are probably many search fields and many ways of
ordering, but then I though, you could do something like:





...

Which is quite nice.  However,  the first time the sproc iscalled, records
1-10 are returned, when the user hit "next", CompanyName of record 10 is

is on page 2 (records 11-20), and jumps to page 5 (records 41-50)?  Things
get a bit tricker since knowing the CompanyName of record 20 isn't going to

end up with the wrong page....

Karl



Quote:






Sun, 27 Nov 2005 19:57:46 GMT
 best way to return a paged result set from a stored procedure
I understand, but the TempID field is created based on the order

CREATE TABLE #temp (TempID int IDENTITY PRIMARY KEY, CompanyName
VARCHAR(40), ContactName VARCHAR(40))

INSERT INTO #temp (CompanyName, ContactName)
  SELECT CompanyName, ContactName
    FROM Customers
   ORDER BY CompanyName

The TempID will correspond to different rows for this query than one with
ORDER BY ContactName...but will always be perfectly sequential so you could
then do:





SELECT *
  FROM #Temp


This method, as I mentioned in the other posts, also allows for going from
records 1-10 to 41-50...since it isn't dependant on knowing the previous
value.

Karl


Quote:
> Karl,

> > Linda your code is fast, but only works when you are trying
> > to page against the same column you are ordering. For
> > example, changing your query to:

> > select top 10 * from Customers

> > order by CompanyName

> > no longer returns the corrext next 10 records.

> Well, then you need a query that restricts on CompanyName:

> select top 10 * from Customers

> order by CompanyName

> Any kind of paging solution depends on a known fixed order of
> results that can be filtered on to get the "next" n rows.

> Your temp table solution also depends on a fixed order. If your
> table is populated so that the order of TempID corresponds to
> the order of CompanyName, you will have the same problem if you
> try to get the next n rows by ContactName.

> Linda



Sun, 27 Nov 2005 20:09:19 GMT
 best way to return a paged result set from a stored procedure
Karl,

Quote:
> I understand, but the TempID field is created based on the
> order

> CREATE TABLE #temp (TempID int IDENTITY PRIMARY KEY,
> CompanyName VARCHAR(40), ContactName VARCHAR(40))

> INSERT INTO #temp (CompanyName, ContactName)
>   SELECT CompanyName, ContactName
>     FROM Customers
>    ORDER BY CompanyName

If you go back and read the original post, Stephane is already
using the temp table method and, as he says, it works great but
it's *too slow*. His problem is performance and scalability, not
necessarily flexibility.

Linda



Sun, 27 Nov 2005 20:40:41 GMT
 best way to return a paged result set from a stored procedure
Sorry, i should have been more clear...Stephane and I work together...we are
greedy and would like both :)
I'll definetly look at your suggestions and see if it can't be used, but the
search is quite advanced (as far as fields, and opions) and customizable.

Thanks,
Karl


Quote:
> Karl,

> > I understand, but the TempID field is created based on the
> > order

> > CREATE TABLE #temp (TempID int IDENTITY PRIMARY KEY,
> > CompanyName VARCHAR(40), ContactName VARCHAR(40))

> > INSERT INTO #temp (CompanyName, ContactName)
> >   SELECT CompanyName, ContactName
> >     FROM Customers
> >    ORDER BY CompanyName

> If you go back and read the original post, Stephane is already
> using the temp table method and, as he says, it works great but
> it's *too slow*. His problem is performance and scalability, not
> necessarily flexibility.

> Linda



Sun, 27 Nov 2005 20:41:16 GMT
 best way to return a paged result set from a stored procedure
Karl Seguin,

Quote:
> Sorry, i should have been more clear...Stephane and I work
> together...we are greedy and would like both :)
> I'll definetly look at your suggestions and see if it can't
> be used, but the search is quite advanced (as far as fields,
> and opions) and customizable.

Then you should consider writing a bunch of simple procedures to
cover the different cases instead of one big complicated
procedure.

If you are serious about wanting to improve performance then
stay away from a single stored procedure that looks like what
you posted in reponse to Aaron.





...

A stored procedure is compiled the first time it is executed and
a query plan is created based on the inputs passed to the
procedure.

If the inputs change each time the procedure is executed,
forcing control to branch to a different section of code, this
could lead to excessive runtime recompiles. Or it may not be
recompiled and use a query plan that is based on an
inappropriate index for the particular inputs.

You can have a single procedure as an entry point that client
applications just execute, but inside the procedure you should
branch to individual procedures that each have a stable query
plan.





...

Linda



Sun, 27 Nov 2005 21:30:18 GMT
 best way to return a paged result set from a stored procedure
Wrong! - please read up on what client/server computing is - what you are
doing is using old mainframe techniques that will not work in the
client/server model.

In order to get scalability, minimum network traffic should be communicated
between the client and middle tier/data tier.

You should filter out just the results you want in the data tier (database)
before passing back out to the application - that is without exception the
only scalable place to do it for an OLTP system.

See http://www.sqlserverfaq.com?kbid=622 for a description of how to do this
using ASP.NET and SQL Server stored procedures.

--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)



Mon, 28 Nov 2005 08:19:18 GMT
 best way to return a paged result set from a stored procedure
Check out my presentation on this, there is sample code etc... for an
ASP.NET application - the paging is done inside stored procedures so that
the minimum amount of traffic occurs between the app and the database.

http://www.sqlserverfaq.com?eid=22

--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)



Mon, 28 Nov 2005 08:20:51 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. returning result sets from stored procedures

2. Returning result sets in oracle stored procedure (OLEDB)

3. return multiple result sets from a stored procedure

4. Can't update result set returned from stored procedure using RDO

5. Returning result set from function to stored procedure is very slooooooooow

6. Returning a result set from a Stored Procedure

7. returning result sets from stored procedures

8. How not to return multiple result sets from a stored procedure

9. Stored Procedure/Multiple Return Results/Set Statement

10. return result set from stored procedure

11. return result set from stored procedure

12. Can't update result set returned from stored procedure using RDO


 
Powered by phpBB® Forum Software