locked
Run time RRS feed

  • Question

  • All the tables that this query isusing have Millions of records. What is the better way to reduce run time ? 

    SELECT ISNULL(Audit.Occurred,StartDate) as StartDate,id, alrtTime
    FROM DWInci as Inc
    LEFT OUTER JOIN
    (SELECT EntityID,Min(Occurred) as Occurred 
    FROM archiveAud 
    WHERE enttypee=2 
    AND eventtype='IncCreated'
    GROUP BY EntityID
    UNION ALL 
    SELECT EntityID,Min(Occurred) as Occurred 
    FROM aeAdt
    WHERE enttype=2 AND 
    eventtype='Inc Created'
    GROUP BY entityID) as Adt
    ON Inc.IncidentID=Audit.EntityID
    WHERE CAST(FLOOR(CAST(ISNULL(Adt.Occurred,StartDate) as FLOAT)) as DATETIME) ='2019-08-21'


    sami



    • Edited by arc2013 Thursday, August 22, 2019 6:30 PM
    Thursday, August 22, 2019 6:18 PM

Answers

  • Try this!

    ;With CTE_archiveAud As (SELECT EntityID,Min(Occurred) as Occurred 
    FROM archiveAud 
    WHERE enttypee=2 
    AND eventtype='IncCreated'
    GROUP BY EntityID
    )
    ,CTE_aeAdt As (
    SELECT EntityID,Min(Occurred) as Occurred 
    FROM aeAdt
    WHERE enttype=2 AND 
      eventtype='Inc Created'
    GROUP BY entityID
    )
    SELECT Coalesce(Adt.Occurred,arc.Occurred,StartDate) as StartDate,id, alrtTime
    FROM DWInci as Inc
    LEFT OUTER JOIN  CTE_archiveAud arc ON Inc.IncidentID = arc.EntityID
    LEFT OUTER JOIN  CTE_aeAdt Adt      ON Inc.IncidentID = Adt.EntityID
    WHERE CAST(FLOOR(CAST(Coalesce(Adt.Occurred,arc.Occurred,StartDate) as FLOAT)) as DATETIME) ='2019-08-21'


    mohammad waheed

    Thursday, August 22, 2019 7:18 PM

All replies

  • Try this!

    ;With CTE_archiveAud As (SELECT EntityID,Min(Occurred) as Occurred 
    FROM archiveAud 
    WHERE enttypee=2 
    AND eventtype='IncCreated'
    GROUP BY EntityID
    )
    ,CTE_aeAdt As (
    SELECT EntityID,Min(Occurred) as Occurred 
    FROM aeAdt
    WHERE enttype=2 AND 
      eventtype='Inc Created'
    GROUP BY entityID
    )
    SELECT Coalesce(Adt.Occurred,arc.Occurred,StartDate) as StartDate,id, alrtTime
    FROM DWInci as Inc
    LEFT OUTER JOIN  CTE_archiveAud arc ON Inc.IncidentID = arc.EntityID
    LEFT OUTER JOIN  CTE_aeAdt Adt      ON Inc.IncidentID = Adt.EntityID
    WHERE CAST(FLOOR(CAST(Coalesce(Adt.Occurred,arc.Occurred,StartDate) as FLOAT)) as DATETIME) ='2019-08-21'


    mohammad waheed

    Thursday, August 22, 2019 7:18 PM
  • Thanks for your reply. I will run this and check.

    curious to know why this last sql code was changed?

    WHERE CAST(FLOOR(CAST(ISNULL(Adt.Occurred,StartDate) as FLOAT)) as DATETIME) ='2019-08-21'

    to 

    CAST(FLOOR(CAST(Coalesce(Adt.Occurred,arc.Occurred,StartDate) as FLOAT)) as DATETIME) 


    sami

    Thursday, August 22, 2019 8:23 PM
  • If you use Isnull function then you need to call it twice which might reduce the performance

    Like this Isnull(Isnull(Adt.Occurred,arc.Occurred),StartDate)


    mohammad waheed

    Thursday, August 22, 2019 8:49 PM
  • Hi arc2013,

     

    Could you please share us your table structure and some indexes in your table along with your execution plan? So that we’ll get a right direction.

     

    I will advise you to create some indexes in your table.

     CREATE NONCLUSTERED INDEX IX_archiveAud_enttypee ON archiveAud (enttypee,eventtype);
    
    CREATE NONCLUSTERED INDEX IX_aeAdt_enttypee ON aeAdt(enttype,eventtype);


    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 23, 2019 6:52 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 28, 2019 9:07 AM
  • Mohammad Thanks for your reply.

    The main issue we are having is 

    I think the issue is that both the archiveAud   and aeAdt are massive tables and the query locks the tables preventing other writes to them. How can we solve this? 

    we are quering yesterday's data. We have other application that writes continously to these two tables. This query is preventing writes from other applications while this is running.


    sami

    Wednesday, September 11, 2019 6:02 PM
  • I will mark as answered once the issue is resolved. I have more to this issue.

    sami

    Wednesday, September 11, 2019 6:02 PM