yuvipoy (12/12/2013)
Having a table with 100 columns and 1 million records.Select Count(*) from Mytable;
COUNT(*)
----------
1001920
Time taken to return is 15 sec
select * from Mytable;
Time taken to return is 02:15 min
select * from Mytable order by some_coumn desc;
Time taken to return is 04:58 min
There is no primary key for the column which is ordered by
Order by is taking more time.
I need to show to client some 1000 records which are order by then filter condition
Conditions:
Need to Order by my data based on sort condition client specified
Then apply filter condition
sample
Select * from mytable order by some_column and Where condition like 1 to 1000 and 1001 to 2000 and so on.....
Note there are no Joins just straight select with one primary key
How to improve the query ?
Post the ddl for the table and the indexes. Post a few rows of sample data too if you can.
One million rows is trivial, the count would normally return within a second or so.
The timing for "select * from Mytable" is meaningless, most of the time will be network latency and screen refresh.
This doesn't make sense - "Select * from mytable order by some_column and Where condition like 1 to 1000 and 1001 to 2000 and so on....." - what are you trying to do here?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden