CODEWITHSUNDEEP
sqlsql-serverperformancequery-optimization
Soham Shah
Soham Shah

Reputation: 571

Optimizing SQl Query with bunch of Unions and Conditions

I have written a query to get Production Quantity for either 1st shift, 2nd shift, 3rd shift or all shifts - based on the passed parameters.

Shift timings are following:

1st: 06:00 - 14:00
2nd: 14:00 - 22:00
3rd: 22:00 - 06:00

My query looks like following:

// Param.1 = date start , Param.2 = date end , Param.3 = shift start time , Param.4 = shift end time
// Param.5 = 1 when ShiftStarttime < ShiftEndtime ( Param.1 = 2015-12-26T06:00:00,  param.2 = 2015-12-26T14:00:00, param.3 = 6 , param.4 = 10 )
// Param.5 = 2 when ShiftStarttime > ShiftEndtime ( Param.1 = 2015-12-26T22:00:00,  param.2 = 2015-12-27T06:00:00, param.3 = 22 , param.4 = 2 )
// Param.5 = 3 when all of the shifts ( Param.1 = 2015-12-26T06:00:00,  param.2 = 2015-12-27T06:00:00)

SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID

WHERE

      '[Param.5]' = '1'
       AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND ((DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]'))

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

UNION


SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID


WHERE

      '[Param.5]' = '2'
       AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND (DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR  DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5)

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

UNION


SELECT 

      B.Machine_NAME,
      C.Part_Name,     
      SUM(A.QT_CONFIRMED) QT_CONFIRMED,
      D.SCRAP_REASON

 FROM 
       CONFIRMATION A 

       INNER JOIN MACHINE B ON A.Machine_ID =B.Machine_ID  
       INNER JOIN PART C ON B.Part_ID = C.Part_ID
       LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID =D.SCRAP_REASON_ID


WHERE

      '[Param.5]' = '3'
    AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') 

GROUP BY

      B.Machine_NAME,
      C.Part_Name,
      D.SCRAP_REASON

So basically depending upon the shift start time and shift end time, only on eof the union of the query will be executed due to Param.5 parameter. Please have a look at COMMENT in the query to understand parameters.

This query runs fine and gives proper result but I'm sure that it can be optimized to run faster if somehow I can remove Union or can modify some part of it.

Does anyone has better idea to write this query in more optimal way? If yes, kindly do share.

Upvotes: 1

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The queries all have the same structure except for the WHERE clauses. Hence, you can combine the conditions using OR:

 SELECT B.Machine_NAME,  C.Part_Name, SUM(A.QT_CONFIRMED) as QT_CONFIRMED,
        D.SCRAP_REASON
 FROM CONFIRMATION A INNER JOIN
      MACHINE B
      ON A.Machine_ID = B.Machine_ID INNER JOIN
      PART C
      ON B.Part_ID = C.Part_ID LEFT JOIN
      SCRAP_REASON D
      ON A.SCRAP_REASON_ID = D.SCRAP_REASON_ID
WHERE ('[Param.5]' = '1' AND
       (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND 
       ((DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND 
        (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]'))
      ) OR
      ('[Param.5]' = '2' AND
       (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND 
       (DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR
        DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5)
      ) OR
      ('[Param.5]' = '3' AND (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]')
      );

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

The two queries appear to be identical except for the WHERE clauses, which are different. Try connecting the two WHERE clauses into a single query using OR. Note that I added DISTINCT to the SELECT statement to make sure that duplicates still get removed (which the UNION operator was doing in your original query).

SELECT DISTINCT
    B.Machine_NAME,
    C.Part_Name,     
    SUM(A.QT_CONFIRMED) QT_CONFIRMED,
    D.SCRAP_REASON
FROM 
    CONFIRMATION A
INNER JOIN MACHINE B ON A.Machine_ID = B.Machine_ID  
INNER JOIN PART C ON B.Part_ID = C.Part_ID
LEFT JOIN SCRAP_REASON D ON A.SCRAP_REASON_ID = D.SCRAP_REASON_ID
WHERE
    (
        '[Param.5]' = '1' AND
        (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
        (
            (DATEPART(hour, DT_CONFIRMATION) >= '[Param.3]') AND
            (DATEPART(hour, DT_CONFIRMATION) < '[Param.4]')
        )
    )
    OR
    (
         '[Param.5]' = '2' AND
         (A.DT_CONFIRMATION BETWEEN '[Param.1]' AND '[Param.2]') AND
         (
             DATEPART(hour, DT_CONFIRMATION) BETWEEN 22 AND 23 OR
             DATEPART(hour, DT_CONFIRMATION) BETWEEN 0 and 5
         )
    )
GROUP BY
    B.Machine_NAME,
    C.Part_Name,
    D.SCRAP_REASON

Upvotes: 1

Related Questions