Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahmed3ziz
Starting Member
1 Post |
 Posted - 2011-07-25 : 04:31:41
|
| Hello everyone, I have a table that contain 5.1 million records. one of the fields is the "CreationDate" of type DateTime. I have created non-clustered index on this field. when I execute simple query:SELECT CreationDate FROM TableName it takes 1 minutes and 35 seconds. that is very long time for simple query. can you please advice me how to make this query run much faster?Thanks in advance |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-07-25 : 06:47:08
|
| 1. pin the table into memory.2. put the query into a callable Stored Procedure to help the SQL Engine benefit from cache - on subsequent reads3. partition the table by date (year, month or day)4. split your database5. isolate this database from others - move other databases onto other servers/ other disks, move other software off this server.6. implement more efficient hardware7. Lower your expectations |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-25 : 07:24:08
|
quote:
I have a table that contain 5.1 million records...it takes 1 minutes and 35 seconds...that is very long time for simple query
And if you read 10 rows every second it would take almost 6 days to read 5.1 million rows. I'm not sure who is actually going to look at 5.1 million date values either.You've already created an index on that column, you should confirm if the query optimizer is seeking on that index. If it is, you've done as much optimizing as you can, short of reducing the number of rows returned. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2011-07-26 : 07:22:03
|
| Is that even a useful query? What are you trying to do? |
|
|
|
|
|
|
how to retrieve million of records faster