paging on sql? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

paging on sql?

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2005-08-19 : 12:10:54

hm it will be like that

select count(*)/10 from Customers //that will get the number of pages

then
on vs.net section i will add the numbers that resulted from here to a dropdownlist. for example from 1 to 5 it will add page numbers (for totaly 50 record)
So when you select the 3. page iwant to get the 3rd 10 records. Is there a way like that?
When i select the 3. page. itwill request 20th,21th.....30th records.
can it be??
or is there another waY?

MS BLESS US

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-19 : 13:02:50

Hi bilencekic,

Try this code:
===============================
use Northwind
go

-- select * from Customers order by CustomerID

declare @Handle int, @page int, @page_size int, @start_row_num int, @TotalRows int, @TotalPages int
set @page_size = 10

exec sp_cursoropen @Handle OUT, 'select * from Customers order by CustomerID', 1, 1, @TotalRows out
set @TotalPages = ceiling(@TotalRows * 1./ @page_size)

raiserror('TotalRows = %d, TotalPages by %d = %d', 0, 1, @TotalRows, @page_size, @TotalPages)

-- Page 3
select @page = 3, @start_row_num = (@page-1) * @page_size + 1

exec sp_cursorfetch @Handle,16, @start_row_num, @page_size

-- Page 1
select @page = 1, @start_row_num = (@page-1) * @page_size + 1

exec sp_cursorfetch @Handle,16, @start_row_num, @page_size

-- Page 5
select @page = 5, @start_row_num = (@page-1) * @page_size + 1

exec sp_cursorfetch @Handle,16, @start_row_num, @page_size

-- close cursor
exec sp_cursorclose @Handle
go

bilencekic
Posting Yak Master

121 Posts

Posted - 2005-08-19 : 13:12:46

wow :D it worked
i will make sp version and that is nice really nice :)
thx man thx very much
ms bless you :P
you are the king thx

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 06:07:18

ALTER PROCEDURE bp_UrunleriGetirSayfala
-- select * from Customers order by CustomerID
@altkategoriID int
as
declare @Handle int, @page int, @page_size int, @start_row_num int, @TotalRows int, @TotalPages int
set @page_size = 10

exec sp_cursoropen @Handle OUT, '(select * from Urunler where AltKategoriID = @altkategoriID)', 1, 1, @TotalRows out
set @TotalPages = ceiling(@TotalRows * 1./ @page_size)

raiserror('TotalRows = %d, TotalPages by %d = %d', 0, 1, @TotalRows, @page_size, @TotalPages)

-- Page 3
select @page = 1, @start_row_num = (@page-1) * @page_size + 1

exec sp_cursorfetch @Handle,16, @start_row_num, @page_size

-- close cursor
exec sp_cursorclose @Handle

---i cant use a variable in the query why ?
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@altkategoriID'.

is there any alternate for a performanceable paging ?
i read some articles but queries are too long and complex

MS BLESS US

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 07:53:42

Also refer this
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Madhivanan

Failing to plan is Planning to fail

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 09:13:21

set @a = 11 -- start at row 200

set @b = 20-- end at row 250

normally b-a+1 = 10 rows must return rigt?
but 3 row returned
but when i set it
1 and 10
10 rows returns normally
but when i set it

9-10
2 rows returns but wrong results returns (first and second rows returns but i want 9th and 10 th rows to return )

at 1-10
result :
66 -1- ATI RADEON 9800 PRO
67 -1- TI 4200
68 -1- 6800 PRO
69 -1- GF 6800 PRO
70 -1- 6800
71 -1- 6800s
72 -1- 6800ss
73 -1- 6800pro
75 -1- 6600
76 -1- 6600s

when it is 9-10
resukt is :
66 -1- ATI RADEON 9800 PRO
67 -1- TI 4200

but when 9-10 result must be
75 -1- 6600
76 -1- 6600s

right????????
so why rowcounts are right but results are wrong ?

MS BLESS US

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-28 : 09:17:59

It is bad that SQL Server doesn't keep some internal command similar to TOP like STARTING FROM that will do the trick together with TOP the best way like:

select TOP 100 STARTING FROM 1000
....

Anyway, this is the best link I found for paging. You have 10 techiniques to do this, so U can choose your performance.
http://www.aspfaq.com/show.asp?id=2120

www.r-moth.com

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 09:21:15

hm thx i am checking now.
but isnt there a command for getrows between 10 and 20
or like this ?
ID column is Identity
so they change like
1
2
5
8
9

i dont want to do it with datagrid
i wish i fint it on the link
thx

MS BLESS US

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-28 : 09:29:05

yes if you have ID.... but what if you don't ?
I got across samples where ID is either not present or it is not in the integer form. Of course you can always create temporary table with identity column but that is not quite the best answer for this topic. Or it can be, depending on your problem

>>i dont want to do it with datagrid
>>i wish i fint it on the link
I find the best thing for this to be Repeater control. Since this is beyound the scope of this forum I am not going to elaborate this here, switch to ASP.NET forum. Just keep in mind that DataGrid is for qucik job and with Repeater you can customize things EXACTLY the way you want.

www.r-moth.com

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 09:32:42

when using temp table
i think you have to select al table to correct the ID number like 1,2,3,4,5,6,7
yes with this you can page
but dont you have to select all table ?

MS BLESS US

majkinetor
Yak Posting Veteran

55 Posts

Posted - 2006-03-28 : 09:34:09

You don't need to correct numbers, just remember last one so you can use

select TOP X
....
WHERE ID > last_id

www.r-moth.com

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 09:39:29

i have an idea eheheh
i will have a column named pagenumber
and it will store the row is belong to which page.
when an insert runs
select count(PageNumber) where PageNumber = (Select PageNumber from Table where ProductID = @@IDENTITY - 1)
this query will check the laast products pagenumber
so
if the count is 10 for the latest product
new pagenumber will be inserted
if count(pagenumber) is less then 10
old pagenumber will be inserted
so when i tr to query
i will just write
select ....innerjoins etc etc.... from table where PageNumber = @variable

ehehe yes this will work
i can bet
this will workkkkk

what do u think ?

MS BLESS US

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-28 : 09:52:33

Does this help?

--data
declare @t table (id int)
insert @t
          select 1
union all select 2
union all select 5
union all select 8
union all select 9
union all select 11
union all select 12
union all select 13
union all select 14
union all select 18
union all select 19
union all select 22
union all select 23
union all select 24
union all select 25
union all select 26
union all select 28

--inputs
declare @pageSize int
declare @pageNumber int

set @pageSize = 5
set @pageNumber = 2

--calculation - number of pages
select (count(*) - 1) / @pageSize + 1 as NumberOfPages from @t

--calculation - page values
declare @rowsToIgnore int
declare @id int

if @pageNumber > 1
begin
    set @rowsToIgnore = @pageSize * (@pageNumber - 1)
    set rowcount @rowsToIgnore
    select @id = id from @t order by id
end

set rowcount @pageSize
select id from @t where id > isnull(@id, 0) order by id
set rowcount 0

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 11:43:52

grrrrrrrrrrrreeeeeeeeeeeeeeeAAAAAAAAAAAAAT
thx man

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2006-03-28 : 16:44:30

my first procedures read on sql profiler : 364 cpu:10 and 0 duration:0,10,40 (paging is made by datagrid )
second is the query which is writen above by ryan , read on profiler is : 716 cpu:10 and 0 duration: 0,10,20 (paging on sql)

total number of records : 120
so is reading so much important ?
which one must i choose ?
i tink second one will be better because there wont work any datagrid functions and other stuffs that will slow down the reponse time of webpage.

edit:
i made some text
and the ryans method requess 5-6 pages/sec on performance monitor
but mine requests 90-100 page/sec
ryans respond a bit late but it is okay i think it depends on server so performance will be better than before.

thx

MS BLESS US

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources