计数性能优化

Question

我得到了两个版本的基本相同的代码。(见下文)版本1运行时间为2秒，版本2运行时间为.5 - .6秒。目前从我选择的地方有1000万行，但这个数字上升得相当快。如果可能的话，我想再便宜一点。问题是，我使用版本2，我需要调用它30次(不同的状态，不同的用户名，等等)，最终的数字对于我需要的来说仍然太大了。有没有第三个版本我可以用来代替？或者有没有其他方法可以让我做得更快？或者我唯一能做的就是使用索引。

基本上，所有这些计数都会显示在web应用程序中访问量最大的屏幕上，当1000个用户同时使用系统时，30 * .5秒听起来有点太多了。

版本1

declare @a1 datetime; set @a1 = GETDATE()

declare @int1 INT,@int2 INT,@int3 INT,@int4 INT,@int5 INT,@int6 INT,@int7 INT,@int8 INT

select @int1 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'a'
select @int2 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'b'
select @int3 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'c'
select @int4 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'd'
select @int5 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'e'
select @int6 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'f'
select @int7 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'g'
select @int8 = COUNT(Id) from ToDos where StatusId = 1 and stringUserId = 'h'

select @int1, @int2, @int3, @int4, @int5, @int6, @int7, @int8

SELECT DATEDIFF(MILLISECOND, @a1, GETDATE())

版本2

declare @a1 datetime; set @a1 = GETDATE()

select stringUserId, count(stringUserId)
from ToDos
where StatusId = 1 and stringUserId in ('a','b','c','d','e','f','g','h')
group by stringUserId
order by COUNT(stringUserId) desc

SELECT DATEDIFF(MILLISECOND, @a1, GETDATE())

Answer 1

尝试条件计数。

select 
     @int1 = COUNT(CASE WHEN stringUserId = 'a' THEN 1 END), 
     @int2 = COUNT(CASE WHEN stringUserId = 'b' THEN 1 END), 
     @int3 = COUNT(CASE WHEN stringUserId = 'c' THEN 1 END), 
     @int4 = COUNT(CASE WHEN stringUserId = 'd' THEN 1 END), 
     @int5 = COUNT(CASE WHEN stringUserId = 'e' THEN 1 END), 
     @int6 = COUNT(CASE WHEN stringUserId = 'f' THEN 1 END), 
     @int7 = COUNT(CASE WHEN stringUserId = 'g' THEN 1 END), 
     @int8 = COUNT(CASE WHEN stringUserId = 'h' THEN 1 END)
from ToDos 
where StatusId = 1

仅供参考:我没有包含CASE的ELSE部分，因为默认情况下将返回NULL，而COUNT不计算空值

Answer 2

您可以尝试：

select a.* from (select stringUserId, count(stringUserId) as IDCount
from ToDos
where StatusId = 1 and stringUserId in ('a','b','c','d','e','f','g','h')
group by stringUserId) a
order by a.IDCount desc

这将从order by中删除count函数