locked
Scalar function Degrading query performance, how to tune my query? RRS feed

  • Question

  • below is the Query,Inner query executing well ,but i am passing inner query result to functions as a parameter  to multiple functions,Functions are taking time to give the results,please help me to tune this query,thanks in advance,below is the query

    SELECT
    YYYY
    ,CalendarQuarter
    ,Account_id
    ,current_quarter Cur_Qtr_Date
    ,previous_quarter Pre_Qtr_Date
    ,previous_year Pre_Year_Qtr_Date
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (current_quarter, app_quarters.Account_id) AS current_quarter_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (previous_quarter, app_quarters.Account_id) AS previous_mont_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_gross_Whsl_amount
    ,[dbo].[ufnGetnetsalesWhsl_qtr](current_quarter, app_quarters.Account_id) AS current_quarter_net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_quarter, app_quarters.Account_id) AS previous_mont__net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_net_Whsl

    FROM (
    SELECT YYYY
    ,CalendarQuarter
    ,wsf.Account_id AS Account_id
    ,sum(wsf.net_sales_amount) Net_Whsl_Amount
    ,sum(wsf.gross_sales_amount) Gross_Whsl_Amount
    ,sum(wsf.net_sales_units) Net_Unit_Sales
    ,min(time_date) current_quarter
    ,dateadd(quarter, - 1, min(time_date)) previous_quarter
    ,dateadd(month, - 12, min(time_date)) previous_year
    FROM date_dim dd
    ,whl_sales_fact wsf
    ,Company_dim sd
    WHERE dd.time_id = wsf.time_id
    AND wsf.Account_id = sd.company_id
    AND YYYY = (SELECT YEAR( Dateadd(M,-1,GETDATE())))

    GROUP BY dd.YYYY,dd.CalendarQuarter
    ,wsf.Account_id
    ) app_quarters

    Tuesday, January 7, 2020 7:09 AM

