0

There are two tables

Table 1 :

Year     char(4)
MonthKey char(2)
ComplaintCount int

Table 2 :

FileCode  varchar(50)
StartDate varchar(10)
EndDate   varchar(10)

I wrote a query and the query takes a long long time to execute. Of course there is no result and its still executing and i think it is in a loop maybe, which is not expected!

The query is:

SELECT dbo.GetLastDayOfMonth(c.Year,c.Monthkey) AS DateKey,
       ComplaintCount,
       Count(FileCode)
FROM Table1 C
INNER JOIN Table2 F
    ON dbo.GetLastDayOfMonth(c.Year,c.Monthkey) = dbo.GetLastDayOfMonth(LEFT(f.FileFormationDate,4),SUBSTRING(f.FileFormationDate,6,2))
GROUP BY dbo.GetLastDayOfMonth(c.Year,c.Monthkey), c.ComplaintCount

The function GetLastDayOfMonth() returns the last day of month according to the Persian Calendar. There are 3,000,000 records in Table 1 and 3 records in Table 2. Even running the JOIN by itself (without the GROUP BY) is still executing and until now i can see 1,000,000 records in the Results pane.

What could the problem be and how can I fix it?

This is the code for the function:

CREATE FUNCTION [dbo].[GetLastDayOfMonth] (
    @year CHAR(4)
    ,@Month CHAR(2)
    )
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @result VARCHAR(10)

    SET @result = (
            SELECT max(datekey)
            FROM StageArea.Common.DimTime
            WHERE [Year] = @year
                AND MonthKey = @Month
                AND LastDayOfMonthKey = 1
            )

    RETURN @result
END
  • What version of sql server do you use ? because starting with 2012 it is a build in function : EOM - (end of month) , Anyhow, the use of scalar function - is the one that is creating your problem. What about adding a computed column ? ( do get the right date (and index it)) – Sabin Bio Aug 16 '17 at 10:23
  • Hi. I can not use EOM cause we are in Iran and we need a persian date so we've created a function in order to have the persian date.and the version in 2014 – Pantea Aug 16 '17 at 10:25
  • 1
    Calendar table do you have? I think , this would resolve the left side of the join. – Sabin Bio Aug 16 '17 at 10:30
  • If you would not insist on storing dates as varchar (instead of date data type) you could use efficient date functions. – TomTom Aug 16 '17 at 10:48
  • 1
    Pantea, could you add the code for the scalar function to your question above? That is the most likely bottleneck. I also recommend you use a calendar table with the Persian dates, months and years added to it, so you can JOIN to it, instead of using complicated scalar functions. – Oreo Aug 16 '17 at 11:58
1

Based on the answer the OP gave, I'll submit the below attempt at blindly optimising the code, that avoids using scalar functions to get the DateKeys.

Instead, we grab the DateKey logic out of the function and use it as the basis of our JOINs to the 2 tables containing the count of distinct FileCodes and the count of Complaints:

SELECT D.DateKey
    ,F.ComplaintCount
    ,C.FileCodeCount
FROM (
    SELECT [Year]
        ,MonthKey
        ,MAX(DateKey) AS DateKey
    FROM StageArea.Common.DimTime
    WHERE LastDayOfMonthKey = 1
    GROUP BY [Year]
        ,MonthKey
    ) D
INNER JOIN [StageArea].[Preservation].[viwComplaint] F
    ON F.[Year] = D.[Year] AND F.MonthKey = D.MonthKey
INNER JOIN (
    SELECT LEFT(FileFormationDate, 4) AS [Year]
        ,SUBSTRING(FileFormationDate, 6, 2) AS MonthKey
        ,COUNT(DISTINCT FileCode) AS FileCodeCount
    FROM [StageArea].[Sales].[viwLossCalculation]
    GROUP BY LEFT(FileFormationDate, 4)
        ,SUBSTRING(FileFormationDate, 6, 2)
    ) C
    ON C.[Year] = D.[Year] AND C.MonthKey = D.MonthKey

For further performance optimisations, I would look within the [StageArea].[Sales].[viwLossCalculation] and [StageArea].[Preservation].[viwComplaint] views.

  • Good point about the scalar function and a really good effort deciphering the logic behind the OP's query. – Andriy M Aug 16 '17 at 22:46
0

Try this using CTE, you're only calling the function a minimal amount of times this way. Could potentially be improved if you can filter the first queries somehow if appropriate.

    WITH T1
AS (SELECT TOP (2147483647) dbo.GetLastDayOfMonth(c.Year, c.Monthkey) AS DateKey,
           ComplaintCount,
           FileCode
    FROM Table1 c
   ),
     T2
AS (SELECT TOP (2147483647) dbo.GetLastDayOfMonth(LEFT(f.FileFormationDate, 4), SUBSTRING(f.FileFormationDate, 6, 2)) AS DateKey2
    FROM Table2 f
   )
SELECT C.DateKey,
       C.ComplainCount,
       COUNT(C.FileCode) AS FileCodeCount
FROM T1 C
    INNER JOIN T2 F
        ON C.DateKey = F.DateKey2
GROUP BY C,
         DateKey,
         C.ComplaintCount;
  • Unfortunately it has been 45 minutes that the query is running even by using CTE :( – Pantea Aug 16 '17 at 11:44
  • 1
    How big are the respective tables? Could you post an execution plan for it? – Ollie Aug 16 '17 at 12:01
  • 1
    CTEs aren't materialised in SQL Server, so your join T1 to T2 is still between two functions, not their materialised output. A CTE is unlikely to be any better in this scenario – Mark Sinkinson Aug 16 '17 at 12:05
  • ah yeah that keeps escaping me but its not a total loss, just been trying this out on a mock up with 10 million rows. I can somewhat mitigate the non materialization using TOP (2147483647) , @Pantea give it a go, i've edited the answer – Ollie Aug 16 '17 at 13:46
  • forgot to mention, on my mock up with the top it takes 2 mins 47, without, well its been going to 15 mins i'll update if it finished before i leave work – Ollie Aug 16 '17 at 13:47
0

Thank you for your effort and suggestions. Here is the solution which took only 6 minutes to execute:

WITH T1
AS (
    SELECT FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2)) AS Datekey
    FROM [StageArea].[Sales].[viwLossCalculation] f
    GROUP BY FileCode
        ,dbo.GetLastDayOfMonth(left(f.FileFormationDate, 4), substring(f.FileFormationDate, 6, 2))
    )
    ,T2
AS (
    SELECT dbo.GetLastDayOfMonth(c.Year, c.Monthkey) AS datekey2
        ,ComplaintCount AS complaintcount
    FROM [StageArea].[Preservation].[viwComplaint] c
    )
SELECT C.DateKey
    ,f.complaintcount
    ,COUNT(DISTINCT c.FileCode) AS FileCodeCount
FROM T1 C
INNER JOIN T2 F ON C.DateKey = F.DateKey2
GROUP BY C.DateKey
    ,f.ComplaintCount;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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