June 10, 2011 at 11:51 am
hi,
I have a SQL table that is called by a third party app, which means i don't have access to the source code that uses the table.
The select is "select Top 1 mid from....." without any orderby. It has worked for 2 years without an issue.
Recently I lost some data in the table and had to re-insert it from a backup. Now when the above select runs it does not get the Min value but goes for a random value in the middle of the range.
If I run the same select with an orderby it works, but like I said I don't have the ability to fix the select in the compiled .net source. Has anyone ever had any experience with this and is there something on the SQL side I can look at to fix it?
Thanks!
Maureen
June 10, 2011 at 12:03 pm
without an order by it is likely doing an order based on the index. since you had to reinsert records the index is now out order most likely. it could actually be several things but that would be my first guess. Either way I would recommend adding an order by and the problem should go away.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 10, 2011 at 12:06 pm
Query would be helpful. DDL would be more helpful.
Without a sort, TOP 1 will return whatever record is first (random). However I do believe this is influenced by a clustered index. So if data was lost then a new data load happens, your index is fragmented. Perhaps a rebuild?
June 10, 2011 at 12:10 pm
Without an order by there is absolutely no guarantee whatsoever what record a TOP 1 will return. Depends on what order the last operator in the execution plan leaves it it.
If it's worked all this time you've simply been lucky, nothing more. To fix this you need to change the query at source, anything else will break again sooner or later.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 12:17 pm
June 10, 2011 at 12:41 pm
No. Depends on index used, scan mode used, parallelism (and that's completely ignoring joins or other operators)
It's one of those things that will appear to work reliably until something causes the query plan to change...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 1:02 pm
You guys are awesome! Not only were the responses fast, but they definitely pointed me in the right direction.
The table didn't have an index on it (go figure - remember it's a 3rd party app) so I couldn't rebuild it but I could rebuild the table. So I did that and when I inserted the data from the old table I used an order by, just to be safe. Now the Top 1 is working again.
I will email the vendor and let them know they need to fix the select to include an Order By but at least the users can continue.
Thanks!
Maureen
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply