CODEWITHSUNDEEP
sqlsql-servert-sql
erkinyldz
erkinyldz

Reputation: 688

Computed Column is too slow in ordering or filtering

I have a computed column I use for ordering or filtering, but when there is 1000+ rows it takes too long to execute.

This query is for a booking system to order the Available Dates depending on their prices.

Here is database schema:

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.

The computed column for price on AvailableDate is computed by a function. Price is determined with;

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

Here is the function query:

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

Execution Plan says 78% of the cost is on: Key Lookup (Clustered) [AvailableDate].[PK_AvailableDate]

My activity monitor says this query is the most expensive one:

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;

Upvotes: 0

Views: 203

Answers (3)

Pho
Pho

Reputation: 108

Can this help you understand the computed columns? http://sqlblog.com/blogs/ben_nevarez/archive/2009/08/10/the-query-optimizer-and-computed-columns.aspx

If DB size and write times are not issues, I would consider to denormalise the schema. That would remove the need for a function to compute at the cost of write times. For example, the dategroup x price x discount could be in one table. The table can be unique & clustered indexed by dategroupid for look up.

Upvotes: 1

Piotr Zieliński
Piotr Zieliński

Reputation: 174

Always you can try to rewrite this function to inline, you need to remember that multiline-functions are alwyas much slower than inline-functions.

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

I have tried to re-write your join :

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;

try and let me know if it makes impact.

Upvotes: 0

Related Questions