RowCount paging - 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 2005 Forums

Transact-SQL (2005)

RowCount paging

Author

Topic

cda2007
Starting Member

17 Posts

Posted - 2010-03-22 : 16:53:14

Hello.

At this time I have the following paging procedure which is very unreliable taking in consideration I return more than 3000 rows at first query :
ALTER Procedure [dbo].[getProduseFiltre]
(@Ca_ID int,

@NrPag int,
@ProdusePePagina int,
@NrTotalProduse int = 0 OUTPUT,

@PretMinim decimal(9,2) = NULL,
@PretMaxim decimal(9,2) = NULL,

@list NVARCHAR(2000) = NULL,
@listDelimitator NVARCHAR (3) = ',',

@OrderBy int = NULL)

AS

----declare the sql to be executed
DECLARE @sql nvarchar(4000)

SELECT @sql ='
----declare table
DECLARE @ProduseFiltre table
(RowNumber INT,
Produs_ID INT,
PM_ID INT,
PM_Nume varchar(150),
Model varchar(150),
Pret decimal(9,2),
Imagine varchar(100),
PM_Imagine varchar(100),
Stoc int,
Ca_ID int)

Insert into @ProduseFiltre
SELECT ROW_NUMBER() OVER (ORDER BY ps.Produs_ID) AS Row,
ps.Produs_ID,
pm.PM_ID,
pm.PM_Nume,
p.Model,
p.Pret,
p.Imagine,
pm.PM_Imagine,
p.Stoc,
c_pm.Ca_ID

FROM dbo.Produse p INNER JOIN
dbo.Produs_Specificatii ps ON p.Produs_ID = ps.Produs_ID INNER JOIN
dbo.ProdusMare pm ON p.Produs_Parent_ID = pm.PM_ID INNER JOIN
dbo.Categorii_ProdusMare c_pm ON pm.PM_ID = c_pm.PM_ID
WHERE c_pm.Ca_ID = @Ca_ID'

IF @PretMinim IS NOT NULL
SELECT @sql = @sql + ' AND p.Pret >= @PretMinim '

IF @PretMaxim IS NOT NULL
SELECT @sql = @sql + ' AND p.Pret <= @PretMaxim '

IF @list IS NOT NULL
SELECT @sql = @sql + ' AND ps.pSpecValori_ID in (Select items from dbo.Split(@list, @listDelimitator))'

IF 1 =1
SELECT @sql = @sql + ' GROUP BY ps.Produs_ID, pm.PM_Nume, p.Model, p.Pret, p.Imagine, pm.PM_Imagine, p.Stoc, pm.PM_ID, c_pm.Ca_ID '

IF @list IS NOT NULL
SELECT @sql = @sql + ' Having count(*) = (Select count(*) from dbo.Split(@list, @listDelimitator))'

IF 1 = 1
SELECT @sql = @sql + ' -- return the total number of products using an OUTPUT variable
SELECT @NrTotalProduse = COUNT(Produs_ID) FROM @ProduseFiltre

SELECT RowNumber, Produs_ID, PM_ID, PM_Nume, Model, Pret, Imagine, PM_Imagine, Stoc' +
' FROM @ProduseFiltre ' +
' WHERE RowNumber >= (@NrPag - 1) * @ProdusePePagina
AND RowNumber <= @NrPag * @ProdusePePagina'

IF @OrderBy = 3
SELECT @sql = @sql + ' ORDER BY p.Pret '

IF @OrderBy = 4
SELECT @sql = @sql + ' ORDER BY p.Pret DESC '

IF @OrderBy = 1
SELECT @sql = @sql + ' ORDER BY PM_Nume '

IF @OrderBy = 2
SELECT @sql = @sql + ' ORDER BY PM_Nume DESC '

IF @OrderBy IS NULL
SELECT @sql = @sql + ' ORDER BY PM_Nume '

EXEC sp_executesql @sql, N'@Ca_ID int,

