SQL Server Performance Forum – Threads Archive

paging through stored procedure

As I mentioned in my previous posting that I am using a database having 1-3 lac records. I want to use paging through stored procedure. Suppose a query matches 1 lac records. I want to send only 30 records to webserver instead of sending all the records. I also want to dislay page numbers as well as total records found. I tried it using TSQL cursor and ADO recordset in ASP it was very slow….
Please suggest any other solution. Regards
Gurpreet Sachdeva

---

This is a common question and there a variety of solutions. Below are two URLs to articles that will provide the background you need to resolve your issue: http://www.aspfaqs.com/webtech/062899-1.shtml
http://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=37 If you still need more help, then search these two websites for additional information.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

---

I have gone through the link you have provided in above posting. I just want to know that is this the only way to do it or is there any other better method. In other words is it good to create such big temporary table of so often. Regards
Gurpreet Sachdeva

---

The temporary table approach shown in those links are for people who want to avoid creating a dynamic where clause. You can pull out any section of records in a single query, without a temporary table. Its a little tricky to visualise at first, but try something like this…. id value
—————
600 bob
605 john
632 rob
799 sue To pull out a couple of records from the middle of this table (imagine you want 2 records per page..) select * from (select TOP 2 * from (select TOP 3 * from Names order by id ASC) a order by id DESC) b order by id ASC Essentially you are selecting the top 3 records, turning them upside down in terms of order, and then selecting the top 2 of those. Then reordering back to be on order of ID. ‘2’ would become whatever your page size is.
‘3’ would become (pagesize * currentpage) + 1 You can possibly figure out total pages by doing a count(*) on your query, before you add the above paging criteria. This is often quicker than looping through using a cursor, but each case is different i guess. Hope this helps. Let me know if you need further explanation.

---

Here is another link: Recordset Paging – Speed Test
http://www.adopenstatic.com/experiments/recordsetpaging.asp /Argyle

---

Chappy, I found this post extremely helpful. It really improved my understanding of what is possible with SQL. Thanks, Luke

---

Hi all! I agree with luke & hope chappy continues doing that. Thanks,

---

Hi Chappy,
I was not clear about something.
How are you going to set the value or TOP? Are you planing to build a dynamic string to execute?
You cannot do SELECT TOP @variable *. You can only do SET ROWCOUNT @variable. Bambola.

---

Yes I agree, you would need to dynamically build the sql. Maybe this would perform worse than temporary tables but I suspect not, either way, its another alternative for him to investigate.

---

hi i tried the wonderful query suggested by chappy. Following were the records 600bob
605john
632rob
799sue here is the query select * from
(select TOP 2 * from
(select TOP 3 * from temp1 order by id ASC)
a order by id DESC)
b order by id asc output is 632rob
605john it should be 605 john
632 rob changing asc to desc in the outermost query does not affects the output in any way. I am unable to understand why it is happening ? thanks in advance lsb

---

The method I’ve used in the past works as follows.<br /><br />1. From the page number and number of records per page work out the position where you want to start.<br />2. Select that number of records and store the values of the fields you order by into variables<br />3. Select the number of records per page where your order criteria is &gt; the variables in 2.<br /><br />I can’t recall the exact syntax I’ve used but it goes something like this. There are instances where concatenating OrderFields can lead to complications which I got around by joining the table to itself and using that as part of the ordering but I certainly can’t remember the code for that<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br /><pre><br />@PageNumber = 5,<br />@RowsPerPage = 10<br /><br />You therefore want to ignore the first 50 records<br /><br />SET @@ROWCOUNT = 50<br /><br />SELECT @OrderFields = OrderField1 + OrderField2<br />FROM table<br />ORDER BY OrderField1 + OrderField2 DESC<br /><br />SELECT ResultFields<br />FROM table<br />WHERE OrderField1 + OrderField2 &gt; @OrderFields<br />ORDER BY OrderField1 + OrderField2<br /><br /></pre><br />

---

@@ROWCOUNT is the number of rows affectedby the last statement. You cannot SET a value to it.
You must have meant SET ROWCOUNT 50. Take into consideration that when you are ordering by the whole result set will be prepared anyway. Bambola.

---

lavinder: you say.. —
select * from
(select TOP 2 * from
(select TOP 3 * from temp1 order by id ASC)
a order by id DESC)
b order by id asc output is 632 rob
605 john it should be 605 john
632 rob
— I cannot reproduce this, mine returns the results with john at the top. Are you sure those are the exact parameters you passed in? Anyway, I was trying to create a little stored proc for you, but I think youre right that my code isnt 100% correct. Ill repost later once I have a chance to amend my sql.

---

This is what I usually do. Just change the parameters to suit your needs. CREATE PROCEDURE BasicPagingProcedure
(
@aParamvarchar(10) = null,
@iSizeint = 30,
@iPageint = 1
) As
set nocount on
declare @iStartint,
@iEndint,
@allrowsint,
@errint select @iStart = (((@iPage – 1) * @iSize) + 1)
select @iEnd = (@iStart + @iSize – 1) — creating table with as few columns as possible
declare @tResults table
(
I_IDint identity(1,1) primary key,
I_PKint
) — get all lines search param
insert into @tResults
select I_PK
from table_name with (nolock)
where A_Param = @aParam select @allrows = @@rowcount, @err = @@ERROR — joining both tables and gettin the requested "page"
select a.I_PK –, a.field_name1, a.field_name2 etc
from @tResults t, table_name a with (nolock)
where a.I_PK = t.I_PK
and I_ID between @iStart AND @iEnd — You can use @allrows to calculate number of pages You can change the table datatype with a temp table. Bambola.

---

]]>