Microsoft Sql Server query optimization - join on dates

Question

how could I improve the performance of this query? I must have a 5 minutes partition and I'd like to run this on more than one day, ideally five. I can't run as a stored procedure.

DECLARE @StartDate datetime = '2019-02-11 00:00:00.001'
       ,@EndDate   datetime = '2019-02-13 23:59:59.999';

WITH theDates AS
 (SELECT @StartDate as theDate
  UNION ALL
   SELECT DATEADD(MINUTE, 5, theDate)
    FROM theDates
   WHERE DATEADD(MINUTE, 5, theDate) <= @EndDate) 

SELECT theDate as Time,
(SELECT ISNULL(AVG(CONVERT(BIGINT, (CAST(DATEDIFF(ms, a.stmpmsg, b.stmpmsg) as decimal(38,2))))),0) as avg
FROM db1 as a INNER JOIN db2 as b
    ON a.ProcInstLUID = b.ProcInstLUID

    and a.integrationId LIKE 'TAG%' and a.integrationid = b.integrationid and a.stepid = 'TAG_1'

    and (b.stepid = 'TAG_2' and b.msgstatusdet like 'TAG_3%') 
    and a.serviceId = 'TAG_4' and b.serviceId = 'TAG_5'
    and a.stmpmsg > @StartDate
    and a.stmpmsg < @EndDate
    and a.stmpmsg < b.stmpmsg
    AND DATEPART(DAY, a.stmpmsg) = datepart(DAY,theDate)
    AND DATEPART(hh, a.stmpmsg) = datepart(hh,theDate) 
    AND (DATEPART(n, a.stmpmsg) >= datepart(n,theDate)
    and DATEPART(n, b.stmpmsg) < (datepart(n,theDate)+5))
    WHERE 1=1 
) AS AvgLng 
FROM theDates 
OPTION (MAXRECURSION 0)

Running on Microsoft Sql Server, last time it take about 17 hours to give me a result.

The rCTE needs to go and it needs to be replaced with a Tally Table... — Thom A
– Thom A ♦, Commented Feb 21, 2019 at 16:42
On the subject of your JOIN, though, you're applying functions to your columns, like DATEPART. That is going to ruin any SARGability your query have. What is that JOIN actually trying to achieve. It looks like it's trying to join on rows that are between the date, and the date in 5 minutes? Also, due to the fact it's in a subquery, means that subquery is going to be reevaluated for every row. For the sample we have, that means it's going to be evaluated 865 times! — Thom A
– Thom A ♦, Commented Feb 21, 2019 at 16:45

Thom A · Accepted Answer · 2019-02-21 16:57:37Z

We don't have any sample data here, and I don't completely understand the OP's logic. This, instead, is to get the ball rolling for them. Without a response, there's little more I can do here though:

DECLARE @StartDate datetime = '2019-02-11T00:00:00.001',
        @EndDate datetime = '2019-02-13T23:59:59.999';
--Let's use a tally to start with
WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) V(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000 --That should be more than enough
    ),
--Generate the dates. Notice I provide both a start and end value
Dates AS(
    SELECT DATEADD(MINUTE, 5*T.I, @StartDate) AS StartDate,
           DATEADD(MINUTE, 5*(T.I+1), @StartDate) AS EndDate
    FROM Tally T
    WHERE DATEADD(MINUTE, 5*(T.I+1), @StartDate) <= @EndDate)
--And now, IF I understand your query correctly
SELECT {Columns go here}
FROM Dates D
     LEFT JOIN db1 a --A is a poor coice for an alias
          --This syntax is a little foreign to some, but I think it might be what you need
                   JOIN db2 b ON a.ProcInstLUID = b.ProcInstLUID
                              AND a.integrationId LIKE 'TAG%'
                              AND a.integrationid = b.integrationid
                              AND a.stepid = 'TAG_1'
                              AND (b.stepid = 'TAG_2'
                               AND b.msgstatusdet LIKE 'TAG_3%')
                              AND a.serviceId = 'TAG_4'
                              AND b.serviceId = 'TAG_5'
                   --This is pure guess work
                   ON a.stmpmsg >= D.StartDate
                  AND a.stmpmsg < D.EndDate
                  AND b.stmpmsg >= D.StartDate
                  AND b.stmpmsg < D.EndDate;;

Thanks a lot!! With some adjustment now works perfectly (it takes 50 sec to complete), and i understand what i did wrong! I know that the question was a little bit confusing, sorry about that.

Collectives™ on Stack Overflow

Microsoft Sql Server query optimization - join on dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related