How to get paged data using t-sql
-
Hey all,
I need to build a stored procedure which will give the paged data from a query for an online application.. The inputs provided are pagesize & the page no. I currently having this procedure to get the paged data.. I just want to know that is there any better way to do it.
My select may contains 1000+ records or 100+ records.
CREATE PROCEDURE dbo.usp_GetPagedDataFromProc
(
@iPageIndex INT ,
@iPageSize INT ,
@strTableName VARCHAR(100) ,
@strPrimaryKeyName VARCHAR(100) ,
@strSelectClause VARCHAR(4000) ,
@strWhereClause VARCHAR(4000) ,
@strOrderBy VARCHAR(1000) ,
@iRecordCount INT = NULL OUTPUT
)
AS
BEGIN
DECLARE @iPageLowerBound INT
DECLARE @iPageUpperBound INT
DECLARE @iRowsToReturn INT
DECLARE @strSQL NVARCHAR(4000)
-- First set the rowcount
SET @iRowsToReturn = @iPageSize * @iPageIndex
-- Set the page bounds
SET @iPageLowerBound = @iPageSize * (@iPageIndex - 1)
SET @iPageUpperBound = @iPageLowerBound + @iPageSize + 1
SET @strSQL = ' SELECT * INTO ##TMPRECFILL '
SET @strSQL = @strSQL + ' FROM ' + @strTableName
IF LEN(LTRIM(RTRIM(@strWhereClause))) != 0
BEGIN
SET @strSQL = @strSQL + ' WHERE ' + @strWhereClause
END
EXEC sp_ExecuteSQL @strSQL
SET @strSQL = ' '
SET ROWCOUNT @iRowsToReturn
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1),
TablePrimaryKey int
 
