我应该使用临时表吗？

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

我有一个报告查询需要4分钟,并且在我们应用的最大30秒允许限制之下。

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:

我注意到它有很多INNER JOINS。我知道,其中一个是连接到一个包含数百万行的Person表。我想知道打破查询是否更有效率。做以下事情会更有效率:

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

假设所有键都已编入索引。表C有800万条记录,表B有600万条记录,表A有400,000条记录。

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

- TempTableC现在有1000条记录

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.

基本上,将结果集引入临时表,然后加入。

1 个解决方案

#1

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.

如果您的Person表被正确编入索引,那么INNER JOIN不应该导致这样的问题。检查是否在所有表中已加入的列上创建了索引。使用临时表来看似乎是一个相对简单的查询似乎是在解释数据库设计不足的问题。

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

正如其他人所说,唯一可以确定的方法是发布您的查询计划。