Yesterday i got a query that was performing VERY bad. It had 4 joined tables and 4 subqueries. Nothing REALLY complicated, but it was taking 80-100 seconds to execute. That's right, about 1.5minutes!
First thing that came to my head and usually it helps was: indexes. I created them, but.... they didn't help much in this case.
So i tried to analyze execution plan. What took my attention was that one of the sub-queries had an over 20% cost. But it was a simple one so nothing really could be done there optimization wise.
Next step was to try to change the order of the tables in joins. As "base" table i made the one that had the "bad" sub-query. And - YES, this DID help! From the initial around 100 seconds, the time went down to around 5 seconds.
Lesson learned: trying to optimize a query, pay attention to joined tables order!
Posted At : Aug 20, 2010 19:18 PM
| Posted By : Ed Tabara
Related Categories: SQL
Related Categories: SQL
(27 votes)
Comments
I had no idea the join order would make any difference. I assumed SQL Server takes that into account when it computes the execution plan. Something to keep in mind in future. Thanks.
# Posted By
Gary F
| Aug 22, 2010 17:06 PM
Yeah.. i suppose it's about the number of computed rows. In my case i would bet what helped was that i made as base table the Inner Join table that had the "problem" sub-query. As result that sub-query became part of the WHERE and so could be executed from the start instead of being computed for each row of the JOIN... if my explanations wasn't too confusing :)
# Posted By
Ed
| Aug 22, 2010 17:51 PM
