替换SQL服务器子查询,使查询更快,而不是抓取
问题描述:
我想加快这个查询,我试着各种连接,但我似乎无法结束与storm2 = 1和storm2 = 2重复查询一个加入替换SQL服务器子查询,使查询更快,而不是抓取
SELECT v1.id
,v1.StormOut
,v1.StormNo
,v1.StormYes
,v1.Equipment
,(
SELECT TimeOn
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS TimeOn
,v1.Vehicle
,v1.NAME
,v1.Equipment AS Equip
,(
SELECT ScheduleOrig
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS ScheduleOrig
,(
SELECT ScheduleHTML
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS ScheduleHTML
,(
SELECT Schedule
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS Schedule
,(
SELECT TimeOff
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS TimeOff
,v1.StormTimeOn
,v1.StormTimeOff
,v1.StormOut AS 'Out'
,v1.StormYes AS Yes
,v1.StormNo AS No
,(
SELECT comments
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS comments
,v1.StormComments
,(
SELECT Comments
FROM [v_StormLog]
WHERE vehicle = v1.Vehicle
AND Storm2 = 1
AND CurrentDate = v1.CurrentDate
) AS CommentsHTML
,v1.Storm2
FROM [v_StormLog] v1
WHERE DATEADD(dd, DATEDIFF(dd, 0, v1.[CurrentDate]), 0) = @Date
AND v1.sgroup = 'North'
AND v1.Storm2 = @storm2
ORDER BY v1.ScheduleHTML
,v1.NAME
答
如果我记得没错,子查询的这种风格被转换到一个OUTER JOIN,但你应该检查你的执行计划以确保。假如是这样的话:
SELECT v1.id
,v1.StormOut
,v1.StormNo
,v1.StormYes
,v1.Equipment
,v2.TimeOn AS TimeOn
,v1.Vehicle
,v1.NAME
,v1.Equipment AS Equip
,v2.ScheduleOrig AS ScheduleOrig
,v2.ScheduleHTML AS ScheduleHTML
,v2.Schedule AS Schedule
,v2.TimeOff AS TimeOff
,v1.StormTimeOn
,v1.StormTimeOff
,v1.StormOut AS 'Out'
,v1.StormYes AS Yes
,v1.StormNo AS No
,v2.comments AS comments
,v1.StormComments
,v2.Comments AS CommentsHTML
,v1.Storm2
FROM [v_StormLog] v1
LEFT JOIN [v_StormLog] v2
ON v2.vehicle = v1.Vehicle
AND v2.CurrentDate = v1.CurrentDate
AND v2.Storm2 = 1
WHERE v1.[CurrentDate] >= @Date
AND v1.[CurrentDate] < DATEADD(dd,@Date,1)
AND v1.sgroup = 'North'
AND v1.Storm2 = @storm2
ORDER BY v1.ScheduleHTML
,v1.NAME
此外,这是不好的:DATEADD(dd, DATEDIFF(dd, 0, v1.[CurrentDate]), 0) = @Date
。您正在告诉数据库加载每个日期时间值并在其上执行此功能,然后将其与固定值进行比较。你想避免这种情况。你想要针对静态值运行你的函数。在这里,@Date
显然是以00:00作为时间分量的日期时间值。所以,在这种情况下,你可以做这样的:
v1.[CurrentDate] >= @Date
AND v1.[CurrentDate] < DATEADD(dd,@Date,1)
这意味着“的currentdate是或@date后@date后一天的午夜之前”。
答
你需要自己加入表格。这会减少内部选择的数量。你还应该检查有适当的索引中查找的执行计划
SELECT v1.*, v2.* -- update using the fields you need
FROM [v_StormLog] v1
JOIN [v_StormLog] v2
ON v2.vehicle = v1.Vehicle
AND v2.CurrentDate = v1.CurrentDate
WHERE
DATEADD(dd, DATEDIFF(dd, 0, v1.[CurrentDate]), 0) = @Date
AND v1.sgroup = 'North'
AND v1.Storm2 = @storm2
AND v2.Storm2 = 1
你也已经重复场
,v1.StormOut
,v1.StormNo
,v1.StormYes
而且
,v1.StormOut AS 'Out'
,v1.StormYes AS Yes
,v1.StormNo AS No
至少向我们展示执行计划http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan –