2

I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause which converts the date format. It only has 7 tables joined on simple On condition (No crazy stuff).

Going forward I need to add couple of more tables to the join list. This will only make the performance way worse. I need to fine tune the current query before I starting adding to it.

Here is the query:

 SELECT [dbo].[dFK](oew.StartDate) AS StartDate, -- INTEGER DATE!
    [dbo].[dFK](oew.EndDate) AS EndDate,
    [dbo].[dFK](oew.EffectiveDate) AS EffectiveDate
FROM    OpenEnrollmentWindow oew
    INNER JOIN ProductYear py ON oew.OrganizationProductYearID = py.ID
    INNER JOIN Marketplace m ON py.MarketplaceID = m.ID
    INNER JOIN Organization o ON m.OrganizationID = o.ID
    INNER JOIN Consumer c ON c.OrganizationID = o.ID
    LEFT JOIN OpenEnrollmentWindowProduct oewp ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
    LEFT JOIN OpenEnrollmentWindowProductType oewpt ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID

enter image description here

Here is the definition of the function:

CREATE FUNCTION [dbo].[dFK]
(@dt as sql_variant)
RETURNS int
AS
BEGIN
    DECLARE @type varchar(128)  
    DECLARE @iDate int
    SET @type = CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
    SET @iDate =
        CASE 
            WHEN @type = 'int' AND @dt >= 19000101 AND @dt <= 20451231 THEN CONVERT(int, @dt)
            WHEN @type = 'int' AND @dt < 19000101 OR @type = 'int' AND @dt > 20451231 THEN 1
            WHEN @dt IS NULL THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME) OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME)) AND @type = 'datetime' THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE) OR @dt > CAST('2045-12-31' AS DATE)) AND @type = 'date' THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    RETURN @iDate
END

GO

This is used as a fact table source. The date is converted to avoid a reverse lookup against date dimension. Let's just say it has to be converted at server side only. It is spitting out some 6 million rows. Now I do understand that's quite a lot, and that's why I am seeking some query optimization suggestions here.

5
  • 5
    It's important to note that those functions will need to be executed for every row returned. That's probably your problem.
    – Zane
    Commented Sep 18, 2015 at 18:55
  • What datatype are you passing into that function? DATETIME? Commented Sep 18, 2015 at 19:18
  • 2
    The function is 90% likely to be the problem here. You should convert it to an inline table-valued function and also declare it to only receive a specific datatype for its parameter. Commented Sep 18, 2015 at 19:20
  • Invariably the function as many have said. And looking at the code of this, it would be easy to make this not a function call at all, by replacing each of those calls with the case statement and replacing the variables with their values (type with the definition of type and dt with whatever your column being passed in is). That is an easy change that will likely help a great deal, but as others have said there are likely even better ways than that.
    – mskinner
    Commented Sep 18, 2015 at 19:23
  • Why not just fix the data and datatype. Smalldatetime has close to range you are looking for 1900-01-01 through 2079-06-06. Or just use Date.
    – paparazzo
    Commented Sep 18, 2015 at 19:24

1 Answer 1

10

The problem is the use of scalar functions. These are executed once per reference per row, and the current internal implementation is such that this is very nearly as expensive as running a separate query per invocation (18 million times, for 6 million rows with three function references per row).

A quick solution is to convert the function to an in-line table-valued function. These are in-lined into the query text, much in the same way views are expanded before query optimization. So the first step is to translate the function to:

CREATE FUNCTION dbo.dFK_InLine
    (@dt as sql_variant)
RETURNS table
AS
RETURN
    SELECT
        ReturnValue =
        CASE 
            WHEN CA.datatype = 'int' 
                AND @dt >= 19000101 AND @dt <= 20451231
                THEN CONVERT(integer, @dt)
            WHEN (CA.datatype = 'int' AND @dt < 19000101) 
                OR (CA.datatype = 'int' AND @dt > 20451231)
                THEN 1
            WHEN @dt IS NULL 
            THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME) 
                OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME)) 
                AND CA.datatype = 'datetime' 
            THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE) 
                OR @dt > CAST('2045-12-31' AS DATE)) 
                AND CA.datatype = 'date' 
            THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    FROM
    (
        VALUES
        (
            CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
        )
    ) AS CA (datatype);

Then modify the source query to use it:

SELECT 
    SD.ReturnValue AS StartDate, -- INTEGER DATE!
    ED.ReturnValue AS EndDate,
    EFD.ReturnValue AS EffectiveDate
FROM OpenEnrollmentWindow oew
CROSS APPLY dbo.dFK_InLine(oew.StartDate) AS SD
CROSS APPLY dbo.dFK_InLine(oew.EndDate) AS ED
CROSS APPLY dbo.dFK_InLine(oew.EffectiveDate) AS EFD
INNER JOIN ProductYear py 
    ON oew.OrganizationProductYearID = py.ID
INNER JOIN Marketplace m 
    ON py.MarketplaceID = m.ID
INNER JOIN Organization o 
    ON m.OrganizationID = o.ID
INNER JOIN Consumer c 
    ON c.OrganizationID = o.ID
LEFT JOIN OpenEnrollmentWindowProduct oewp 
    ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
LEFT JOIN OpenEnrollmentWindowProductType oewpt 
    ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID;

All that said, this is still quite an...unusual strategy, particularly the use of sql_variant and the CASE logic. You might get better value from refactoring the design to use a strong types, and a more traditional model.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.