Replace sql server subquery to make query faster instead of crawling

Question

I want to speed this query up, I was trying various joins, but I can't seem to not end up with duplicate query for storm2 = 1 and storm2 = 2 with a join

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

At least show us the execution plan http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Juan Carlos Oropeza, Nov 04 '15 at 21:23

score 0 · Answer 1 · answered Nov 04 '15 at 21:28

You need join the table with itself. This will reduce the number of inner select. You still should check have the proper index looking the execution plan

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

Also You have duplicated fields

,v1.StormOut
,v1.StormNo
,v1.StormYes

And

,v1.StormOut AS 'Out'
,v1.StormYes AS Yes
,v1.StormNo AS No

score 0 · Accepted Answer · answered Nov 04 '15 at 21:39

If I remember right, that style of subquery gets translated to an OUTER JOIN, but you should check your execution plan to be sure. Assuming that's the case:

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

Also, this is bad: DATEADD(dd, DATEDIFF(dd, 0, v1.[CurrentDate]), 0) = @Date. You're telling the database to load every single datetime value and perform this function on that, and then compare it to a fixed value. You want to avoid that. You want to run your functions against static values. Here, @Date is clearly a datetime value with 00:00 as the time component. So, in this case, you can do it like this:

v1.[CurrentDate] >= @Date
AND v1.[CurrentDate] <  DATEADD(dd,@Date,1)

Which means "CurrentDate is on or after @Date and before midnight of the day after @Date".

Replace sql server subquery to make query faster instead of crawling

2 Answers2