CODEWITHSUNDEEP
sqlsql-server
Craig
Craig

Reputation: 18694

Should I use a temp table?

I have a report query that is taking 4 minutes, and under the maximum 30 seconds allowed limit applied on us.

I notice that it has a LOT of INNER JOINS. One, I see, is it joins to a Person table, which has millions of rows. I'm wondering if it would be more efficient to break up the query. Would it be more efficient to do something like:

Assume all keys are indexed. Table C has 8 million records, Table B has 6 Million records, Table A has 400,000 records.

SELECT Fields
FROM TableA A
INNER JOIN TableB B
ON b.key = a.key
INNER JOIN Table C
ON C.key = b.CKey
WHERE A.id = AnInput

Or

SELECT *
INTO TempTableC
FROM TableC
WHERE id = AnInput

-- TempTableC now has 1000 records Then

SELECT Fields
FROM TableA A
INNER JOIN TableB B --Maybe put this into a filtered temp table?
ON b.key = a.key
INNER JOIN TempTableC  c
ON c.AField = b.aField
WHERE a.id = AnInput

Basically, bring the result sets into temp tables, then join.

Upvotes: 1

Views: 147

Answers (1)

Mike Chamberlain
Mike Chamberlain

Reputation: 42480

If your Person table is indexed correctly, then the INNER JOIN should not be causing such a problem. Check that you have an index created on column(s) that are joined to in all your tables. Using temp tables for what appears to be a relatively simple query seems to be papering over the cracks of an inadequate database design.

As others have said, the only way to be sure is to post your query plan.

Upvotes: 3

Related Questions