计算列在排序或筛选方面太慢

Question

我有一个用于排序或过滤的计算列，但是当有 1000+ 行时，执行时间太长。

此查询适用于预订系统根据其价格订购可用日期。

下面是数据库架构：

AvailableDates has one DateGroup
DateGroup has many Prices
DateGroup has many Discounts
Each Discount contains 3 columns. MinPerson, MaxPerson, DiscountPercentage
AvailableDates has many BookingGroups.
BookingGroups has many Bookings.
BookingGroups has a computed column that calculates how many bookings there are.

可用日期上价格的计算列由函数计算。价格由以下因素确定;

Get Max Price from Prices
Get How many booking there is
Get discount that will be applied depending on number of bookings.

下面是函数查询：

FUNCTION [dbo].[fn_datePrice]
(
@id INT,
@groupId INT
)
RETURNS decimal(19, 5)
AS
BEGIN
declare @price decimal(19,5), @discount decimal(19,5), @numOfPeople INT
SELECT @numOfPeople= b.NumberOfPeople FROM BookingGroup b
WHERE b.DateId = @id and b.Status != 'Expired';
if (@numOfPeople is null or @numOfPeople < 1)
SET @numOfPeople = 1;
SELECT @price = MAX(pr.Price),
@discount = disc.DiscountPercentage
FROM DateGroup dateGroup
LEFT JOIN Prices pr on pr.GroupId = dateGroup.Id
LEFT JOIN Discounts disc on disc.GroupId = dateGroup.Id and @numOfPeople BETWEEN disc.MinPeople and disc.MaxPeople
WHERE dateGroup.Id = @groupId
GROUP BY dateGroup.Id, disc.DiscountPercentage;
if (@discount is null)
return @price
return @price * (100 - @discount) / 100
END;
GO

执行计划说 78% 的成本用于：密钥查找(群集([可用日期]。[PK_AvailableDate]

我的活动监视器显示此查询是最昂贵的查询：

SELECT @price = MAX(pr.Price),
@discount = disc.DiscountPercentage
FROM DateGroup dateGroup
LEFT JOIN Prices pr on pr.GroupId = dateGroup.Id
LEFT JOIN Discounts disc on disc.GroupId = dateGroup.Id and @numOfPeople BETWEEN disc.MinPeople and disc.MaxPeople
WHERE dateGroup.Id = @groupId
GROUP BY dateGroup.Id, disc.DiscountPercentage;

Answer 1

这可以帮助您理解计算列吗？ http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/10/the-query-optimizer-and-computed-columns.aspx

如果数据库大小和写入时间不是问题，我会考虑对架构进行非规范化。这将消除以写入时间为代价的函数计算的需要。例如，日期组 x 价格 x 折扣可以位于一个表中。该表可以是唯一的，并按日期组ID进行聚簇索引以进行查找。

Answer 2

我试图重写你的连接：

SELECT @price = MAX(pr.Price),
@discount = disc.DiscountPercentage
FROM DateGroup dateGroup
LEFT JOIN Prices pr on pr.GroupId = dateGroup.Id
LEFT JOIN Discounts disc on disc.GroupId = dateGroup.Id 
WHERE   (@numOfPeople BETWEEN disc.MinPeople and disc.MaxPeople)
AND (dateGroup.Id = @groupId)
GROUP BY dateGroup.Id, disc.DiscountPercentage;

尝试让我知道它是否有影响。

Answer 3

始终可以尝试将此函数重写为内联函数，您需要记住，多行函数总是比内联函数慢得多。