改进SQL查询计数性能的建议?

I've got a query that is taking a very long time to run due to a select similar to:

我有一个查询需要很长时间才能运行，因为有一个类似于:

SELECT 
Count( Distinct t1.v1),
Count (Distinct 
Case t2.v1 When 'blah' then t1.v1
Else null
End ),
....
FROM t1
LEFT JOIN t2 ON t1.v3 = t2.v3
WHERE t1.myDate BETWEEN @start and @end
AND t2.v5 = @id

Can anyone suggest any good methods for improving this performance?

有谁能提出改进性能的好方法吗?

4 个解决方案

#1

As there are No Where clause predicates (no filters) on this query it will involve a complete table scan or index scan no matter what you do (unless the inner join restricts the resultset...).

由于在这个查询中没有Where子句谓词(没有过滤器)，因此无论您做什么，它都将涉及一个完整的表扫描或索引扫描(除非内部连接限制resultset…)。

So the only improvement that you can get is possibly to affect what type of join is being done. To improve the performance of the join, make sure there is an index on the t1.v3 and t2.v3 columns....

因此，您能得到的唯一改进可能是影响正在执行的连接类型。要提高连接的性能，请确保在t1上有索引。v3和t2。v3列....

#2

Difficult to say without the big picture, what you're trying to achieve etc.

如果没有大局观、你想要达到的目标等等，很难说清楚。

But the immediate thing to check based on that is indexes - do you have suitable indexes (e.g. on t1.v3 / t2.v3)? If you do have appropriate indexes, are they indexes fragmented/statistics out of date?

但是最直接的检查就是索引——你是否有合适的索引(比如t1)。v3 / t2.v3)?如果您确实有适当的索引，那么它们是碎片化/过时的索引吗?

What does the execution plan say?

执行计划说什么?

#3

Your LEFT JOIN on t2 with a filter on t2.v5 = @id changes this to an inner join. You'll need ...LEFT JOIN t2 ON t1.v3 = t2.v3 AND t2.v5 = @id...

你的左连接在t2上，在t2上有一个过滤器。v5 = @id将其更改为内部连接。你需要…在t1上左连接t2。v3 = t2。v3和t2。v5 = @ id…

Next, what indexes do you have? Based on what I see

接下来，有哪些索引?根据我所看到的。

t1: (mydate, v3) INCLUDE (v1)
t1:(mydate, v3) INCLUDE (v1)
t2: (v3, v5) INCLUDE (v1)
t2:(v3, v5) INCLUDE (v1)

You could try reversing the key cols too

你也可以试试倒车

Finally, ensure all data types are correct and match (even in the 2nd count). Implicit conversions kill performance.

最后，确保所有数据类型都是正确的和匹配的(即使是在第2次计数中)。隐式转换杀死性能。

#4

You are probably getting 1 scan on t1 and 1 seek on t2, if you are getting anything else, either the indexes are not appropriately in place, or you didn't give us enough information.

你可能在t1上得到1次扫描，在t2上得到1次搜索，如果你得到了其他信息，要么是索引不合适，要么是你没有给我们足够的信息。

If you are getting 1 scan on t1 and 1 seek on t2, and you think this query is close to the appropriate solution (e.g. you cannot get the information you want from a faster query that looks nothing like this one), you may have the optimal plan.

如果你在t1上得到1次扫描，在t2上得到1次搜索，并且你认为这个查询接近合适的解决方案(例如，你无法从一个看起来不像这个的更快的查询中得到你想要的信息)，那么你可能有一个最优的方案。

#1