December 9, 2008 at 9:12 am
Hello,
I'm using Row_Number() to page SQL results and running into an inconsistency in the results returned. When I increase the page size (maximum rows) but keep the same starting row, I would expect to see the smaller set within the larger set. Instead, I get what appears to be an unpredictable resultset--as if the order by is not working.
My goal is to get a stored procedure where I can pass search and sorting parameters with paging functionality. The tables have several million rows.
If anyone has suggestions, I'd really appreciate it. Here's the query:
declare
@DocType char(30) = 'judgements',
@PersonName varchar(100) = '%' + 'stone',
@OrderBy varchar(50) = 'VOODCD',
@StartRowNumber int = 1,
@MaximumRows int = 15
SET NOCOUNT Off;
begin
with Documents as
(
select
distinct dbo.BCPDOF01.VOCERT,
dbo.BCPOFG01.GGNAME,
dbo.BCPOFG01.GGTYPE,
dbo.BCPDOF01.VOODCD,
dbo.BCPDOF01.VONOPG,
dbo.BCPDOF01.VOBOOK,
dbo.BCPDOF01.VOPAGE,
dbo.BCPDOF01.VOASPN,
dbo.CSPSTT01.STDSLN,
dbo.BCPOST01.STSTTP,
dbo.CSPSTT01.STTITL,
ROW_NUMBER() over (order by @OrderBy) as RowNumber
from
dbo.BCPDOF01
inner join
dbo.BCPOFG01
on
dbo.BCPDOF01.VOBTNO = dbo.BCPOFG01.GGBTNO
and
dbo.BCPDOF01.VODOCN = dbo.BCPOFG01.GGBTSQ
inner join
dbo.BCPOST01
on
dbo.BCPOST01.STBTNO = dbo.BCPDOF01.VOBTNO
and
dbo.BCPOST01.STBTSQ = dbo.BCPDOF01.VODOCN
inner join
dbo.CSPSTT01
on
dbo.CSPSTT01.STTITL = dbo.BCPOST01.STSTTP
where GGNAME like @PersonName
)
select *
from
Documents
where RowNumber between @StartRowNumber and
((@StartRowNumber + @MaximumRows) -1)
end
Cheers,
Tess
December 9, 2008 at 9:48 am
you should just run the CTE/Derived table section to confirm RowNumber returns the correct #
I think that this part won't work, as it does NOT sort by the [VOODCD] column as you thought it is (this is not dynamic SQL)
Therefore the order is not guaranteed
ROW_NUMBER() over (order by @OrderBy) as RowNumber
is the same as
ROW_NUMBER() over (order by 'VOODCD') as RowNumber
actual sorting by column code is
ROW_NUMBER() over (order by VOODCD) as RowNumber
Just run the code to double check the last column RowNumber, could be different time each time, irrelevant to paging
select
distinct dbo.BCPDOF01.VOCERT,
dbo.BCPOFG01.GGNAME,
dbo.BCPOFG01.GGTYPE,
dbo.BCPDOF01.VOODCD,
dbo.BCPDOF01.VONOPG,
dbo.BCPDOF01.VOBOOK,
dbo.BCPDOF01.VOPAGE,
dbo.BCPDOF01.VOASPN,
dbo.CSPSTT01.STDSLN,
dbo.BCPOST01.STSTTP,
dbo.CSPSTT01.STTITL,
ROW_NUMBER() over (order by @OrderBy) as RowNumber
from
dbo.BCPDOF01
inner join
dbo.BCPOFG01
on
dbo.BCPDOF01.VOBTNO = dbo.BCPOFG01.GGBTNO
and
dbo.BCPDOF01.VODOCN = dbo.BCPOFG01.GGBTSQ
inner join
dbo.BCPOST01
on
dbo.BCPOST01.STBTNO = dbo.BCPDOF01.VOBTNO
and
dbo.BCPOST01.STBTSQ = dbo.BCPDOF01.VODOCN
inner join
dbo.CSPSTT01
on
dbo.CSPSTT01.STTITL = dbo.BCPOST01.STSTTP
where GGNAME like @PersonName
December 9, 2008 at 10:06 am
Be careful if the tables get into the 50+ million rows row_number() may not give you what you need in terms of performance.
* Noel
December 9, 2008 at 12:01 pm
Thank you for the suggestion; I'll run the CTE alone and see what the row number is.
December 9, 2008 at 12:02 pm
The largest table is ~8 million rows; other tables are about 3 and 5 million.
For performance, would you recommend using the temp table approach?
Cheers
December 9, 2008 at 12:28 pm
Depending on the "width" you could still get into trouble.
So far the fastest way I have seen uses SET ROWCOUNT ...
Let me see if I can find the example.
* Noel
December 11, 2008 at 3:14 pm
You are correct: the CTE does not return the results consistently. The same data row results with a different Row_Number() just by re-running the query. And the order by is not working.
Any suggestions or pointers will be greatly appreciated.
Meanwhile, I may have to go back to the temp table approach.
Thanks very much.
December 11, 2008 at 4:30 pm
Tess (12/11/2008)
You are correct: the CTE does not return the results consistently. The same data row results with a different Row_Number() just by re-running the query. And the order by is not working.Any suggestions or pointers will be greatly appreciated.
Meanwhile, I may have to go back to the temp table approach.
Thanks very much.
You may need to run that statement as dynamic SQL, because as Jerry mentioned - it looks like SQL is thinking that is a constant.
That being said - even when you do that - unless the field you specify is unique - you STILL will not end up with predictable results.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 15, 2008 at 4:03 pm
Thank you. The dynamic SQL version that doesn't use Row_Number()works fine. However, I'm having a problem getting the Row_Number() to work dynamically.
Here's where things break down...
Where I'd like to order by dynamically:
ROW_NUMBER() over (order by ' + convert(nvarchar(50), @OrderBy) + ') as RowNumber
...and further down, where I need to select the ordered results within a range of row numbers:
select *
from
Documents
where RowNumber between (' +
CONVERT(int, @StartRowNumber) + ' and
(' + CONVERT(int, @StartRowNumber) + ' + ' +
CONVERT(int, @MaximumRows) + ' - 1) ) '
If anyone has further ideas or suggestions, please let me know. I'd appreciate your input tremendously.
Cheers,
December 16, 2008 at 8:39 am
Success!
I got the number of tics right, the conversions right, added another column to make the ordering more unique, and ... voila! It works 😀
Thank you everyone for pointing me in the direction of dynamic SQL. That was the key. Here's what I had to do for anyone else who may face similar challenges:
1) Add columns to the "Row_Number() over " statement if necessary in order to get more predictable ordering. Mine looks like:
ROW_NUMBER() over (order by ' + convert(nvarchar(50), @OrderBy) + ', GGName) as RowNumber
2) Get the number of tics right when building a dynamic SQL statement. "Print @sql" (or whatever you name your string) is very helpful here to figure out where the tics are missing. The where clause in my inner query looks like this (three tics on each side of the dynamic interjection):
where GGNAME like ''' + convert(nvarchar(100), @PersonName) + '''
3) Convert the dynamic bits back to nvarchar for sp_executesql. My final query looks like this:
select *
from
Documents
where RowNumber between ' + CONVERT(nvarchar(8), CONVERT(int, @StartRowNumber)) + ' and
( ' + CONVERT(nvarchar(8), CONVERT(int, @StartRowNumber)) + ' + ' +
CONVERT(nvarchar(8), CONVERT(int, @MaximumRows)) + ' - 1)
order by RowNumber
Part of the problem I had is that there are no unique columns in my data model. I'm working with a legacy system and have no options to improve the model. So to get ordering predictability of results, you'll notice I added the "order by RowNumber" to the final query. Now the paging and sorting works well.
Thanks again everyone.
Happy holidays to all and very best wishes for the new year! 🙂
Tess
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply