大家来看看,关于 CROSS JOIN 优化的问题!
景色 2014-01-22 05:23:24 WITH TB AS
(
SELECT
A.Col1,
A.Col2,
DT
FROM A WITH(NOLOCK)
CROSS JOIN SELECT CONVERT(VARCHAR(10),DATEADD(DD,Number,'20140325'),112) AS DT FROM master..SPT_VALUES WHERE Type='P' AND DATEADD(DD,Number,'20140325')<='20140325') D
)
SELECT
TB.Col1,
TB.Col2,
B.Col3,
B.Col4,
C.Col5,
C.Col6,
FROM TB
LEFT JOIN B WITH(NOLOCK) ON B.Col1=TB.Col1 AND B.Col2=TB.Col2 AND B.DT=TB.DT
LEFT JOIN C WITH(NOLOCK) ON C.Col1=TB.Col1 AND C.Col2=TB.Col2 AND C.DT=TB.DT
WHERE TB.Col1 IN ('CHAR1','CHAR2','CHAR3','CHAR4','CHAR5')
group by TB.Col1, TB.Col2
order by TB.Col1, TB.Col2
上面的脚本的大意,如果不带 CROSS JOIN 执行很快,加了 CROSS JOIN 就非常慢!CPU 增加20%,大家有没有遇到过,有没有好的解决方案!