Paging still executes the entire SQL statement right?

[cawthor]

I have a query front end built in ASP. It's possible the user could run a statement that returns thousands of rows and times out. I want to implement a paging feature. I've seen examples using absolutepage and pagesize properties of the recordset. Am I right in thinking that these features won't increase the speed of my query (all rows are still returned), they will just assist me in displaying the data? If this is the case then my query will still timeout. I want to add some code to my SQL statement so it only selects say 50 rows at a time. Does anyone know if this is possible in SQL Server. I know there is a 'TOP' command that selects the first x rows...won't be much use when I get to the end of my data set! Is there a unique RECID value built in to SQL that I can Reference...eg on page 2 I can select the top 50 recs where recid > last recid.

THanks...Rich.

 

[ddot02]

Maybe i dont get your question clearly, but maybe what you need is to speed up your query or increase the performance while retrieving the records from your database using paging.

You can build a store procedure for each table you want to display the records in your SQL server. Then, in your ASP script, you just need to call the store procedure name.

By doing this, the sorting process (X records/page) will be handled directly by SQL server instead of by IIS as the web server. If you want only 20 records displayed per page, the SQL server will send exactly (only) 20 records to IIS at the time, and not entire records. And the web server (IIS) doesnt need to keep entire datas. It will increase the performance.

 

[Genimuse]

You're right, though, SQL Server has to the the whole query, I'm nearly positive.

It's quite possible, though, that the query can be sped up through optimization, indexing, and perhaps most easily, changing the query into a stored procedure.

 

[travisbrown]

You should be using GETROWS() in conjunction with a Stored Procedure rather than looping through the resultset. This will return specified lines.

Check out

http://www.4guysfromrolla.com/webtech/070500-1.shtml

or

http://juicystudio.com/tutorial/asp/getrows.asp

 

[Genimuse]

I don't think he's talking about looping through the recordset, just using SQL to page through the records, hopefully not selecting them all and then only retrieving a subset.

I suggest asking the folks over in the SQL Server forum, forum183.

 

[travisbrown]

Well, if he's paging them (I assume on an ASP page), regardless of whether he is displaying on or 1000 records, he should still use GETROWS() and a stored procedure or view. Much, much faster and more efficient. Even GETSTRING() would be faster and provide a target subset.

If he wants a subset baset on position in the table, this is what GETROWS() is for.

Cheers.

Rich, want to explain how you want to use the data?

 

[Genimuse]

GetRows is also for lightening the load on the database and closing the connection as soon as possible.

But GetRows still won't solve keeping the database from performing a select that grabs every row -- it just tells ado what portion of that to grab. It won't speed up the select itself, which is what I believe Rich is asking about.

 

[travisbrown]

True Genimuse, it doens't make the SQL perform differently, but it will reduce the load on the app server and allow Rich to get the script parsed more quickly. I was just offering an additional solution.

Rich, you might be able to create a SELECT TOP 50 query, then use @@ROWCOUNT and pass the value back to subsequent queries, then do an OUTERJOIN with a nested query to return the next TOP 50 rows not in TOP @@ROWCOUNT. You'd have to aggregate the @@ROWCOUNT values each time.

@@ROWCOUNT returns the number of rows in the last query.

I'm not sure if you will get a performance enhancement over a straight SELECT in a stored procedure on an indexed table and using GETROWS(), though.

 

[Genimuse]

Something like that might help quite a bit. Thinking about it, if you have an ORDER BY that is anything but the native row number (not even your primary key), however, it's going to forced to select everything to perform the ordering.

I'm pretty sure you're stuck with trying to improve the speed of the query itself.

 

[Tarwn]

Another option would be to try using the recordset object and a .Open command. Personally I tend to stick with Connection.Execute for most things. But you ay be able to speed things up on those large result sets by using a keyset selection. With keyset it will go back to the db and retrieve the data when you ask for it, which hurts performance, but it may also cut down on communications if you are only asking for a single set of 20 records out of a given 1000-item query.

By itself a query for thousands of rows of data should not cause your page to time out. Especially when the source is SQLServer. I have an Access db with over 300,000 records that can handle 5-10,000 row queries AND display them in less time then it takes the browser to render the received HTML. I would suggest following some of the suggestions on speediong up the application side of things first to see if perhaps that is where the timeout is being caused.

-T