Using temp tables to break up complex queries

Question

Using temp tables to break up complex queries

sqlguy-736318

Hall of Fame

Points: 3859
More actions
December 5, 2008 at 6:19 pm

#198544

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dva2007 SSCertifiable Points: 7707 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 8

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?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software