SET @strSQL = 'SELECT @iRecordCount = COUNT(' + @strPrimaryKeyName + ')'
SET @strSQL = @strSQL + ' FROM ##TMPRECFILL'
EXEC sp_ExecuteSQL @strSQL, N'@iRecordCount int OUTPUT', @iRecordCount OUTPUT
SET @strSQL = ''
-- Insert into the temp table
SET @strSQL = 'INSERT INTO #PageIndex ( TablePrimaryKey )'
SET @strSQL = @strSQL + ' SELECT ' + @strPrimaryKeyName
SET @strSQL = @strSQL + ' FROM ##TMPRECFILL'
IF LEN(LTRIM(RTRIM(@strOrderBy))) != 0
BEGIN
SET @strSQL = @strSQL + ' ORDER BY ' + @strOrderBy
END
EXEC sp_ExecuteSQL @strSQL
SET @strSQL = ''
-- Return paged results
SET @strSQL = 'SELECT ' + @strSelectClause
SET @strSQL = @strSQL + ' FROM ##TMPRECFILL '
SET @strSQL = @strSQL + ' WHERE EXISTS ( SELECT NULL FROM #PageIndex WHERE'
SET @strSQL = @strSQL + ' TablePrimaryKey = ##TMPRECFILL.' + @strPrimaryKeyName
SET @strSQL = @strSQL + ' AND IndexID > ' + CAST(@iPageLowerBound AS varchar)
SET @strSQL = @strSQL + ' AND IndexID < ' + CAST(@iPageUpperBound AS varchar)
SET @strSQL = @strSQL + ' ) '
IF LEN(LTRIM(RTRIM(@strOrderBy))) != 0
BEGIN
SET @strSQL = @strSQL + ' ORDER BY ' + @strOrderBy
END
EXEC sp_ExecuteSQL @strSQL
SET @strSQL = ''
DROP TABLE ##TMPRECFILL
DROP TABLE #PageIndex
END
GO
--Ramesh
-
Not sure about the performance of your proc but the following will return the first page of 100 almost instantly and returns the 12,000th page of a 40 column, 1.2 million row table in less than a second... it's also parameter sensitive to page size and page number... it does require at least an index on the key column (CustID in this case) to be that fast. Since it does use dynamic SQL, it would be best if you converted it to a stored procedure (Injection attacks). You'll also need to change the table name and the name of the key column in the code below. The lines with "@Start" are just for measuring duration and should be removed after testing...
Finally, I've not tried it using xp_ExecuteSQL...
declare @start datetime
set @start = getdate()
--===== 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) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 12000
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @sql =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
'
--===== Produce the page
EXEC (@SQL)
print CONVERT(varchar(30),getdate()-@start,114)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Almost forgot... the routine starts with PAGE 0... you can certainly "fix" that by subtracting 1 for the page parameter or by passing in a pre-decremented page number.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just curious. Have you tried using static sql and using a temp table to extract the first derived table then use not exists with that table??
-
Haven't tried any of this using a temp table... what changes do you suggest?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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) --Holds dynamic SQL
Declare @Keys table (CustID int not null primary key clustered)
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 12000
SET @Skip = @PageSize*@PageNum
SET ROWCOUNT @Skip
insert into @Keys (CustID) Select CustID from dbo.Customers order by CustID
SET ROWCOUNT @PageSize
Select C.* FROM dbo.Customers C WHERE NOT EXISTS (Select CustID FROM @Keys K WHERE C.CustID = K.CustID)
SET ROWCOUNT 0
This is all untested. I have others ideas but I don't know if they can always work (depending on how many search critererias are added). Maybe you could do a check in SysIndexes to see how many rows are in the table and change the logic depending on how many rows are gonna be inserted in the temp table and use exists or not exists on always the smallest set of data!?!?
-
I see what you mean, now... I gotta do some tweekin' because page 0 now sets ROWCOUNT = 0 and the last page takes forever... I'll let you know what I come up with...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shoot... these type of paging routines are so much easier in 2005 with TOP @variable...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yup.... but as I said, I was just thinking out loud so it might not work at all
. -
I've made it work a couple of ways but I haven't been able to make it faster than the dynamic SQL solution. I'm going to leave this one be and say use the dynamic SQL solution I wrote. Or, upgrade to 2005 and convert the dynamic solution to a static solution using TOP @variable.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
I just forgot that all the input parameters are optional except @strTableName parameter.
& the snippet will only work if you have a static SQL...
In my case, i have to build the dynamic SQL then return the paged data...
For e.g.
EXECUTE usp_GetPagedDataFromProc NULL , NULL , 'dbo.Customers' , NULL , NULL , NULL , NULL
should return all the columns & rows from the customers table.
--Ramesh
--Ramesh
-
Ramesh,
Can do... but what are the other 4 parameters. If I add table name to the list of parameters, that will bring the current count of parameters up to 3... what are the other 4?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
The parameters used are
@iPageNo = Page Number to get (OPTIONAL)
@iPageSize = Page size (OPTIONAL)
@strTableName = Table name from where to get data
@strSelectClause = column list for the resultset (OPTIONAL)
@strWhereClause = WHERE clause (OPTIONAL)
@strOrderBy = Order BY clause (OPTIONAL)
@iRecordCount = Output parameter that will let me know how many records were returned
....
By the way i used your query to build my own dynamic SQL & its working fine & also boosted the performance as compared to the one which I earlierly used to..
Finally , I need to change this SP to work for the resultset returned by other stored procedure. (Check for my last thread).
--Ramesh
-
Great.. thanks for the info. Glad it helped on performance, too. I'll give it a whirl tonight...
Result set from another store proc? I must have missed that one in your last thread. I'm thinkin' "scope change".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's late and I didn't have a chance to work on this... sorry.
Just to give you a head start, you can mod the code for table name just by replacing Customer with the name of your table as a variable 'codestring' + @strTableName + 'restofcodestring'.
For the optional page size and pagenumber, just add some logic to return everything in the table if either is null. You can do kinda the same thing with the WHERE and the ORDER BY parameters.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply