December 5, 2008 at 6:19 pm
I was in an interview and I was asked about how to improve the performance of a query.
This particular query was supposed to join 5 tables and the data model could not be modified.
One thing I mentioned was to use temp tables to break up the query so the single query wouldn't have to do so much processing.
Based on my experience, when a single query is required to perform an increasing amount of processing the performance hit can become exponential. And I know that if a query has 5 or more tables then this indicates that the query should be refactored.
So do you agree with my answer? How would you better articulate the nuts and bolts behind my statement?
December 6, 2008 at 1:21 am
sqlguy (12/5/2008)
And I know that if a query has 5 or more tables then this indicates that the query should be refactored.
Why 5? What's magic about a 5 table join that 'requires' temp tables?
So do you agree with my answer? How would you better articulate the nuts and bolts behind my statement?
Personally, no. I would have looked at indexing first. It it's a simple 5 table join (no aggregates, no subqueries) then I doubt a temp table would make it better. In fact, I've several times improved performance by removing a temp table from a stored procedure and letting SQL process everything at once.
Where a temp table becomes useful for intermediate storage depends on the size of the tables, the complexity of the query, the volumes of data that will be returned by the query, the state of the server, etc.
That's why most people, when asked if a temp table would be faster will say 'It depends, try both and see which works better'
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
December 6, 2008 at 2:50 am
I agree with indexing on table field which is used in the join. May be check execution plan and see table scan or index scan.
This article is nice for you to read.
http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables
regards,
vijay
December 6, 2008 at 4:41 am
I guess I need to go back to those 25 table joins that are running in under 50ms and start adding temp tables...
Nah. I'll stick with getting indexing right, and structuring the query so that it's set based, joins the tables on the appropriate keys doesn't have any funky constructs in the WHERE clause or the JOINs that prevent index usage. I'd check the wait states to see what, if anything is blocking this procedure, because maybe it's fine, but the performance tuning is needed elsewhere.
That's the sort of stuff I'd expect to hear about in an interview on tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2008 at 9:10 am
Grant Fritchey (12/6/2008)
I guess I need to go back to those 25 table joins that are running in under 50ms and start adding temp tables...Nah. I'll stick with getting indexing right, and structuring the query so that it's set based, joins the tables on the appropriate keys doesn't have any funky constructs in the WHERE clause or the JOINs that prevent index usage. I'd check the wait states to see what, if anything is blocking this procedure, because maybe it's fine, but the performance tuning is needed elsewhere.
That's the sort of stuff I'd expect to hear about in an interview on tuning.
OK - so it sounds like the best answer is to make sure that the columns used in the join are indexed.
You mentioned that there are some types of joins that could prevent index usage. Can you give me an example of this?
December 6, 2008 at 1:01 pm
sqlguy (12/6/2008)
Grant Fritchey (12/6/2008)
You mentioned that there are some types of joins that could prevent index usage. Can you give me an example of this?
CAST(Table1.Col1 AS INT) = CAST(Table2.Col1 AS INT) is a classic example. Any form of function (even one that doesn't change the value) prevents index seeks. This includes UPPER (which I see used a great deal in case insensitive databases for some reason), any conversion including implicit conversion, date manipulation, etc.
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
December 6, 2008 at 1:23 pm
GilaMonster (12/6/2008)
sqlguy (12/6/2008)
Grant Fritchey (12/6/2008)
You mentioned that there are some types of joins that could prevent index usage. Can you give me an example of this?CAST(Table1.Col1 AS INT) = CAST(Table2.Col1 AS INT) is a classic example. Any form of function (even one that doesn't change the value) prevents index seeks. This includes UPPER (which I see used a great deal in case insensitive databases for some reason), any conversion, including implicit conversion, date manipulation, etc.
Right - I knew that including an indexed column in a function would negate the index. This is a problem with including an indexed column in a function. This is not specific to joins, though. Are there any use cases specific to joins (rather than functions) that will negate an index? This is what I was inferring from the previous post.
December 6, 2008 at 1:31 pm
There's very little difference, as far as the query optimiser's concerned, between a join condition and a where clause predicate.
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
December 7, 2008 at 3:20 am
dva2007 (12/6/2008)
I agree with indexing on table field which is used in the join. May be check execution plan and see table scan or index scan.
It seems like columns that are joined are typically PK's or FK's (at least in my queries).
Can you think of an example where a column would not be a PK or FK but would still need to be joined on?
December 7, 2008 at 7:24 am
JOINs are pretty much by definition going to be predicated on the PK, an FK or an alternate key that can function as linking mechanism for an FK. You can throw other columns in as a part of the JOIN cluase, but as Gail has pointed out, that's because WHERE clause filtering and JOIN filtering are not very different at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply