Getting sequential row blocks for page by page output

[safaritek]

This is a web catalogue application, just for reference.

Until lately, we had been querying the database for all products and then caching that query. We then let the application server use that cached query to show 10 products per page using a standard next/previous page format. The application server would be the device which determined the start and end rows to show.

This ran fine for many years, but now the company is adding many more products online each month, causing the initial query to take longer and longer as they do that.

What I am looking for is a way to simply request the 10 required rows from SQL server each time, rather than have to cache a huge query at the onset.

i.e. Page 1 would need to call data rows 1-10, Page 2 would need to call rows 11-20, etc etc


I know I can get the very first 10 records by using TOP, but for the life of me I am coming to a brain freeze when trying to get any other set of 10 rows!

This is likely a very simple task, but it is eluding me at the moment.

Any and all help is appreciated.

 

[gmmastros]

The answer to your question depends on the version of SQL Server you are using. With SQL Server 2005, this is a lot easier. What version of SQL Server do you have?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crowley16]

how are you getting the data to the front end and how is the data being displayed when it gets there?

I *believe* in ado, you can use a forward only cursor type which will start to return records immediately before the full resultset is generated. This will depend on the type of query you're running, e.g. won't work if there's any aggregates...

of course, you can still use the top argument, with a where statement to filter out the records already selected...

--------------------
Procrastinate Now!

 

[safaritek]

Sorry - I meant to include the details

Right now I have SQL Server 2000 and we run all queries via the SQl ODBC driver on a Win2003 machine

 

[safaritek]

Additionally it is just a simple query at this point, which is why I know it needs to be altered.

An example of how it is called thus far, is like this:

SELECT Column1,Column2,Column3 etc
FROM Products
WHERE Active = 1
ORDER BY CATALOGNUM


There is a KeyID column as an identifier specific to the database, but with various products being deleted or deactivated the ID number is never going to be 100% sequential.

I was thinking of using TOP with a criteria as was implied above, but that would require some type of criteria that always has results that are able to be calculated, but there doesn't seem to be any column that would allow proper results 100% of the time

.. UNLESS there is some database specific system column that is available, simliar to a "start" and "end" row definition??

Again, any suggestions/help is appreciated.

 

[gmmastros]

With SQL 2000, I usually create a table variable with an identity column. Then, I insert the KeyId column in to this table in the order in which I want the data to sort. Then, it's a simple matter to use the table variable to return the data you want. Here's an example to show you what I mean. It may not be perfect (for your situation), but with a little tweaking, it may perform nicely.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Int[/color] [COLOR=blue]Identity[/color](1,1) [COLOR=blue]Primary[/color] [COLOR=blue]Key[/color], KeyId [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(KeyId)
[COLOR=blue]Select[/color] KeyId
[COLOR=blue]From[/color]   Products
[COLOR=blue]Where[/color]  Active = 1
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] CatalogNum

[COLOR=blue]SELECT[/color] Column1,Column2,Column3 etc
[COLOR=blue]FROM[/color]   Products
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Temp [COLOR=blue]T[/color]
         [COLOR=blue]On[/color] Products.KeyId = [COLOR=blue]T[/color].KeyId
[COLOR=blue]WHERE[/color]  [COLOR=blue]T[/color].RowId >= 20
       And [COLOR=blue]T[/color].RowId < 30
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] [COLOR=blue]T[/color].KeyId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom