Hi Everyone!
We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit when working with SQL Server
2000, but have run into major problems when using it on SQL Server
2005.
The query goes like this:
DECLARE @PageSize int
SET @PageSize = 10
DECLARE @PK int
DECLARE @tblPK TABLE (
PK int NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT KeyID FROM JobTable WHERE KeyID IN(SELECT KeyID FROM JobTable
WHERE Criteria = TRUE) ORDER BY JobTable.KeyID
OPEN PagingCursor
FETCH RELATIVE 1 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 ResultsFields FROM JobTable INNER JOIN @tblPK tblPK ON
JobTable.KeyID = tblPK.PK WHERE Criteria = TRUE ORDER BY
JobTable.KeyID
I know this doesn't look as optimised as it should but there is a lot
happening under the hood to get it to this point. This aside, there
must be a reason why performance suffers so much with SQL 2005?
Inserting a print statement into the cursor loop outputting the date
stamp showed that each iteration was taking approx 4.5 seconds. This
is a problem we never experienced in SQL Server 2000.
Any help would be greatly appreciated!
Thanks in advance,
Steve
