0

following query is extremely slow.

Slow (1min 57 sec): select top 50000 * from Digital where identitycol > 1350000

Fast (3 sec): select top 50000 * from Digital where identitycol > 100

the table has about 3.000.000 rows. Is there a way to speed up this query?

sharkyenergy
  • 3,842
  • 10
  • 46
  • 97
  • an index perhaps....? – Mitch Wheat Oct 18 '13 at 11:27
  • the column identitycol is not present by default in the database. i am adding it just before executing this query.. is it Worth adding an index? how long will that take? – sharkyenergy Oct 18 '13 at 11:29
  • @sharkyenergy - What do you mean you are adding it? Why? How? – Lieven Keersmaekers Oct 18 '13 at 11:32
  • i need to go through all the lines of the database, and do it in blocks of 50000 rows. to reduce the load on the server (very old machine) i add the column with this: `alter table Digital add "identitycol" int identity(1,1)` when i have finished going throguh the table (after 2 hours) i remove the column again. – sharkyenergy Oct 18 '13 at 11:36
  • Doesn't the table have a clustered index? Why not base the where clause on that? And if it doesn't, why not keeping the identity column and make that the clustered index *(or at least add an index to it)*. – Lieven Keersmaekers Oct 18 '13 at 11:37
  • i googled and people said that in order to make a query with a range i need a "nested" query. i tried it, it worked, but was very intensive and somewhen run out of memory. so i tried this aproach. could you please make an example of what you mean? – sharkyenergy Oct 18 '13 at 11:39
  • @sharkyenergy - You really should show us the entire query you are working with but in general, you can achieve paging by adding a ROW_NUMBER clause and filter on that. The performance of all this will be highly dependant on your current table structure and you haven't shown us anything about that yet. – Lieven Keersmaekers Oct 18 '13 at 11:48
  • @LievenKeersmaekers thanks, row_number was my very first try, but the query timed out before it finished execution. thats the reason why i added this column in the first place. – sharkyenergy Oct 18 '13 at 11:57
  • @sharkyenergy - A 3M table isn't that large. You should either show us the DDL and perhaps even the statements you've tried or start to close this question . – Lieven Keersmaekers Oct 18 '13 at 12:19

0 Answers0