Looking to optimize performance of this SQL Server statement

2023-03-20 01:51 问答作者：

I know it's a tall order to ask to make something fast out of this SQL statement, but I'd appreciate if someone could take a look and maybe suggested how can I optimize it?

The following query is generated by an ASP NET script (so I'm not just hardcoding values in it). Although I did "unwrap" the first SELECT statement for readability:

WITH 
valDiff0 AS (SELECT datediff(second,
  CASE  
   WHEN '2011-06-06 00:00:00' < [inTime] THEN [inTime]  
   ELSE '2011-06-06 00:00:00' 
  END, 
  CASE  
   WHEN isdate([outTime]) = 1 THEN   
     CASE    
      WHEN '2011-06-06 23:59:59' > [outTime] THEN [outTime]
      ELSE '2011-06-06 23:59:59'   
     END  
   ELSE
     CASE    
      WHEN '2011-06-06 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    
      ELSE '2011-06-06 23:59:59'   
     END 
   END)  
AS v1 FROM [dbo].[Info] WHERE   
   [cName] LIKE 'T%' 
   AND [b] <> 0 
   AND CONVERT(INT, [evt]) & 7 = 4 
   AND isdate([inTime]) = 1 
   AND  [inTime] <= '2011-06-06 23:59:59' 
   AND  [outTime] >= '2011-06-06 00:00:00'),

valDiff1 AS (
  SELECT datediff(second, CASE  WHEN '2011-06-07 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-07 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-07 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-07 23:59:59'   END  else   case    WHEN '2011-06-07 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-07 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-07 23:59:59' AND  [outTime] >= '2011-06-07 00:00:00'), 
valDiff2 AS (SELECT datediff(second,CASE  WHEN '2011-06-08 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-08 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-08 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-08 23:59:59'   END  else   case    WHEN '2011-06-08 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-08 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-08 23:59:59' AND  [outTime] >= '2011-06-08 00:00:00'), 
valDiff3 AS (SELECT datediff(second,CASE  WHEN '2011-06-09 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-09 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-09 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-09 23:59:59'   END  else   case    WHEN '2011-06-09 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-09 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-09 23:59:59' AND  [outTime] >= '2011-06-09 00:00:00'), 
valDiff4 AS (SELECT datediff(second,CASE  WHEN '2011-06-10 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-10 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-10 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-10 23:59:59'   END  else   case    WHEN '2011-06-10 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-10 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-10 23:59:59' AND  [outTime] >= '2011-06-10 00:00:00'), 
valDiff5 AS (SELECT datediff(second,CASE  WHEN '2011-06-11 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-11 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-11 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-11 23:59:59'   END  else   case    WHEN '2011-06-11 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-11 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-11 23:59:59' AND  [outTime] >= '2011-06-11 00:00:00'), 
valDiff6 AS (SELECT datediff(second,CASE  WHEN '2011-06-12 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-12 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-12 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-12 23:59:59'   END  else   case    WHEN '2011-06-12 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-12 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-12 23:59:59' AND  [outTime] >= '2011-06-12 00:00:00'), 
valDiff7 AS (SELECT datediff(second,CASE  WHEN '2011-06-13 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-13 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-13 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-13 23:59:59'   END  else   case    WHEN '2011-06-13 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-13 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-13 23:59:59' AND  [outTime] >= '2011-06-13 00:00:00'), 
valDiff8 AS (SELECT datediff(second,CASE  WHEN '2011-06-14 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-14 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-14 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-14 23:59:59'   END  else   case    WHEN '2011-06-14 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-14 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-14 23:59:59' AND  [outTime] >= '2011-06-14 00:00:00'), 
valDiff9 AS (SELECT datediff(second,CASE  WHEN '2011-06-15 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-15 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-15 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-15 23:59:59'   END  else   case    WHEN '2011-06-15 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-15 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-15 23:59:59' AND  [outTime] >= '2011-06-15 00:00:00'), 
valDiff10 AS (SELECT datediff(second,CASE  WHEN '2011-06-16 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-16 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-16 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-16 23:59:59'   END  else   case    WHEN '2011-06-16 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-16 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-16 23:59:59' AND  [outTime] >= '2011-06-16 00:00:00'), 
valDiff11 AS (SELECT datediff(second,CASE  WHEN '2011-06-17 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-17 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-17 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-17 23:59:59'   END  else   case    WHEN '2011-06-17 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-17 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-17 23:59:59' AND  [outTime] >= '2011-06-17 00:00:00'), 
valDiff12 AS (SELECT datediff(second,CASE  WHEN '2011-06-18 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-18 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-18 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-18 23:59:59'   END  else   case    WHEN '2011-06-18 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-18 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-18 23:59:59' AND  [outTime] >= '2011-06-18 00:00:00'), 
valDiff13 AS (SELECT datediff(second,CASE  WHEN '2011-06-19 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-19 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-19 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-19 23:59:59'   END  else   case    WHEN '2011-06-19 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-19 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-19 23:59:59' AND  [outTime] >= '2011-06-19 00:00:00'), 
valDiff14 AS (SELECT datediff(second,CASE  WHEN '2011-06-20 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-20 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-20 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-20 23:59:59'   END  else   case    WHEN '2011-06-20 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-20 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-20 23:59:59' AND  [outTime] >= '2011-06-20 00:00:00'), 
valDiff15 AS (SELECT datediff(second,CASE  WHEN '2011-06-21 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-21 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-21 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-21 23:59:59'   END  else   case    WHEN '2011-06-21 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-21 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-21 23:59:59' AND  [outTime] >= '2011-06-21 00:00:00'), 
valDiff16 AS (SELECT datediff(second,CASE  WHEN '2011-06-22 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-22 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-22 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-22 23:59:59'   END  else   case    WHEN '2011-06-22 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-22 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-22 23:59:59' AND  [outTime] >= '2011-06-22 00:00:00'), 
valDiff17 AS (SELECT datediff(second,CASE  WHEN '2011-06-23 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-23 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-23 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-23 23:59:59'   END  else   case    WHEN '2011-06-23 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-23 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-23 23:59:59' AND  [outTime] >= '2011-06-23 00:00:00'), 
valDiff18 AS (SELECT datediff(second,CASE  WHEN '2011-06-24 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-24 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-24 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-24 23:59:59'   END  else   case    WHEN '2011-06-24 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-24 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-24 23:59:59' AND  [outTime] >= '2011-06-24 00:00:00'), 
valDiff19 AS (SELECT datediff(second,CASE  WHEN '2011-06-25 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-25 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-25 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-25 23:59:59'   END  else   case    WHEN '2011-06-25 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-25 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-25 23:59:59' AND  [outTime] >= '2011-06-25 00:00:00'), 
valDiff20 AS (SELECT datediff(second,CASE  WHEN '2011-06-26 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-26 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-26 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-26 23:59:59'   END  else   case    WHEN '2011-06-26 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-26 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-26 23:59:59' AND  [outTime] >= '2011-06-26 00:00:00'), 
valDiff21 AS (SELECT datediff(second,CASE  WHEN '2011-06-27 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-27 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-27 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-27 23:59:59'   END  else   case    WHEN '2011-06-27 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-27 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-27 23:59:59' AND  [outTime] >= '2011-06-27 00:00:00'), 
valDiff22 AS (SELECT datediff(second,CASE  WHEN '2011-06-28 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-28 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-28 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-28 23:59:59'   END  else   case    WHEN '2011-06-28 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-28 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-28 23:59:59' AND  [outTime] >= '2011-06-28 00:00:00'), 
valDiff23 AS (SELECT datediff(second,CASE  WHEN '2011-06-29 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-29 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-29 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-29 23:59:59'   END  else   case    WHEN '2011-06-29 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-29 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-29 23:59:59' AND  [outTime] >= '2011-06-29 00:00:00'), 
valDiff24 AS (SELECT datediff(second,CASE  WHEN '2011-06-30 00:00:00' < [inTime] THEN [inTime]  ELSE '2011-06-30 00:00:00' END, CASE  WHEN isdate([outTime]) = 1 THEN   CASE    WHEN '2011-06-30 23:59:59' > [outTime] THEN [outTime]    ELSE '2011-06-30 23:59:59'   END  else   case    WHEN '2011-06-30 23:59:59' > [inTime] + 604800 THEN [inTime] + 604800    ELSE '2011-06-30 23:59:59'   END END)  AS v1 FROM [dbo].[Info] WHERE   [cName] LIKE 'T%' AND [b] <> 0 AND CONVERT(INT, [evt]) & 7 = 4 AND isdate([inTime]) = 1 AND  [inTime] <= '2011-06-30 23:59:59' AND  [outTime] >= '2011-06-30 00:00:00') 
SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff0
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff1
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff2
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff3
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff4
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff5
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff6
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff7
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff8
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff9
 UNION ALL
 SELECT SUM(开发者_如何学JAVACASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff10
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff11
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff12
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff13
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff14
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff15
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff16
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff17
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff18
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff19
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff20
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff21
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff22
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff23
 UNION ALL
 SELECT SUM(CASE WHEN v1 < 604800 THEN v1 ELSE 604800 END) FROM valDiff24

How can one optimize something like that?

First, I'd try and shorten it just a little, by creating a Calendar table. This may also Partially improve performance...

WITH
  Calendar AS
(
  SELECT 0 AS [id], CAST('2011 Jun 06' AS DATETIME) AS [DateStart], CAST('2011 Jun 06' AS DATETIME) + 1 AS [DateEnd]
  UNION ALL
  SELECT id + 1, [DateStart] + 1, [DateStart] + 2 FROM Calendar WHERE id < 24
)
,
  FilteredData AS
(
  SELECT
    COALESCE(Info.[inTime],  CAST('2011 Jun 06')) AS [inTime],
    COALESCE(Info.[outTime], Info.[inTime] + 7)   AS [outTime]
  FROM
    [dbo].[Info]
  WHERE
         Info.[cName] LIKE 'T%'
     AND Info.[b] <> 0
     AND CONVERT(INT, Info.[evt]) & 7 = 4
     AND (Info.[outTime] IS NULL OR Info.[outTime] >= CAST('2011 Jun 06' AS DATETIME)
     AND (Info.[inTime]  IS NULL OR Info.[inTime]  <  CAST('2011 Jun 06' AS DATETIME) + 25)
)
,
  CleanedData AS
(
  SELECT
    [Calendar].id,
    CASE  
      WHEN Calendar.[DateStart] < Info.[inTime]  THEN Info.[inTime]  
      ELSE Calendar.[DateStart] 
    END AS [inTime], 
    CASE    
      WHEN Calendar.[DateEnd]   > Info.[outTime] THEN Info.[outTime]
      ELSE Calendar.[DateEnd]   
    END AS [outTime]
 FROM
    [Calendar]
  INNER JOIN
    [FilteredData] AS [Info]
      ON  Info.[inTime]  <  Calendar.[DateEnd]
      AND Info.[outTime] >= Calendar.[DateStart]
)

SELECT
  [id],
  SUM(DATEDIFF(SECOND, [InTime], [OutTime])) AS duration
FROM
  [CleanedData]
GROUP BY
  [id]

-- Note: The CleanedData step causes the InTime and OutTime to be no more than a day appart.
-- This means that there is no need to check the DATEDIFF is less than 1 week long.

In terms of optimisation, what Indexes do you have on the table? They can Certainly help here...

  WHERE
     Info.[cName] LIKE 'T%'
     AND Info.[b] <> 0

Finally, and index can't help with CONVERT(INT, Info.[evt]) & 7 = 4. Are you able to add a calculated field to the table, which you can index? Or change the single field to multiple fields; a set of flags for different modes/states? (Or make a derived table of this information?) Or would indexing this field not make much difference to you?

You have logic that says "if outTime IS NULL, use inTime plus 1 week". What do you want to do if both inTime and OutTime are NULL?

There is also an issue regarding optimisation for filtering the data based on the dates you are interested in. Where an event can be any duration, you have to check both the in and out times, which kills your ability to make use of an index. If you know that an event has a maximum length, however, you can do something like this...

WHERE
      Info.inTime  > (myEarliestDate) - (myLongestDuration)
  AND Info.inTime  < (myLatestDate)
  AND Into.OutTime > (myEarliestDate)

What that gives you is both > AND < wrapped around your inTime field, explicitly limitting everything to a very specific range of data. As such, I'd add this to the end of the WHERE clause called FilteredData. (On the assumption that no event ever lasts more than 7 days.)

     AND (Info.[inTime]  IS NULL OR Info.[inTime]  >= CAST('2011 Jun 06' AS DATETIME) - 7)

继续阅读：performance select sql sql-server

Looking to optimize performance of this SQL Server statement

更多精彩内容

精彩评论

最新问答

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

哪里医院专治输卵管堵塞好？

外语基础薄弱的人出国自由行，带哪种翻译器比较好？？

输卵管积液手术价格？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？