开发者

Looking to optimize performance of this SQL Server statement

开发者 https://www.devze.com 2023-03-20 01:02 出处：网络
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?
相关专题：performanceselectsqlsql-server

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)
0

上一篇:

:下一篇

更多 问答 相关资讯：

精彩评论

暂无评论...
登录 注册
Qedev.com
验证码 换一张
取 消

关注公众号

热门标签

图文推荐

Delphi - Custom drawing a message list

Delphi - Custom drawing a message list
C++ header-only include pattern

C++ header-only include pattern
IE7 Margin Collapses Into Padding

IE7 Margin Collapses Into Padding
in CoffeeScript, how can I use a variable as a key in a hash?

in CoffeeScript, how can I use a variable as a key in a hash?
Interactive visualization of a graph in python [closed]

Interactive visualization of a graph in python [closed]
How to customise PHP MYSQL tables?

How to customise PHP MYSQL tables?
High quality, simple random password generator

High quality, simple random password generator
Image Recognition ApI in android

Image Recognition ApI in android

开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9