sql语句执行效率低 求大神优化

[chenyijunshuai]

语句如下：
with rownum as
(
select r=ROW_NUMBER() over (order by a.fromuser,a.timestamp),*
from (select * from GL_G18_MsgSignalRecord where instype in (0,1) and fromtype=4 ) a
)
,totable as
(
select tot.region as colname, tot.robot as rowname,SUM(tot.onlinetime) as pvalue
from
(select Convert(varchar(4), datepart(YEAR,b.timestamp))+'-'+ REPLICATE('0',2-LEN(Convert(varchar(2), datepart(month,b.timestamp))))+ Convert(varchar(2), datepart(month,b.timestamp)) as month,
e.RegionName as region,
d.RobotCode as robot,
(case when c.instype=0 and b.instype=1 then DATEDIFF(S,c.timestamp,b.timestamp) else 0 end) as onlinetime
from rownum c,rownum b,DG_Robot d,DG_Region e where c.r=b.r-1 and b.fromuser=c.fromuser and b.fromuser=d.RobotCode and d.RegionCode=e.RegionCode
) tot group by region,robot
)
select top 5 colname from totable group by colname order by sum(pvalue) desc

其中主表 GL_G18_MsgSignalRecord中有12000左右的数据，执行速度为24s
主要实现功能：
取instype=0及下一条instype=1之间的时间间隔、按照月份（month）、区域（region）、机器人（robot）进行列选项搜索 ；
其中主表关联了区域、机器人两张表

求大神指点 如何优化此语句 谢谢

[chenyijunshuai]

引用 1 楼 hery2002 的回复:

try

;WITH ROWNUM AS 
 ( 
   SELECT 
            R=ROW_NUMBER() OVER (ORDER BY A.FROMUSER,A.[TIMESTAMP])
            ,A.FROMUSER
            ,A.TIMESTAMP 
   FROM GL_G18_MSGSIGNALRECORD A
 WHERE INSTYPE IN (0,1) 
    AND FROMTYPE=4
 ) 
 ,TOTABLE AS 
 (
     SELECT 
      E.REGIONNAME AS COLNAME,
     SUM((CASE WHEN C.INSTYPE=0 AND B.INSTYPE=1 THEN DATEDIFF(S,C.[TIMESTAMP],B.[TIMESTAMP]) ELSE 0 END)) AS PVALUE 
      FROM  ROWNUM C
   INNER JOIN ROWNUM B  ON C.R=B.R-1   AND B.FROMUSER=C.FROMUSER 
   INNER JOIN DG_ROBOT D ON  B.FROMUSER=D.ROBOTCODE
   INNER JOIN DG_REGION E  ON D.REGIONCODE=E.REGIONCODE 
    GROUP BY REGION,ROBOT
  )
SELECT TOP 5 COLNAME 
    FROM TOTABLE 
GROUP BY COLNAME 
ORDER BY SUM(PVALUE) DESC

问题好像出在对GROUP BY REGION,ROBOT的分组上 如果只对REGION分组，也就是对列分组，不取行数据就可以查询出来 分组中包括ROBOT的分组之后 就挂了 不知道我说的对不对 望大神分析一下 其中具体原因

[Tiger_Zhao]

WITH rownum AS (
    SELECT r = Row_number() OVER(PARTITION BY fromuser -- 看下面 b、c 的连接条件，formuser 用错了
                                 ORDER BY [timestamp]),
           fromuser, [timestamp], instype -- 只列需要的字段
      FROM gl_g18_msgsignalrecord
     WHERE instype IN (0,1)
       AND fromtype = 4
    /* 有日期条件先在这里过滤 */
       AND [timestamp] >= '2015-05-01'
       AND [timestamp] < '2015-06-01'
    
)
,totable AS (
        SELECT e.regionname AS colname,
               SUM(Datediff(s,c.TIMESTAMP,b.TIMESTAMP)) AS pvalue 
         FROM rownum c
         JOIN rownum b
           ON b.fromuser = c.fromuser
          AND c.r = b.r - 1
         JOIN dg_robot d
           ON b.fromuser = d.robotcode
         JOIN dg_region e
           ON d.regioncode = e.regioncode
        WHERE c.instype = 0 -- 这两个作为过滤条件，而不是所有记录都参与统计
          AND b.instype = 1
       /* 如果有 robot 的过滤条件写在这里，不要出现在 SELECT 字段中
          AND e.regioncode = 12345
       */
     GROUP BY e.regionname -- 最终结果只有一个分组，就不要画蛇添足了
)
  SELECT TOP 5 colname
    FROM totable
ORDER BY pvalue DESC

提高速度最关键的是索引
gl_g18_msgsignalrecord (fromuser, [timestamp], instype)，如果没有日期条件改为 (fromuser, instype)包含([timestamp])
dg_robot(robotcode)包含(regioncode)
dg_region(regioncode)包含(regionname)

[hery2002]

try

;WITH ROWNUM AS 
 ( 
   SELECT 
            R=ROW_NUMBER() OVER (ORDER BY A.FROMUSER,A.[TIMESTAMP])
            ,A.FROMUSER
            ,A.TIMESTAMP 
   FROM GL_G18_MSGSIGNALRECORD A
 WHERE INSTYPE IN (0,1) 
    AND FROMTYPE=4
 ) 
 ,TOTABLE AS 
 (
     SELECT 
      E.REGIONNAME AS COLNAME,
     SUM((CASE WHEN C.INSTYPE=0 AND B.INSTYPE=1 THEN DATEDIFF(S,C.[TIMESTAMP],B.[TIMESTAMP]) ELSE 0 END)) AS PVALUE 
      FROM  ROWNUM C
   INNER JOIN ROWNUM B  ON C.R=B.R-1   AND B.FROMUSER=C.FROMUSER 
   INNER JOIN DG_ROBOT D ON  B.FROMUSER=D.ROBOTCODE
   INNER JOIN DG_REGION E  ON D.REGIONCODE=E.REGIONCODE 
    GROUP BY REGION,ROBOT
  )
SELECT TOP 5 COLNAME 
    FROM TOTABLE 
GROUP BY COLNAME 
ORDER BY SUM(PVALUE) DESC