CODEWITHSUNDEEP
sqlsql-server
ZIXUAN QIU
ZIXUAN QIU

Reputation: 27

SQL query: How to select the first 100000-200000 rows in a huge table

I know the normal way should be:

SELECT *
FROM mytable
ORDER BY date
OFFSET 100000 ROWS
FETCH FIRST 100000 ROWS

However, when mytable has 80 Million rows, the "ORDER BY" command will takes a long time to run. To me, the order doesn't matter, I just want to download 100,000 rows of data one at each time. Is there any good way to achieve it?

Upvotes: 0

Views: 3666

Answers (2)

Biggum
Biggum

Reputation: 372

If the order doesn't matter, just don't use it. But the correct point is to follow @juergen instructions. Try to order always on indexed columns. I'm not sure if you fetch 100k lines at once, the system will load 100k rows to memory. But when you process this, the loop will work over the 100k rows and will end.

¿Could you explain why order does not matter to you? Without that, you can get repeated rows within different fetches.

If you use order, you ensure to not get repeated rows.

If th query is slow, create an index. In this kind of select, I'm not sure if pruning would help, as you have no where clause.

Upvotes: 0

juergen d
juergen d

Reputation: 204784

The order by only takes a long time because you use a column without an index on it. Use an indexed column like an id column in your order by.

Or add an index on date

Upvotes: 4

Related Questions