sql - 存储过程重构

Question

我正在考虑重构此存储过程，因为它似乎导致使用 100% DTU 容量的 Azure DB 超时。

有人对我如何改进存储过程中使用的 SQL 有任何建议吗？

谢谢

declare @value bigint
declare @secondValue bigint 

select @value = count(*) from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] = 'paid') AND profilestatus = 'something'
and ExpiresOn >= CURRENT_TIMESTAMP

select @trialing = count(*) from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] = 'avalue') AND profilestatus = 'something'
and ExpiresOn >= CURRENT_TIMESTAMP

select @value as firstValue, @secondValue as secondValue

客户统计:

Answer 1

我发现您想要获取两个计数 - [Type] 的两个值中的每一个计数，在这种情况下，group by 应该会显着提高您的性能:

select [Type], count(*) AS cnt
from [dbo].[myTable]
where ([Name] = 'something') AND ([Type] IN ('paid', 'avalue')) AND profilestatus = 'something' AND ExpiresOn >= CURRENT_TIMESTAMP
group by [Type]

您可以将该查询合并到您的存储过程中，期望结果集中有两条记录。