Answers

  • Is that possible to rewrite UDF as JOIN?

    ALTER FUNCTION [dbo].[ufnGetnetsalesWhsl_qtr](@cur_date date, @Account_id varchar(20))
    RETURNS TABLE
    AS

    return(select sum(a.net_sales_amount)
    from whl_sales_fact a join date_dim b
    on a.time_id = b.time_id 
    where a.Account_id = @Account_id
    and datename(Quarter,@cur_date) + datename(year,@cur_date) = datename(quarter,b.time_date) + datename(year,b.time_date)

    )

    Now

    SELECT
    YYYY
    ,CalendarQuarter
    ,Account_id
    ,current_quarter Cur_Qtr_Date
    ,previous_quarter Pre_Qtr_Date
    ,previous_year Pre_Year_Qtr_Date

    /*
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (current_quarter, app_quarters.Account_id) AS current_quarter_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (previous_quarter, app_quarters.Account_id) AS previous_mont_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_gross_Whsl_amount
    ,[dbo].[ufnGetnetsalesWhsl_qtr](current_quarter, app_quarters.Account_id) AS current_quarter_net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_quarter, app_quarters.Account_id) AS previous_mont__net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_net_Whsl

    /*
    d.previous_year_net_Whsl
    FROM (
    SELECT YYYY
    ,CalendarQuarter
    ,wsf.Account_id AS Account_id
    ,sum(wsf.net_sales_amount) Net_Whsl_Amount
    ,sum(wsf.gross_sales_amount) Gross_Whsl_Amount
    ,sum(wsf.net_sales_units) Net_Unit_Sales
    ,min(time_date) current_quarter
    ,dateadd(quarter, - 1, min(time_date)) previous_quarter
    ,dateadd(month, - 12, min(time_date)) previous_year
    FROM date_dim dd
    ,whl_sales_fact wsf
    ,Company_dim sd
    WHERE dd.time_id = wsf.time_id
    AND wsf.Account_id = sd.company_id
    AND YYYY = (SELECT YEAR( Dateadd(M,-1,GETDATE())))

    GROUP BY dd.YYYY,dd.CalendarQuarter
    ,wsf.Account_id
    ) app_quarters

    cross apply [ufnGetnetsalesWhsl_qtr] (current_quarter, app_quarters.Account_id) as d


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 7, 2020 8:10 AM
    Answerer

All replies

  • Function code(all of the functions are similar to below function)

    ALTER FUNCTION [dbo].[ufnGetnetsalesWhsl_qtr](@cur_date date, @Account_id varchar(20))
    RETURNS decimal
    AS
    BEGIN
    declare @ret_value decimal
    select @ret_Value = sum(a.net_sales_amount)
    from whl_sales_fact a join date_dim b
    on a.time_id = b.time_id 
    where a.Account_id = @Account_id
    and datename(Quarter,@cur_date) + datename(year,@cur_date) = datename(quarter,b.time_date) + datename(year,b.time_date)
    return @ret_Value
    END
    Tuesday, January 7, 2020 7:12 AM
  • Is that possible to rewrite UDF as JOIN?

    ALTER FUNCTION [dbo].[ufnGetnetsalesWhsl_qtr](@cur_date date, @Account_id varchar(20))
    RETURNS TABLE
    AS

    return(select sum(a.net_sales_amount)
    from whl_sales_fact a join date_dim b
    on a.time_id = b.time_id 
    where a.Account_id = @Account_id
    and datename(Quarter,@cur_date) + datename(year,@cur_date) = datename(quarter,b.time_date) + datename(year,b.time_date)

    )

    Now

    SELECT
    YYYY
    ,CalendarQuarter
    ,Account_id
    ,current_quarter Cur_Qtr_Date
    ,previous_quarter Pre_Qtr_Date
    ,previous_year Pre_Year_Qtr_Date

    /*
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (current_quarter, app_quarters.Account_id) AS current_quarter_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr] (previous_quarter, app_quarters.Account_id) AS previous_mont_gross_Whsl_amount
    ,[dbo].[ufnGetgrosssalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_gross_Whsl_amount
    ,[dbo].[ufnGetnetsalesWhsl_qtr](current_quarter, app_quarters.Account_id) AS current_quarter_net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_quarter, app_quarters.Account_id) AS previous_mont__net_Whsl
    ,[dbo].[ufnGetnetsalesWhsl_qtr](previous_year, app_quarters.Account_id) AS previous_year_net_Whsl

    /*
    d.previous_year_net_Whsl
    FROM (
    SELECT YYYY
    ,CalendarQuarter
    ,wsf.Account_id AS Account_id
    ,sum(wsf.net_sales_amount) Net_Whsl_Amount
    ,sum(wsf.gross_sales_amount) Gross_Whsl_Amount
    ,sum(wsf.net_sales_units) Net_Unit_Sales
    ,min(time_date) current_quarter
    ,dateadd(quarter, - 1, min(time_date)) previous_quarter
    ,dateadd(month, - 12, min(time_date)) previous_year
    FROM date_dim dd
    ,whl_sales_fact wsf
    ,Company_dim sd
    WHERE dd.time_id = wsf.time_id
    AND wsf.Account_id = sd.company_id
    AND YYYY = (SELECT YEAR( Dateadd(M,-1,GETDATE())))

    GROUP BY dd.YYYY,dd.CalendarQuarter
    ,wsf.Account_id
    ) app_quarters

    cross apply [ufnGetnetsalesWhsl_qtr] (current_quarter, app_quarters.Account_id) as d


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 7, 2020 8:10 AM
    Answerer
  • If you are able to run the query on SQL 2019, I would expect that you would see a drastic improvement, since on SQL 2019, SQL Server inlines UDFs in many cases, and the UDF you posted certainly qualifies for inlining.

    Else rewrite to an inline table function as suggested by Uri.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 7, 2020 11:05 PM
  • Thank you so much @Uri Dimant ,I have changed all the functions to table valued function,Now i am getting result set in less that 3 minutes,earlier it's more than two hours..:)
    below is the changed Query

    SELECT YYYY
    ,CalendarQuarter
    ,Account_id
    ,current_quarter AS Cur_Qtr_Date
    ,previous_quarter AS Pre_Qtr_Date
    ,previous_year AS Pre_Year_Qtr_Date
    ,current_quarter_net_Whsl
    ,PQ_Net.net_sales_amount AS previous_quarter_net_Whsl
    ,PY_Net.net_sales_amount AS previous_year_net_Whsl
    ,current_quarter_gross_Whsl_amount
    ,PQ_Gross.gross_sales_amount AS previous_quarter_gross_Whsl_amount
    ,PY_Gross.gross_sales_amount AS previous_year_gross_Whsl_amount
    ,current_quarter_gross_Whsl_amount
    ,PQ_Units.net_sales_units AS previous_quarter_Unit_Sales
    ,PY_Units.net_sales_units AS previous_year_Unit_Sales
    ,CQ_StockValue.stock_Val_Usd_Net AS Current_quarter_Stock_Value
    ,PQ_StockValue.stock_Val_Usd_Net AS previous_quarter_Stock_Value
    ,PY_StockValue.stock_Val_Usd_Net AS previous_year_Stock_Value
    FROM (
    SELECT YYYY
    ,CalendarQuarter
    ,wsf.Account_id AS Account_id
    ,sum(wsf.net_sales_amount) current_quarter_net_Whsl
    ,sum(wsf.gross_sales_amount) current_quarter_gross_Whsl_amount
    ,sum(wsf.net_sales_units) current_quarter_Unit_Sales
    ,min(time_date) current_quarter
    ,dateadd(quarter, - 1, min(time_date)) previous_quarter
    ,dateadd(month, - 12, min(time_date)) previous_year
    FROM date_dim dd
    ,whl_sales_fact wsf
    ,Company_dim sd
    WHERE dd.time_id = wsf.time_id
    AND wsf.Account_id = sd.company_id
    AND YYYY = (
    SELECT YEAR(Dateadd(M, - 1, GETDATE()))
    )
    GROUP BY dd.YYYY
    ,dd.CalendarQuarter
    ,wsf.Account_id
    ) app_quarters
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetnetsalesWhsl_qtr](app_quarters.previous_quarter, app_quarters.Account_id) AS TT
    ) AS PQ_Net
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetnetsalesWhsl_qtr](app_quarters.previous_year, app_quarters.Account_id) AS TT
    ) AS PY_Net
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetgrosssalesWhsl_qtr](app_quarters.previous_quarter, app_quarters.Account_id) AS TT
    ) AS PQ_Gross
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetgrosssalesWhsl_qtr](app_quarters.previous_year, app_quarters.Account_id) AS TT
    ) AS PY_Gross
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetunitssalesWhsl_qtr](app_quarters.previous_quarter, app_quarters.Account_id) AS TT
    ) AS PQ_Units
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetunitssalesWhsl_qtr](app_quarters.previous_year, app_quarters.Account_id) AS TT
    ) AS PY_Units
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetStockValueWhsl_qtr](app_quarters.current_quarter, app_quarters.Account_id) AS TT
    ) AS CQ_StockValue
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetStockValueWhsl_qtr](app_quarters.previous_quarter, app_quarters.Account_id) AS TT
    ) AS PQ_StockValue
    CROSS APPLY (
    SELECT TT.*
    FROM [dbo].[ufnGetStockValueWhsl_qtr](app_quarters.previous_year, app_quarters.Account_id) AS TT
    ) AS PY_StockValue

      
    Wednesday, January 8, 2020 9:57 AM
  • I am using Azure SQL
    Wednesday, January 8, 2020 9:57 AM
  • I am using Azure SQL
    In such case your UDF should be inlined. But since you already have rewritten the query and good result, there's no reason investigate this further right now.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 8, 2020 10:48 AM