求教，SQL语句优化和索引优化。

[-Arvin]

表IntervalInfo如下：

表MonitoringsiteInfo：

表Gisdata数据有几百万条,Devicecode升序为聚集索引：

目前有触发器语句如下，执行耗时大概1500毫秒：

select tt.*,case WHEN AcquisitionTime is null or (DATEDIFF(mi,AcquisitionTime,GETDATE()) > 10) or (DATEDIFF(mi,AcquisitionTime,GETDATE()) > 10)	
 THEN '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/离线.png' ELSE '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/在线.png' END AS devicestatus from
(select b.Devicecode,b.MonitoringsiteName,(select MAX(c.AcquisitionTime) from Gisdata c where b.Devicecode=c.Devicecode and b.ChannelID=c.ChannelID) as AcquisitionTime
from MonitoringsiteInfo b JOIN IntervalInfo a on b.IntervalID = a.IntervalID where a.StationID=@StationID)tt order by Devicecode,AcquisitionTime

求优化方法。

[-Arvin]

引用 2 楼 DVD_01 的回复:

try:

select b.Devicecode ,b.MonitoringsiteName,
		 case 
			when AcquisitionTime is null
                       or ( datediff(mi, c.AcquisitionTime, getdate()) > 10 )
                       or ( datediff(mi, c.AcquisitionTime, getdate()) > 10 ) 
					then '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/离线.png'
               else '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/在线.png'
		end as devicestatus
	from MonitoringsiteInfo b
		join IntervalInfo a on b.IntervalID = a.IntervalID
		left join Gisdata c on  b.Devicecode = c.Devicecode and b.ChannelID = c.ChannelID
			and c.AcquisitionTime=(select max(x.AcquisitionTime) from Gisdata x where x.Devicecode=c.Devicecode and x.ChannelID=c.ChannelID)
	where a.StationID = @StationID

先检查执行计划，看最耗成本的位置在哪，是否能应用到已创建的索引，尽可能减少table/index scan操作，多index seek.

跟原来的语句执行时间是差不多的，还是谢谢老哥。

[0与1之间]

先看看执行计划哪里最耗时吧

[Tiger_Zhao]

在intervalinfo表用(stationid,intervalid)建索引
在monitoringsiteinfo表用(intervalid,devicecode,channelid)建索引
在gisdata表用(devicecode,channelid,acquisitiontime)建索引

        SELECT b.devicecode,
               b.monitoringsitename,
               ca.acquisitiontime,
               CASE WHEN acquisitiontime IS NULL 
                    OR (Datediff(mi,acquisitiontime,Getdate()) > 10)
                    /* 条件重复
                    OR (Datediff(mi,acquisitiontime,Getdate()) > 10)
                    */
               THEN '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/离线.png'
               ELSE '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/在线.png'
               END AS devicestatus
          FROM monitoringsiteinfo b
          JOIN intervalinfo a
            ON b.intervalid = a.intervalid
   OUTER APPLY (
                    SELECT TOP 1 acquisitiontime
                      FROM gisdata c
                     WHERE b.devicecode = c.devicecode
                       AND b.channelid = c.channelid
                  ORDER BY acquisitiontime DESC
              ) ca
       WHERE a.stationid = @StationID
    ORDER BY devicecode, acquisitiontime

[二月十六]

直接这样写行吗？

     SELECT  b.Devicecode ,
                b.MonitoringsiteName ,
                ( SELECT    CASE WHEN MAX(AcquisitionTime) IS NULL
                                      OR ( DATEDIFF(mi, AcquisitionTime,
                                                    GETDATE()) > 10 )
                                      OR ( DATEDIFF(mi, AcquisitionTime,
                                                    GETDATE()) > 10 )
                                 THEN '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/离线.png'
                                 ELSE '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/在线.png'
                            END AS devicestatus
                  FROM      Gisdata c
                  WHERE     b.Devicecode = c.Devicecode
                            AND b.ChannelID = c.ChannelID
                ) AS AcquisitionTime
        FROM    MonitoringsiteInfo b
                JOIN IntervalInfo a ON b.IntervalID = a.IntervalID
        WHERE   a.StationID = @StationID
        ORDER BY Devicecode ,
                AcquisitionTime

[Andy-W]

try:

select b.Devicecode ,b.MonitoringsiteName,
		 case 
			when AcquisitionTime is null
                       or ( datediff(mi, c.AcquisitionTime, getdate()) > 10 )
                       or ( datediff(mi, c.AcquisitionTime, getdate()) > 10 ) 
					then '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/离线.png'
               else '/Eonline;component/bin/Debug/Image/MonitoringsiteImages/在线.png'
		end as devicestatus
	from MonitoringsiteInfo b
		join IntervalInfo a on b.IntervalID = a.IntervalID
		left join Gisdata c on  b.Devicecode = c.Devicecode and b.ChannelID = c.ChannelID
			and c.AcquisitionTime=(select max(x.AcquisitionTime) from Gisdata x where x.Devicecode=c.Devicecode and x.ChannelID=c.ChannelID)
	where a.StationID = @StationID

先检查执行计划，看最耗成本的位置在哪，是否能应用到已创建的索引，尽可能减少table/index scan操作，多index seek.

[Tiger_Zhao]

你程序的错误就不要拿到数据库板块来问了。
自己跟踪错误。

[-Arvin]

还是出现了内存溢出的现象报错：引发类型为“System.OutOfMemoryException”的异常。

[Tiger_Zhao]

你的数据库用到了SQL CLR开发的函数/存储过程之类吧。
数据量再怎么大，只会减低速度，不会有异常。最多是tempdb空间不足的错误。

[-Arvin]

引用 7 楼 Tiger_Zhao 的回复:

你的数据库用到了SQL CLR开发的函数/存储过程之类吧。 数据量再怎么大，只会减低速度，不会有异常。最多是tempdb空间不足的错误。

没有用到你所说的函数，因为我们是监测软件，程序中有定时器，不断的执行这个存储过程。 之前我测试了自己的SQL语句与楼上提供的sql语句因为执行时间差不多所以没有改成上面哥们提供的。现在换了下再运行2天试试。

[-Arvin]

另外检查了代码中有在定时器的函数中不断声明Dictionary<string, string> 不知道会不会更这个也有关系，已取消了。