@NrPag int,
@ProdusePePagina int,
@NrTotalProduse int OUTPUT,

@PretMinim decimal(9,2),
@PretMaxim decimal(9,2),

@list NVARCHAR(2000),
@listDelimitator NVARCHAR(5),
@OrderBy int',

@Ca_ID,

@NrPag,
@ProdusePePagina,
@NrTotalProduse OUTPUT,

@PretMinim,
@PretMaxim,

@list,
@listDelimitator,

@OrderBy

I found a faster way to achieve paging but I don't know how to make it work for me and return the number of total pages after query. The procedure I found is:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Paging_RowCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Paging_RowCount]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE Paging_RowCount
(
@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

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK

/*Find the @PK type*/
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*Set sorting variables.*/
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
BEGIN
IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END

IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
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 = @SortTable AND c.name = @SortName

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 @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
'
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Please help me make it work.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-23 : 13:28:47

CREATE PROCEDURE Paging_RowCount
(
@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

Is it working if your data return from more than 1 table in DB?

cda2007
Starting Member

17 Posts

Posted - 2010-03-23 : 14:23:18

It should but I don't know how to make it work

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 15:01:23

I think you're trying to do too much with the one procedure. It's too generic. Make it more specific. eg, don't pass field and table names to the procedure. If you need to paginate 3 tables/queries, then have 3 pagination procedures, one for each. This will mean several simple, similar procedures, rather than one complex procedure that takes longer to run, and will be more difficult to maintain.

Secondly, avoid EXEC(@SQL) when ever you can. These procedures need to generate a new execution plan every time they're run.

Here's a really simple pagination procedure that works on the AdventureWorks.Person.Address table. Notice how simple it is, and how easy it is to read, compared to yours.

DECLARE @PageNum INT
DECLARE @PageSize INT

SET @PageNum = 1
SET @PageSize = 20

SELECT AddressID, AddressLine1, AddressLine1, City, StateProvinceID, PostalCode, rowguid, ModifiedDate
FROM (
	SELECT *, ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY ModifiedDate, AddressID) AS Rownum
	FROM Person.Address )z
WHERE Rownum > (@PageNum - 1)* @PageSize 
	AND Rownum <= (@PageNum) * @PageSize 
ORDER BY ModifiedDate, AddressID

There are 10 types of people in the world, those that understand binary, and those that don't.

cda2007
Starting Member

17 Posts

Posted - 2010-03-23 : 16:11:30

If I have 5000 records the performance would be awful. If I have 300,000 it would be a disaster. The query I found is as you say a generic one that's why I'm asking for your help in making a combination of the two queries with the best performance.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 18:08:13

quote:
Originally posted by cda2007

If I have 5000 records the performance would be awful. If I have 300,000 it would be a disaster. The query I found is as you say a generic one that's why I'm asking for your help in making a combination of the two queries with the best performance.

You missed the point. The query I gave was just an example. I haven't tested it for performance. It wasn't meant to provide a final solution. It was an example of a much simpler approach. The point I was making was more of a general observation. It's usually better to have several object, each tailored to a specific purpose, then one general purpose object that does everything.

Having looked at your second sp, I believe there is a problem with schema. It appears to assume dbo schema. If you use something like 'Person.Address' in the @Table field, the query that sets @type and @prec fails to return any results. Hope this helps.

There are 10 types of people in the world, those that understand binary, and those that don't.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-23 : 23:36:55

Hi cda2007

I did not test your solution, but it looks slow and danger because of using Exec

Long time ago, I did develop paging. It is close to what DBA in the making presents. I tested it with 100000 rows, and it works fine.

If you do not have completed solution yet, let me know, I will find it out from my old code lib or someone in this forum may post a better one.

cda2007
Starting Member

17 Posts

Posted - 2010-03-26 : 16:06:45

I would very much like the paging solution you propose. Thanks a million.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources