December 12, 2008 at 10:10 am
Hello folks,
My company has recently migrated some of our apps from SS2K to SS2K5, and we have noticed that the stored procs which use paging (in the pre-2K5, temp table and ROWCOUNT method) do not work consistently on 2K5. They work consistently in 2K.
Here is a minimal example:
CREATE PROCEDURE dbo.TEST_SS2K5_PAGING
@slice_size INT = 1,
@slice_start INT = 1
DECLARE @user_buffer TABLE (user_id INT)
INSERT INTO @user_buffer
SELECT user_id FROM Users ORDER BY lastname, firstname, user_id
IF @slice_start > 1 -- if @slice_start==1, all records would be deleted!
BEGIN
DECLARE @discard_up_to INT
SET @discard_up_to = @slice_start-1
SET ROWCOUNT @discard_up_to
DELETE FROM @user_buffer
END
SET ROWCOUNT @slice_size
SELECT * FROM @user_buffer
Then, when tested with the following:
EXEC TEST_SS2K5_PAGING @slice_start=1
EXEC TEST_SS2K5_PAGING @slice_start=1000
EXEC TEST_SS2K5_PAGING @slice_start=1
EXEC TEST_SS2K5_PAGING @slice_start=1
I get the results:
user_id
-------
14662 (correct)
user_id
-------
16260 (correct)
user_id
-------
19505 (incorrect -- should be 14662!)
user_id
-------
14662 (correct)
The incorrect results only occur when a @slice_start of 623 or greater is given. The next result will be row 622 + @slice_start of the result set (or, when @slice_size > 1, the results start at row 622 + @slice_start). Across different 2K5 databases and different stored procs, 622 is the magic number.
It seems that a query with @slice_start > 622 will "break" the next result, and a query with @slice_start =< 622 will "unbreak" the next result:
EXEC TEST_2K5_PAGING @slice_start=1000 -- 16260, correct, next result will be incorrect
EXEC TEST_2K5_PAGING @slice_start=1000 -- 18929, incorrect, next result will be incorrect
EXEC TEST_2K5_PAGING @slice_start=1000 -- 18929, incorrect, next result will be incorrect
EXEC TEST_2K5_PAGING @slice_start=1 -- 19505, incorrect, next result will be correct
EXEC TEST_2K5_PAGING @slice_start=1 -- 14662, correct, next result will be correct
EXEC TEST_2K5_PAGING @slice_start=1000 -- 16260, correct, next result will be incorrect
EXEC TEST_2K5_PAGING @slice_start=1 -- 19505, incorrect, next result will be correct
Anyone have any insight or experience with this problem? The SPs continue to work fine on our 2K databases. As an interim solution, we've rewritten these SPs to use the WITH ... SELECT ... syntax introduced in 2K5, but it would be very nice to find out what's actually going wrong.
Thanks in advance for any help. And forgive the multiple edits.
-pete
December 12, 2008 at 10:32 am
Modifying your SP to use a Tally table (Sorted DESC so you can more easily see) instead of your user table, I get these results.
20000
19001
20000
20000
Which seems correct.
Modified code below:
ALTER PROCEDURE [dbo].[TEST_SS2K5_PAGING]
@slice_size INT = 1,
@slice_start INT = 1
AS
DECLARE @user_buffer TABLE (N INT)
INSERT INTO @user_buffer(N)
SELECT N FROM Tally ORDER BY N DESC
IF @slice_start > 1 -- if @slice_start==1, all records would be deleted!
BEGIN
DECLARE @discard_up_to INT
SET @discard_up_to = @slice_start-1
SET ROWCOUNT @discard_up_to
DELETE FROM @user_buffer
END
SET ROWCOUNT @slice_size
SELECT * FROM @user_buffer
December 12, 2008 at 10:41 am
That does seem correct. And you're on 2K5? All our 2K5 (9.0 SP2 to be exact) databases exhibit this error.
December 12, 2008 at 11:17 am
Oops, I ran that on my 2K8 test server.
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Try taking advantage of some of the new 2K5 features and see if it fixes the issue. Something like this:
CREATE PROCEDURE [dbo].[TEST_SS2K5_PAGING3]
@slice_size INT = 1,
@slice_start INT = 1
AS
;WITH Paging AS(
SELECT Tally.*,
ROW_NUMBER() OVER(ORDER BY N) 'RowNumber' -- You'd order by LastName, FirstName, User
From Tally
)
SELECT TOP (@slice_size) *
FROM Paging
WHERE RowNumber >= @Slice_Start
EXEC TEST_SS2K5_PAGING3 10,10
EXEC TEST_SS2K5_PAGING3 100,20
EXEC TEST_SS2K5_PAGING3 1,1000
EXEC TEST_SS2K5_PAGING3 1
December 12, 2008 at 11:25 am
For your table, this would likely look like this:
CREATE PROCEDURE [dbo].[TEST_SS2K5_PAGING3]
@slice_size INT = 1,
@slice_start INT = 1
AS
;WITH Paging AS(
SELECT Users.*,
ROW_NUMBER() OVER(ORDER BY lastname, firstname, user_id) 'RowNumber'
From Users
)
SELECT TOP (@slice_size) *
FROM Paging
WHERE RowNumber >= @Slice_Start
December 12, 2008 at 1:56 pm
Garadin (12/12/2008)
Oops, I ran that on my 2K8 test server.Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Try taking advantage of some of the new 2K5 features and see if it fixes the issue. Something like this:
-snip-
Yeah, that is what we have done in the meantime. It would be nice to have a stored proc which works on all our servers, though.
Strangely, the WITH ... SELECT ... version of the SP runs about 4-5x slower than the version using a temp table and ROWCOUNT tricks.
Thanks for the help though.
December 12, 2008 at 2:51 pm
gamache (12/12/2008)
Garadin (12/12/2008)
Oops, I ran that on my 2K8 test server.Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
Try taking advantage of some of the new 2K5 features and see if it fixes the issue. Something like this:
-snip-
Yeah, that is what we have done in the meantime. It would be nice to have a stored proc which works on all our servers, though.
Strangely, the WITH ... SELECT ... version of the SP runs about 4-5x slower than the version using a temp table and ROWCOUNT tricks.
Thanks for the help though.
Indeed. Unfortunately, until someone can figure out why it's not working in the first place, it's hard to fix :ermm:
I was wondering about the performance of the rownumber solution. The way you were doing it seemed really inefficient at first glance... inserting a ton of data, then deleting most of it and returning that... but I have the same misgivings about all the other ways I could think of to do it based on a specific sort order as well. If you can generate and store a rownumber type value ahead of time to get your order down, this query could be way faster. Is this something that is heavily used?
December 15, 2008 at 7:29 am
Garadin (12/12/2008)
I was wondering about the performance of the rownumber solution. The way you were doing it seemed really inefficient at first glance... inserting a ton of data, then deleting most of it and returning that... but I have the same misgivings about all the other ways I could think of to do it based on a specific sort order as well. If you can generate and store a rownumber type value ahead of time to get your order down, this query could be way faster. Is this something that is heavily used?
It's not used all that heavily, and it operates on a set of constantly-changing data, so caching probably isn't necessary or advisable. I had the same feelings about the inefficiency when I first saw it, but it can't really be simplified any further. (I didn't write the SP in question.)
I'm still wondering if anyone else has seen this problem.
December 15, 2008 at 1:46 pm
Well I have learned a few things about SQL Server in the last few hours. Two things which come into play here.
One: 622 is the number of index rows in one node of btree. I guess this means SQL Server uses btrees of order 622 internally. I am not sure about further significance of this number. Nice to know, and it indicates an off-by-one error when accessing the btree, but doesn't solve my problem.
Two: clearing the procedure cache with DBCC FREEPROCCACHE ensures a correct result for the next query. THIS, a one-line cure-all, is what I was looking for as a solution. Of course there are side-effects; in 2K5 you can't specify a particular procedure or query, just clear the entire procedure cache. This will cause a temporary performance hit when the cache is cleared, but I think this is OK in my situation.
You learn something new every day...
-pete
December 15, 2008 at 2:26 pm
gamache (12/12/2008)
CREATE PROCEDURE dbo.TEST_SS2K5_PAGING
@slice_size INT = 1,
@slice_start INT = 1
[highlight]DECLARE @user_buffer TABLE (user_id INT)[/highlight]
INSERT INTO @user_buffer
SELECT user_id FROM Users ORDER BY lastname, firstname, user_id
IF @slice_start > 1 -- if @slice_start==1, all records would be deleted!
BEGIN
DECLARE @discard_up_to INT
SET @discard_up_to = @slice_start-1
SET ROWCOUNT @discard_up_to
DELETE FROM @user_buffer
END
SET ROWCOUNT @slice_size
[highlight]SELECT * FROM @user_buffer[/highlight]
The problem here is that you have no primary key(which is clustered by default) on your table variable and (even more importantly) no ORDER BY on your final Select.
Under these conditions SQL Server is free to store the input in any physical order that it feels is expedient and to return them in any order that is convenient. As it happens, your new SQL 2005 environment is finding it expedient to store and return the rows in an order different from the order that you are inserting them. You never saw this on your SQL 2000 environment, simply because it never saw a reason to do it.
So this is not an error in SQL Server 2005, but actually a day 1 bug in this procedure that you were fortunate to never see before now. You can fix this easily with a Primary Key & ORDER BY, but you are probably better off converting to the ROW_NUMBER() method.
[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]
December 15, 2008 at 2:28 pm
December 15, 2008 at 2:48 pm
Barry is my hero today. Thank you for pointing that out, and what a trivial explanation for a weirdo problem.
December 15, 2008 at 3:45 pm
Glad we could help, and thanks for the kudos Gamache.
[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 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply