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屋!