473,380 Members | 1,387 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

Efficient Store Proc for Paging Very large DataSet using Cursor Approach

This approach I found very efficient and FAST when compared to the
rowcount, or Subquery Approaches.

This is before the advent of a ranking function from DB such as
ROW_NUMBER() in SQL Server 2005 and the likes of it. So
This one works with SQL2000

What do you think?
==The Generic paging Cursor Approach in Stored Procedure

/*
Generic Paging Routine using Cursor approach.
--------------------------------------------

Built to use with ASPNET custom paging. Just pass the parameters
you your query and it builds the dynamic SQL to return only the
requested page.

This seems to be working for me. I tried the other two paging
approaches (1) Paging By RowCount (which has some errors on sorting)
and (2) Paging by Subquery (which is too slow). This procedure
goes to show that cursors are not necessarily evil at all times.

DON'T FORGET TO HANDLE SQL-INJECTION in your code!

ONE CAVEAT/restriction: Primary key type is set to INT. I normally
use identity column anyway.

NOTE: This returns 2 results: 1 for dataset 1 for the total count
which is useful when consumed by ASPNET or the like.

References:
http://www.thecodeproject.com/aspnet...?select=820618

*/

CREATE PROCEDURE uspPagingCursor (
@Fields VARCHAR(1000) = '*',
@Tables VARCHAR(1000) ,
@PK VARCHAR(100) ,
@PageSize INT,
@PageNumber INT = 1,
@Sort VARCHAR(1000) = '',
@Filter VARCHAR(2000) = '' ,
@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

/*

This is the part removed from orig code for speed.
I know my @type is INT always.

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) + ')'

*/

SET @TYPE = ' int '

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)

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

/*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 != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
SET @strFilter = ' WHERE TRUE '
/* SET @strFilter = '' */
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''

/*Execute dynamic query*/
EXEC(
'DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '

DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup
+ ' ORDER BY ' + @Sort + '

OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK

SET NOCOUNT ON

WHILE @PageSize 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PK)
FETCH NEXT FROM PagingCursor INTO @PK
SET @PageSize = @PageSize - 1
END

CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ' + @Fields + ' FROM ' + @Tables + ', @tblPK tblPK ' +
@strFilter
+ ' and ' + @PK + ' = tblPK.PK ' + @strGroup + ' ORDER BY '

+ @Sort
)
EXEC('
SELECT (COUNT(' +@Pk +') - 1)/' + @strPageSize + ' + 1 AS PageCount
FROM ' + @tables + @strFilter
)

GO

==Sample Stored Procedure calling the above proc uspPagingCursor
/************************************************** ***********
Description:

This simply returns page data from tbTransactions Table.

Returns:
Result Set (WHICH web like ASPNET can consume )
Notes:

This invokes the generic paging procedure uspPagingCursor.

************************************************** ***********/

CREATE PROCEDURE uspGetTransactionsPage
@PageSize INT,
@PageIndex INT = 1,
@SortField VARCHAR(1000) = '',
@QueryFilter VARCHAR(2000) = ''
AS
DECLARE @FieldNames VARCHAR(1000)
DECLARE @TableNames VARCHAR(1000)
DECLARE @PrimaryKey VARCHAR(1000)
DECLARE @JoinExpr VARCHAR(1000)

IF @SortField = '' SET @SortField = 'SubmitDate DESC'

/* an identity/unique column is needed for Paging to work */

SET @PrimaryKey = 'rowid'

SET @TableNames = ' tbTransaction, tbResponseCode , tbUser'

/* Put your SQL SELECT Here */

SET @FieldNames =
'
rowid,
MerchantTransactionId,
MerchantIdProcessor,
TransactionOrigin,
SubmitDate,
ExpirationDate,
TransactionAmount,
CustomerName,
AccountNumber
'

/* Put your SQL SELECT JOIN Here */

SET @JoinExpr =
' tbTransaction.ResponseCode *= tbResponseCode.ResponseCode
and tbTransaction.EmployeeId *= tbUser.userid
'
IF @QueryFilter = ''
SET @QueryFilter = @JoinExpr
ELSE
SET @QueryFilter = @JoinExpr + ' AND ' + @QueryFilter
EXEC uspPagingCursor @FieldNames, @TableNames, @PrimaryKey,
@PageSize, @PageIndex, @SortField, @QueryFilter, NULL
GO

Aug 23 '06 #1
0 3354

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Jo | last post by:
Hi, I am using a OleDB provider that connects to ANY datasource - I do not know if these have some nice ID that I can page on. Using the OleDataAdapter FILL method. I then fill the dataset...
11
by: hoopsho | last post by:
Hi Everyone, I am trying to write a program that does a few things very fast and with efficient use of memory... a) I need to parse a space-delimited file that is really large, upwards fo a...
3
by: Robert Schuldenfrei | last post by:
Dear NG, I am making progress processing trees. I can "span" a tree if I make wise use of the C# stack methods, push() and pop(). What I want to place on the stack are rows from a data table. ...
1
by: Patrick.O.Ige | last post by:
I have been paging with DataSet using "DataGridPageChangedEventArgs" and i guess it works with only Dataset because default paging requires that the DataGrid be able to determine the number of...
2
by: Daniel Walzenbach | last post by:
Hi, I have a question regarding the DataGrid control. If paging is enabled the grid binds the data, sets the paging on the top/bottom (or however it is set up) and throws away unnecessary...
15
by: Macca | last post by:
Hi, My app needs to potentially store a large number of custom objects and be able to iterate through them quickly. I was wondering which data structure would be the most efficient to do this,a...
5
by: sql_er | last post by:
Guys, I have an XML file which is 233MB in size. It was created by loading 6 tables from an sql server database into a dataset object and then writing out the contents from this dataset into an...
1
by: rbrowning1958 | last post by:
Hello, I wonder whether someone can explain to me how data is fetched from a database server when using ASP.NET 2.0's gridview with paging enabled? My SQL DataSource has a simple "select * from...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.