sql - 如何优化这个TSQL

Question

此查询大约需要01:30运行：

select DATEADD(dd, 0, DATEDIFF(dd, 0, t1.[OccurredOn]))
       , count(t2.UserId)
       , count(*) - count(t2.UserId)
from Events t1
left join (select c.UserId, min(c.OccurredOn) FirstOccurred
           from Events c
           where [OccurredOn] between @start and @end
           group by c.UserId) t2 on t1.OccurredOn = t2.FirstOccurred and t1.UserId = t2.UserId
where t1.EventType = @eventType
    and t1.[OccurredOn] between @start and @end
group by DATEADD(dd, 0, DATEDIFF(dd, 0, t1.[OccurredOn]))
order by DATEADD(dd, 0, DATEDIFF(dd, 0, t1.[OccurredOn]))

如果我从子查询中删除 WHERE子句，它将立即运行。

WHERE单独运行子查询的时间少于1秒

如果我先将子查询 SELECT放入表变量中，然后加入该变量，则整个查询将以19s运行。

Events表如下所示：

[Events](
    [EventType] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [OccurredOn] [datetime] NOT NULL,
)

我有以下 primary, nonclustered, nounique索引：


事件类型
用户身份
发生于


这是执行计划



使用SQL Server 2008

两件事情：


这是怎么回事？发生了什么使此速度变慢？
如何加快速度？


谢谢

Answer 1

您的查询很慢，因为您的订购取决于即时计算（DATEADD(dd, 0, DATEDIFF(dd, 0, t1.[OccurredOn]))），Sql Server无法利用即时计算中的索引。

Postgresql具有index on expression，使用Postgresql，您基本上可以将表达式的结果持久化为一个实际的列（在幕后的列），因此当需要时，您需要对该表达式进行排序，Postgresql可以使用索引在那个表情上。

Sql Server中最相似的功能是持久性公式。

您可以通过以下示例查询轻松地验证功能：

create table PersonX
(
Lastname varchar(50) not null,
Firstname varchar(50) not null
);

create table PersonY
(
Lastname varchar(50) not null,
Firstname varchar(50) not null
);


alter table PersonX add Fullname as Lastname + ', ' + Firstname PERSISTED;    
create index ix_PersonX on PersonX(Fullname);

declare @i int = 0;

while @i < 10000 begin
    insert into PersonX(Lastname,Firstname) values('Lennon','John');
    insert into PersonY(Lastname,Firstname) values('Lennon','John');
    set @i = @i + 1;
end;


select top 1000 Lastname, Firstname
from PersonX
order by Fullname;


select top 1000 Lastname, Firstname
from PersonY
order by Lastname + ', ' + Firstname;

在PersonX上用全名下单要比PersonY快。 PersonX的查询成本仅为32％，而PersonY为68％

要解决查询的性能，请执行以下操作：

alter table Events 
    add OccurenceGroup as 
        DATEADD(dd, 0, DATEDIFF(dd, 0, [OccurredOn])) PERSISTED

create index ix_Events on Events(OccurenceGroup);

然后在OccurenceGroup上进行分组和排序。



顺便说一句，您是否在OccuredOn以及EventType上添加了索引？