ROW_NUMBER is very slow on large tables

Hi all,
I thought that ROW_NUMBER is designed to handle paging on large tables.
However, when I test it on a table with 1,000,000 records, a simple select
with ROW_NUMBER hangs for more than 10 minutes.
Any idea what can be done?
My table is:
Events (ID, Date, Desc)
My query is:
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100You still need to have an index on the column ID. Also maybe you have
other issues such as blocking and the problem is not with the
row_number function.
Adi|||I think your query is wrong.
try this and let me know if it works
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events order by id asc
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100|||Thank you, but this query is just as slow as mine.
"Omnibuzz" wrote:

> I think your query is wrong.
> try this and let me know if it works
> SELECT * FROM
> (
> SELECT TOP 100 *, ROW_NUMBER()
> OVER (ORDER BY ID ASC) as RowNo
> FROM Events order by id asc
> ) as SortedEvents
> WHERE RowNo > 50 and RowNo < 100|||then can you tell me if this query is fast? do you have an index on ID?
SELECT * FROM
(
SELECT TOP 100 *
FROM Events order by id asc
) as SortedEvents|||Thank you.
ID has index.
Could you please elaborate on what is the blocking issue?
I'm testing on a standalone, development server.
Nobody else uses it.
"Adi" wrote:

> You still need to have an index on the column ID. Also maybe you have
> other issues such as blocking and the problem is not with the
> row_number function.
> Adi
>|||Anton,
try this:
SELECT Events .* FROM Events join
(
SELECT TOP 100 id, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
on events.id = SortedEvents.id
WHERE RowNo > 50 and RowNo < 100|||The nesting and the proprietary TOP 100 might be causing problems.
ROW_NUMBER () is new and probalby not well-optimized yet. Keep it
simple
SELECT event_id,
ROW_NUMBER()
OVER (ORDER BY event_id ASC) AS rn
FROM Events
WHERE rn BETWEEN 50 AND 100;|||Nonsense.Several years ago all db vendors acknowledged that all major
db problems had been solved (so they were free to add xml to the engine).
This is just another example of a user underming the operation of the db
by doing something silly and not informing the ng of exactly what it is.
'It's the user stupid' hangs on the wall of all vendors (and in the minds of
most responders:).
On a side note I still do not see any explanation from you from the mind
'set'
to windowing.So yesterdays criminial magically becomes todays most decorated
cop.
Code,code and nothing but code is STILL non-sense.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365856.484616.283960@.j33g2000cwa.googlegroups.com...
>.
> ROW_NUMBER () is new and probalby not well-optimized yet.
>.|||Steve Dassin wrote:
> Nonsense.Several years ago all db vendors acknowledged that all major
> db problems had been solved
Really? Please amuse me by posting an example of some vendor making
such a claim. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--