May 2, 2008 at 1:25 pm
Another forum posted this (for SQL Server 2005 only):
CREATE PROCEDURE dbo.ShowLog
@PageIndex INT,
@PageSize INT
AS
BEGIN
WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)
SELECT Date, Description
FROM LogEntries
WHERE Row between
(@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
-----------
it looks a lot simpler! what do you think?
tx - matt
May 2, 2008 at 2:06 pm
Matt: this is what I posted:
Create Proc spTableRows_Window(@startingRow int, @rows int)
AS
--NOTE: some stuff stolen from Jeff Moden and Matt Miller
Select * From ExampleTable
Where LookupCol IN(
Select LookupCol from
(select LookupCol, row_number() over (order by LookupCol) RN
from ExampleTable) r
Where rn between @startingRow and @startingRow+@rows )
How is what you just posted simpler? It just already has the output column(s) decided, which is faster for small rows, but slower for large rows.
Here is what my narrow-row query would look like with your field names:
Create Proc spShowLog(@startingRow int, @rows int)
AS
Select Date, Description from
(select Date, row_number() over (order by Date) as Row
from LOG) r
Where Row between @startingRow and @startingRow+@rows
Your is the same as mine, it just replaces my (subquery) with a WITH Clause and uses PageNo/PageSize math instead of FirstRow/RowCount math. In theory it should be exactly the same as mine, unless the WITH causes it to be slower.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 2:32 pm
Yes, Barry, it is the same - my mistake.. i was comparing it to the code of Matt Miller earlier in this thread - his code seems quite a bit longer:
-===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @SQL VARCHAR(8000)
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 9000
SET @Skip = 900000
SELECT t.RowNum
FROM dbo.Test t,
(
SELECT TOP (@PageSize) test.RowNum
FROM dbo.Test WITH (NOLOCK)
LEFT OUTER merge JOIN (SELECT TOP(@skip) RowNum
FROM dbo.Test WITH (NOLOCK)
ORDER BY RowNum) r
ON test.rownum=r.rownum
WHERE r.rownum IS NULL
ORDER BY test.RowNum
) d
WHERE t.RowNum = d.RowNum
SET @SQL = '
SELECT t.RowNum
FROM dbo.Test t,
(
SELECT TOP '+STR(@PageSize)+' RowNum
FROM dbo.Test WITH (NOLOCK)
WHERE RowNum NOT IN (SELECT TOP '+STR(@Skip)+' RowNum
FROM dbo.Test
ORDER BY RowNum)
ORDER BY RowNum
) d
WHERE t.RowNum = d.RowNum'
EXEC (@SQL)
--------------------------------------------
what do you think Barry?
May 2, 2008 at 3:07 pm
Well the version of Matt's that you have actually seems to be comparing two queries: a static version (first half), vs. a Dynamic SQL version (second half) so it's really twice as big as a production version would normally be.
Other than that, Matt's is very similar to mine, it uses TOP(XX) instead of Row_Number() which I think is about 20% slower, but otherwise almost the same. He has (NOLOCK), which is probably a good idea (should add to mine) and does not have any data columns which you would add yourself.
Matt's is written as the wide-row format (works better for many columns, or large data rows) which has the extra SELECT(..) wrapping the outside. For a narrow-row situation (few columns or small data rows) it would probably benefit as mine does from removing the final outer SELECT(..).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 5, 2008 at 4:20 pm
ALTER PROCEDURE [dbo].[Paging1]
(
@startRowIndex int,
@maximumRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from the table before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first ID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = ID FROM MyTable ORDER BY Address
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT m.*
FROM MyTable m
WHERE ID >= @first_id
ORDER BY m.Address
SET ROWCOUNT 0
-------------------------
MyTable has three columns: ID (int), Name (string), Address (string)
ID is not necessarily sequential. I need to be able to use this proc for paging and sort on any column. As it now stands the procedure omits rows where the ID is not in sequence. This is a problem - any ideas?
tx,
Matt
This proc
May 5, 2008 at 7:34 pm
Yes, use one of the many techniques that we posted here for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 5, 2008 at 7:43 pm
ok, sorry about that - I will revisit the thread.. tx again,matt
May 5, 2008 at 11:17 pm
[font="Verdana"]
rbarryyoung (4/30/2008)
Mahesh Bote (4/28/2008)
[font="Verdana"]Second approach is much better than first one. It will decrease the network traffic tremendously.[/font]I'm not sure that I follow this, Mahesh. Could you explain why?
Sorry rbarryyoung, I couldn't reply immediately. I was out of station. By the way, I suggested following the 2nd approach because it fetches all the data once and binds to Datagrid control. And Datagrid supports paging feature so when you navigate through the Datagrid you did not need to connect every time to DB to fetch data. Update me if I am going in wrong direction.
Mahesh
[/font]
MH-09-AM-8694
May 6, 2008 at 9:28 am
I believe:
The .Net Datagrid with custom paging WILL need to connect to the DB every time the user changes the page. In fact, with "default" paging the Datagrid still retrieves (all records) from the DB every time the user changes the page.
May 6, 2008 at 11:06 am
Yeah! Got it working in SQL Server! Thanks to Barry, Matt, Jeff, and this forum!
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ID, Name, Address
FROM
(SELECT ID, Name, Address,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM MyTable
)
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
- p.s. where can I put WITH (NOLOCK) ? I tried several places but received errors..
thanks again,
matt
May 6, 2008 at 12:16 pm
It would go after the table name. Are you sure you want it? (Can result in dirty data.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 4:41 pm
matt6749 (5/6/2008)
Yeah! Got it working in SQL Server! Thanks to Barry, Matt, Jeff, and this forum!DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ID, Name, Address
FROM
(SELECT ID, Name, Address,
ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
FROM MyTable
)
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +
' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'
- p.s. where can I put WITH (NOLOCK) ? I tried several places but received errors..
thanks again,
matt
Thanks for posting your solution, Matt. 🙂
--Jeff Moden
May 6, 2008 at 4:44 pm
lol - i wish i could have developed this solution!! - all you guys
May 6, 2008 at 11:50 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2008 at 12:22 am
[font="Verdana"]
matt6749 (5/6/2008)
I believe:The .Net Datagrid with custom paging WILL need to connect to the DB every time the user changes the page. In fact, with "default" paging the Datagrid still retrieves (all records) from the DB every time the user changes the page.
:w00t: Well in that case, I will not suggest the 2nd option, coz I used to work on .Net datagrid almost a year and half back. Sorry for wrong posting.
Mahesh[/font]
MH-09-AM-8694
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply