Using temp tables to break up complex queries

  • 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?

  • 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
  • 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

  • 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

  • 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?

  • 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
  • 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.

  • 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
  • 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?

  • 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