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 |
|
ndiego4
Starting Member
2 Posts |
 Posted - 2010-12-01 : 17:38:06
|
| I am trying to pull top 100 records from a table that has more then a million records. My query is like this "select top 100 * from tablename where date is between '20101001 23:59:59.340' and '20101002 23:59:59.340' but its timing out. I also tried to do a simple select and ordered it by date desc but it still times out. Any help would be appreciated. Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-12-01 : 17:55:50
|
| Sounds like the date column is not indexed. If you put an index on that it should return results very quickly. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-01 : 18:30:00
|
| I am surprised that a simple query will not find rows from a table with 1 Million rows before the Timout hits.Unless ...The server is very busyThe indexes have not been rebuilt, and statistics updated, for a long timeor some other major inefficiency exists.But as Rob says, if you don;t have an Index on that column adding one will make the query fasterAlso, your use of BETWEEN with a start point of '20101001 23:59:59.340' is somewhat strange. Presumably you ONLY want 2nd October? You might find:[date] >= '20101002' and [date] < '20101003'to be an easier approach. |
|
|
|
ndiego4
Starting Member
2 Posts |
Posted - 2010-12-02 : 14:13:56
|
| Thanks a lot I tried your approach [date] >= '20101002' and [date] < '20101003'and it worked. However, the date/time format for example is "24/11/2010 11:59:59 AM" and I want to pull records only that are loaded at 10am to 10:59am how do I narrow that? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 16:28:27
|
Start from the earliest Date/TimeStop BEFORE one unit after the last Date/Time."one unit after" is usually a round number.Starting from, say, "20101001 23:59:59.340" has problems if the datatype changes - for example, more to DATETIME2 and there are real values between "23:59:59.340" and "00:00:00.000"  e.g. for records from 10:00 to 10:59 that would be:MyDate >= 10:00 ANDMyDate < 11:00 |
|
|
|
|
|
|
How to pull top 100 records from a huge table