SELECT
COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) +
COUNT(DISTINCT T3.C1) FROM T1
LEFT JOIN T2 on T1.A1 = T2.A1
LEFT JOIN T3 on T1.A1 = T3.A1
LEFT JOIN T4 on T3.C1 = T4.C1 GROUP BY T1.A2, T1.A3
上面这个SQL效率太差了,
T1,T2,T3,T4表达到5千条的时候,需要两,三分钟
希望大家帮忙优化一下
多谢大家帮帮忙吧!
COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) +
COUNT(DISTINCT T3.C1) FROM T1
LEFT JOIN T2 on T1.A1 = T2.A1
LEFT JOIN T3 on T1.A1 = T3.A1
LEFT JOIN T4 on T3.C1 = T4.C1 GROUP BY T1.A2, T1.A3
上面这个SQL效率太差了,
T1,T2,T3,T4表达到5千条的时候,需要两,三分钟
希望大家帮忙优化一下
多谢大家帮帮忙吧!
可以在相关ON条件上建立索引。如果不熟悉的话。
VS2005有一个自动优化的工具。
楼主可以优化下。
(
SELECT
COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) as a
FROM T1
LEFT JOIN T2 on T1.A1 = T2.A1
GROUP BY T1.A2, T1.A3
)
+
(
SELECT
COUNT(DISTINCT T3.C1)
FROM T1
LEFT JOIN T3 on T1.A1 = T3.A1
GROUP BY T1.A2, T1.A3
)
--try:
SELECT
COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) +
COUNT(DISTINCT T3.C1) FROM T1
inner JOIN T2 on T1.A1 = T2.A1
inner JOIN T3 on T1.A1 = T3.A1
GROUP BY T1.A2, T1.A3