June 7, 2002 at 3:51 am
Could someone tell me how sql server decides what query plan to use? We looked at a select statement at work that had several joins and date ranges. It was taking several minutes to run. We were trying to clean it up and we took out a few 'upper' statement not thinking it would do anything. The query now returns in a matter of seconds. The Execution plan is completely different for the two queries. Why would an 'upper' cause a different execution plan and such different performance?
Thanks,
Eddie
June 7, 2002 at 4:31 am
This is fairly straightforward: any functions, be it UPPER(), LEFT() or whatever will prevent an index being considered for that part of the WHERE clause....
I hope this answers your question, and some guru doesn't come along and contradict me!!
Regards
Simon
UK
June 7, 2002 at 6:23 am
Im not sure if upper would rule out the index or not - you'd think it would only matter if the column(s) in the index were case sensitive. Using portions of a column is a different matter though - something to avoid if you can, if you're doing it a lot in SQL2K you can create a computed column and index it to speed those operations up.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply