April 30, 2008 at 8:51 pm
The query that I posted above has been doing pretty well. I'm testing it on a 3-million row table with substantial column width (sysobjects plus about 200 more bytes) against a non-indexed lookup column and it consistently returns 20 rows in about 5-6 seconds. Even when I go two million rows in it still only takes 8 seconds. And that is on my laptop which has the distinct disadvantge of having the MDF, LDF and TempDB all on the same disk.
[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]
April 30, 2008 at 8:56 pm
If you were in charge of a project, would you tolerate a 5-6 second lag time even on millions of rows? Rhetorical question, for sure because I know you wouldn't.
--Jeff Moden
April 30, 2008 at 9:28 pm
Thanks Jeff - would be good to see that thread..
April 30, 2008 at 10:37 pm
I found it... it's not as robust as I thought it was, but it does have several pieces of test code along with some reasons why some works better than others. Found out why the dynamic SQL works better (forces recompile) and how to make TOP (XX) code work faster (force a recompile), etc...
http://qa.sqlservercentral.com/Forums/Topic478168-338-1.aspx#bm482005
--Jeff Moden
April 30, 2008 at 11:25 pm
That example can't really be used here Jeff because it assumes the existence of a useable RowNum column and that just doesn't exist in the general Web Page/Rows case. Most of the work in my query is actually maikng a useable row number column. And most of the rest of the time goes to dealing with the wide-rows which is also typical for the general Web-Page# problem. (If I drop these two constraints, my approach runs 100 to 1000 times faster).
Do you have a version that works in these more general cases?
[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 1, 2008 at 6:25 am
You don't actually need a numbered column using the TOP (XX) methods... just need a good set of indexes and the correct "sort".
--Jeff Moden
May 1, 2008 at 8:35 am
None of the TOP(XX) methods that I have been testing are able to keep up with the Row_Number() technique that I posted.
[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 1, 2008 at 1:13 pm
I love testing... got any test code you'd like to share? Paging is always one of those PITA's that people (not you) have a hard time getting their arms around and some good quantitative test code just might help folks out.
If not, I'll make some. Lemme know.
--Jeff Moden
May 1, 2008 at 7:47 pm
OK, well I am going away for the weekend and I don't have time to clean it up, so you'll have to take it as is...
Here is the Test-Rig (everything is SQL2005):
Select Identity(int) as RowID --PK/Cluster index column
--This is a unique integer column, that is NOT the PK, good for non-index tests:
, cast(-1 as bigint)*((select count_big(*) from master..sysobjects)*o1.id + o2.id) as NegPkID
--this is a unique string column, that only partly conforms to the PK
, o1.xtype+o2.name + o2.xtype+o1.name + cast(o1.uid as varchar(9)) + cast(o2.uid as varchar(9)) as StrPK
, cast(o1.id as bigint)-o2.id as DiffID --non-unique integer, for grouping
, cast(o1.id as bigint)+o2.id as AddID --non-unique integer, for different grouping
, o2.* --all of the object fields, but skewed from the PK
Into MegaTest3
From master..sysobjects o1
Join master..sysobjects o2 ON 1=1
ALTER TABLE MegaTest3 ADD PRIMARY KEY CLUSTERED (RowID)
On my laptop this makes a table with over three million rows with non-trivial rows, approx. 200-300 bytes wide, with lots of good grouping and sorting columns.
Here are the Row_Number() and TOP(XX) test queries:
Set statistics time Off
Declare @startingRow int, @rows int
Select @startingRow = 1500000, @rows = 20
print '**'
print 'row_number() methods:'
Set statistics time ON
--Non-clustered, wide-result row test:
Select * From MegaTest3 m
Where m.NegPkId IN( Select NegPkID from
(select NegPkID, row_number() over (order by NegPkID) RN
from MegaTest3) r
Where rn between @startingRow and @startingRow+@rows-1
)
--Non-clustered index narrow-result test:
Select * From (select RowID, NegPkID, row_number() over (order by NegPkID) RN
from MegaTest3) r
Where rn between @startingRow and @startingRow+@rows
--Clustered index wide-result test:
Select * From (select *, row_number() over (order by RowID) RN
from MegaTest3) r
Where rn between @startingRow and @startingRow+@rows
--Clustered index narrow-result test:
Select * From (select RowID, NegPkID, row_number() over (order by RowID) RN
from MegaTest3) r
Where rn between @startingRow and @startingRow+@rows
--======TOP(XX) routines
Set statistics time Off
print '**'
print 'TOP(XX) routines:'
Set statistics time ON
--Non-clustered, wide-result row test:
Select * From MegaTest3 m
Where m.NegPkId IN( Select TOP (@rows) NegPkID from
(select TOP(@startingRow+@rows) NegPkID
from MegaTest3
Order by NegPkID) r
Order By NegPkID Desc)
--Non-clustered index narrow-result test:
Select TOP(@rows) * From (select TOP(@startingRow+@rows) RowID, NegPkID
from MegaTest3
Order by NegPkID) r
Order by NegPkID Desc
--Clustered index wide-result test:
Select TOP(@rows) * From (select TOP(@startingRow+@rows) *
from MegaTest3
Order by RowID) r
Order by RowID Desc
--Clustered index narrow-result test:
Select TOP(@rows) * From (select TOP(@startingRow+@rows) RowID, NegPkId
from MegaTest3
Order by RowID) r
Order by RowID Desc
A note about these tests: one of the things that you will notice is that because of the non-trival nature of the test table and the queries, caching is very significant. It usually takes 3 or 4 runs before the numbers stabilize. Also, the intermediate storage can be quite large, so physical seperation of TempDB and the MDF is likely to have a significant impact (I have not been able to test this on a real server yet).
[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 1, 2008 at 8:37 pm
Well, you guys are way more advanced than me!! It's impressive. I'm trying to code an ASP.NET datagrid page with custom paging, table only will have 10,000 records or so. From another SQL Server Central thread I copied this from Matt Miller:
----------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_Cursor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_Cursor]
GO
CREATE PROCEDURE Paging_Cursor (
@Tables varchar(1000),
@pk varchar(100),
@sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS
/*Find the @pk type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int
IF CHARINDEX('.', @pk) > 0
BEGIN
SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
END
ELSE
BEGIN
SET @PKTable = @Tables
SET @PKName = @pk
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)
SET STATISTICS time ON
--===== 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 STATISTICS time OFF
PRINT REPLICATE('=',50)
SET STATISTICS time ON
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)
SET STATISTICS time OFF
--------------------------------------------
I will try to use the above, but my table doesn't have a RowNum column..! I''m rather confused by now. Can someone post some code that I can plop into my db and use it (with asp.net)?? Thanks a lot..!!
I also saw this analysis of paging, but am so confused by now:
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
tx,
matt
matt
May 1, 2008 at 9:43 pm
Matt -
In your case - "Rownum" would be whatever you're using to sort your dataset (preferrably indexed if you want any kind of performance , ideally the clustered index). I think you're ordering on employeeID - so replace rownum with that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 1, 2008 at 10:30 pm
hmm...kind of looks to me that there's a combination of several things of code. One of which is the beginning to dynamically build the statement (based on whatever happens to be the PK), one of which is the paging process itself.
The find the PK type doesn't look to be used at all, so unless you want to work that into your Dynamic SQL somehow - no, I don't think you need any of that (all of the way up to --==Declare the local variables)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 2, 2008 at 9:48 am
Thanks for the info Matt: I've whittled it down to this:
CREATE PROCEDURE Paging_Cursor (
@PageNumber int = 1,
@PageSize int = 10)
AS
--===== Declare the local variables
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
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 Test.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)
------------------
However, I don't understand what @Skip = 90000 is. Do I need this - can you help?
tx, Matt
p.s. this will work on a table without an "identity" column, right?
May 2, 2008 at 10:15 am
matt6749 (5/2/2008)
However, I don't understand what @Skip = 90000 is. Do I need this - can you help?
It's the number of rows to skip over and should normally be set to:
Set @Skip = @PageSize * (@PageNumber - 1)
It appears that it is set to 90000 as a test value.
[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]
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply