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 |
|
smithygreg
Starting Member
37 Posts |
 Posted - 2010-12-09 : 11:29:59
|
Hi...I am having a problem that seems absolutely impossible, so I will just describe it here and hope someone can understand my frustration..For some imports in my database, I can't select top 1, but I can select top 2,3,4...This query SELECT top 1 InvoiceDate From Transactiondata where importid = 153755 order by InvoiceDate Desc will take forever to run and never return a result...While THIS query SELECT top 2 InvoiceDate From Transactiondata where importid = 153755 order by InvoiceDate Desc Runs in a split second. There are only about 60 records in this import and there is a clustered index on the importid field.Has anyone ever seen anything like this before? I Displayed the Estimated Execution plan and the results from that are different.The Top 1 has a key lookup and in the output list of that is the ImportIDThe Top 2 has a key lookup and in the output list of that is the InvoiceDate.Any ideas?I should note also that this only happens with certain files and I can't figure out what about the files is causing the issue...I have 4 imports..2 work, 2 don't.Also, I have copied the data from one of the imports that don't work to a temporary table and the Top 1 query works fine on that.Well, thanks for any help you can offer!-Greg |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-09 : 11:35:53
|
| The top 1 looks like a bug - can't think why it would try to do that in the query plan.Try clearing the cache - updating statistics - recreating clustered indexsee if any of that helps.What happens if you take off the order by or order by something else?With only 60 rows I'm a bit surprised it doesn't scan - maybe they are wide rows?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-09 : 12:43:23
|
"With only 60 rows I'm a bit surprised it doesn't scan"I read that to be "only 60 rows where importid = 153755" - but I could be wrong  Sounds to me like the statistics are stale / out-of-date.Are you doing some sort of bulk import? If so then maybe the statistics are not being updated (perhaps then are only updated once a week, on a Sunday or somesuch, so will be stale for your newly imported data until then ... unless manually rebuilt (or some sort of AutoRefresh setting is used) |
|
|
|
smithygreg
Starting Member
37 Posts |
Posted - 2010-12-09 : 13:52:24
|
| Well, I don't know what exactly it was, but I deleted the two imports form the database where I was having the issues and then I re-imported them and the processing was handled fine on the newer imports. The Mysteries of SQL Server... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-09 : 14:11:11
|
| "The Mysteries of SQL Server..."Unlikely. More likely your delete + re-import incremented the number of "changes" to the table past the point where the statistics recalculation was automatically triggered |
|
|
|
|
|
|
|
|
Top 1 vs. Top 2