SQL查询执行缓慢（对于某些参数值） [英] SQL query executing slowly (for some parameter values)

问题描述

我有一个SQL Server 2005数据库与几个表。其中一个表用于存储多个设备的时间戳和消息计数器，并且具有以下列：

I have a SQL Server 2005 database with several tables. One of the tables is used to store timestamps and message counters for several devices, and has the following columns:

CREATE TABLE [dbo].[Timestamps] (
[Id] [uniqueidentifier] NOT NULL,
[MessageCounter] [bigint] NULL,
[TimeReceived] [bigint] NULL,
[DeviceTime] [bigint] NULL,
[DeviceId] [int] NULL
)

Id 是唯一的主键（Guid.Comb），我有 DeviceId 和 MessageCounter 列。

Id is the unique primary key (Guid.Comb), and I have indexes on both DeviceId and MessageCounter columns.

我想要做的是找到最后插入的行

What I want to do is find the last inserted row (the row with the largest MessageCounter) for a certain device.

奇怪的是，查询设备号。 4 （和除1之外的所有其他设备）几乎瞬间返回：

The thing that is strange is that a query for device no. 4 (and all other devices except no.1) returns almost instantaneously:

select top 1 * 
   from "Timestamps"
   where DeviceId = 4
   order by MessageCounter desc

但对设备号。 1 永久完成：

select top 1 * 
   from "Timestamps"
   where DeviceId = 1 /* this is the only line changed */
   order by MessageCounter desc

最奇怪的是设备1的行数比设备4少：

The strangest thing is that device 1 has much less rows than device 4:

select count(*) from "Timestamps" where DeviceId = 4
(returns 1,839,210)

select count(*) from "Timestamps" where DeviceId = 1
(returns 323,276).

有没有人知道我可能做错了什么？

Does anyone have a clue what I could be doing wrong?

从这两个查询的执行计划中可以清楚地看到设备1在索引扫描中创建大量的行：

From the execution plans for both queries, it is clearly visible that Device 1 (lower diagram) creates a much larger number of rows in Index scan:

设备4（上）和设备1（下）的执行计划http://img295.imageshack.us/img295/5784/execplans.png

区别在于当我在执行计划图上悬停索引扫描节点时：

The difference is when I hover the Index Scan nodes on execution plan diagrams:

Device 4 Actual Number of Rows: 1

Device 1 Actual Number of Rows: approx. 6,500,000

6,500,000行是一个非常奇怪的数字，因为我的 ）查询会为设备1返回约30万行！

6,500,000 rows is a very strange number, since my select count(*) query returns around 300,000 rows for device 1!

推荐答案

$ c>（DeviceId，MessageCounter DESC）。

Try creating an index on (DeviceId, MessageCounter DESC).

此外，请尝试此查询：

select * 
   from "Timestamps"
   where DeviceId = 1
   and MessageCounter = (SELECT MAX(MessageCounter) FROM "Timestamps" WHERE DeviceID = 1)

只是猜测：性能差异可能是因为 DeviceId = 1 分布在多于 DeviceId = 4 的页面上。通过排序，我怀疑您正在清除所有匹配的页面，即使您最终只选择顶行。

Just guessing: The performance difference might be because DeviceId = 1 is spread across more pages than DeviceId = 4. By sorting, I suspect you are dredging up all matching pages, even if you end up selecting only the top row.

这篇关于SQL查询执行缓慢（对于某些参数值）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！