February 21, 2012 at 11:08 am
EDIT: Missed page 2
Jared
CE - Microsoft
February 21, 2012 at 11:08 am
EdwinGene (2/21/2012)
Gail,Thank you for your suggestions. Your suggestion about sargable predicates has helped me to find an alternate way to invoke the query to get SQL Server to use the correct index.
Using the query as it was I was finally able to get SQL Server to use the correct index by inserting a Where clause that limited tbl3.ColA to a single value (Where tbl3.ColA = 'value'). Of course, this obviously would not return the result I was trying to get, but it got me thinking along the path which led to a workable solution (in this case).
I tried to come up with a way that I could limit tbl3.ColA with a Where clause, but I just couldn't find a way that would do that and still include all of the values (which could change) from <table2>. Selecting all the values from <table2> and using the In() function didn't work. Even selecting the max value from <table2> and using that in a Where clause (Where tbl2.ColA <= MaxValue) didn't work.
Then it dawned on me that a way to restrict tbl3.ColA to one value would be to use a Cursor and Dynamic SQL. I know - it didn't seem right to me either. However, given that I had stopped the original query test after 15 minutes and that <table2> is very small (just over 1500 rows) and that it would be using the correct index in <table3>, I figured WTF, I'll give it a try.
The Query is now returning a result set in three (count 'em: THREE) seconds, as compared to somewhere north of 15 minutes for the original query.
So, you see, this is why I wanted a response from someone who has seen this before or who has experience in this area. Your "guess", based on your experience, was just what I asked for and just what I needed to enable me to find a solution. And, you didn't need to see my table or index structures to do it.
Thank you very much for your help. This was much more valuable to me than having a solution spelled out for me. Give a man a fish and he eats for one day. Teach a man to fish, and he eats for the rest of his life.
I'm sure Gail will respond as well, but you do realize that with the information requested you most likely could have had a single query return the entire results in fractions of a second? I do know that if looping using a discrete value in each loop, chances are your real problem is an implicit conversion from Table2 to Table3 that is forcing a table/index scan on the larger table.
Using a cursor with Dynamic SQL is a bad idea for several reasons.
1. Indeterminate number of rows to loop through (AKA RBAR)
2. Query plan caching (most likely doesn't happen).
3. SQL Injection.
There are probably several others that more experienced forum members will know off the top of their heads as well.
February 21, 2012 at 11:09 am
No offence intended, but from what you've said that sounds like a completely wrong approach.
A seek is not better than a scan. Forcing a seek with something like WHERE Column < MaxValue is not going to improve performance even if it does convert a scan to a seek (http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/, http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/) and if the optimiser is not picking the 'correct' index, there's almost always a reason.
Dynamic SQL and cursors being faster sounds like there's something trivial wrong with the original plan, maybe stats, maybe something that I could spot in a second with the execution plan. Maybe it'll continue to work, maybe tomorrow that suddenly goes to > 15 minutes for the same reason (whatever it is) that the original query did, either way it is far from an ideal practice.
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
February 21, 2012 at 11:26 am
GilaMonster (2/21/2012)
No offence intended, but from what you've said that sounds like a completely wrong approach.A seek is not better than a scan. Forcing a seek with something like WHERE Column < MaxValue is not going to improve performance even if it does convert a scan to a seek (http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/, http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/) and if the optimiser is not picking the 'correct' index, there's almost always a reason.
Dynamic SQL and cursors being faster sounds like there's something trivial wrong with the original plan, maybe stats, maybe something that I could spot in a second with the execution plan. Maybe it'll continue to work, maybe tomorrow that suddenly goes to > 15 minutes for the same reason (whatever it is) that the original query did, either way it is far from an ideal practice.
Gail, when returning all rows from the table, does a seek even do anything versus a scan?
Jared
CE - Microsoft
February 21, 2012 at 12:47 pm
SQLKnowItAll (2/21/2012)
GilaMonster (2/21/2012)
No offence intended, but from what you've said that sounds like a completely wrong approach.A seek is not better than a scan. Forcing a seek with something like WHERE Column < MaxValue is not going to improve performance even if it does convert a scan to a seek (http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/, http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/) and if the optimiser is not picking the 'correct' index, there's almost always a reason.
Dynamic SQL and cursors being faster sounds like there's something trivial wrong with the original plan, maybe stats, maybe something that I could spot in a second with the execution plan. Maybe it'll continue to work, maybe tomorrow that suddenly goes to > 15 minutes for the same reason (whatever it is) that the original query did, either way it is far from an ideal practice.
Gail, when returning all rows from the table, does a seek even do anything versus a scan?
Define 'do anything'.
It'll have just about the same performance characteristics, it will just appear as a seek in the plan, not a scan. See the blog posts I referenced.
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
February 21, 2012 at 1:02 pm
GilaMonster (2/21/2012)
SQLKnowItAll (2/21/2012)
GilaMonster (2/21/2012)
No offence intended, but from what you've said that sounds like a completely wrong approach.A seek is not better than a scan. Forcing a seek with something like WHERE Column < MaxValue is not going to improve performance even if it does convert a scan to a seek (http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/, http://sqlinthewild.co.za/index.php/2009/07/29/is-a-scan-a-bad-thing/) and if the optimiser is not picking the 'correct' index, there's almost always a reason.
Dynamic SQL and cursors being faster sounds like there's something trivial wrong with the original plan, maybe stats, maybe something that I could spot in a second with the execution plan. Maybe it'll continue to work, maybe tomorrow that suddenly goes to > 15 minutes for the same reason (whatever it is) that the original query did, either way it is far from an ideal practice.
Gail, when returning all rows from the table, does a seek even do anything versus a scan?
Define 'do anything'.
It'll have just about the same performance characteristics, it will just appear as a seek in the plan, not a scan. See the blog posts I referenced.
By 'do anything' I meant improve performance. By your blog posts it is clear to me that in this case, probably not. In fact, forcing the plan to do a seek based on a SARGable predicate that does not give the desired results is quite silly and pointless.
Jared
CE - Microsoft
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply