SQL Server Express上的查询执行时间太长

I have two tables Channel and ChannelData. Channel Table has 40 records, every minute a record will be inserted in to ChannelData table for each Channel in the Channel Table. Now ChannelData table has more than 15 million records in it. I wrote a query as following to get the latest logged values for the 40 Channels in ChannelData Table.

我有两个表Channel和ChannelData。频道表有40条记录,每分钟将一条记录插入频道表中每个频道的ChannelData表中。现在ChannelData表中有超过1500万条记录。我写了一个查询如下,以获取ChannelData表中40个通道的最新记录值。

select
    max(chnldata.Id) as ChannelDataId,
    chnl.id as ChannelId,
    chnl.ChannelName as ChannelName,
    chnldata.ChannelValue as channelValue,
    chnl.ChannelMonitoringUnits as ChannelUnits, 
    chnldata.ChannelDataLogTime as channelDataLogTime,
    chnl.StationId,
    chnldata.Active
from
    ChannelData as chnldata                        
    inner join Channel as chnl on chnl.Id = chnldata.ChannelId 
where
    chnl.Active = 1
    and
    chnldata.ChannelDataLogTime in 
        (SELECT
            MAX(chnldata1.ChannelDataLogTime)
         FROM
            ChannelData as chnldata1
         where 
            chnldata1.ChannelId = chnl.Id)                        
group by
    chnldata.Id,
    chnl.id,
    ChannelName,
    ChannelValue,
    chnl.ChannelMonitoringUnits,
    ChannelDataLogTime,
    chnl.StationId,
    chnldata.Active

When I am executing this query on SQLServer 2008 Express Edition it is taking 29 minutes to get the results, but when i tried to run the same query on SQLServer 2008 Standard Edition it took less than 1 min on a database with ChannelData table of 15 million records. Is there any other way to write this query, so that I could get the results in less than a minute in SQL Server 2008 Express.

当我在SQLServer 2008 Express Edition上执行此查询时,需要29分钟才能获得结果,但是当我尝试在SQLServer 2008 Standard Edition上运行相同的查询时,在ChannelData表为1500万的数据库上花费不到1分钟记录。有没有其他方法来编写此查询,以便我可以在不到一分钟的时间内在SQL Server 2008 Express中获得结果。

1 个解决方案

#1

Your other server probably has more indexes defined. Indexes aren't included in Generate Scripts by default for some reason, so you might want to look at that.

您的其他服务器可能定义了更多索引。出于某种原因,默认情况下,索引不包含在“生成脚本”中,因此您可能希望查看该索引。

Use the SQL Server Profiler tool (you'll have this with your Standard SKU installer, but you can use it against SQL Server Express). Also look at the Actual Execution Plan (not the Estimated Execution Plan) command, look for Table Scan or Index Scan - that will reveal inefficiencies that can be fixed with an appropriate index.

使用SQL Server Profiler工具(您将使用标准SKU安装程序,但您可以将其用于SQL Server Express)。另请查看实际执行计划(而不是估计执行计划)命令,查找表扫描或索引扫描 - 这将揭示可以使用适当的索引修复的低效率。

See here http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

见http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/

#1