Post reply

Query last too long

  • June 5, 2020 at 12:31 pm

    #3760808

    Hello,

     

    I have this execution plan and the query last   24 h. How we can improve it?

    Thanks,

    Hadrian

  • June 5, 2020 at 12:48 pm

    #3760821

    aureliu wrote:

    Hello,I have this execution plan and the query last   24 h. How we can improve it?

    Thanks,

    Hadrian

    Which execution plan?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 5, 2020 at 12:51 pm

    #3760824

    Hi,It was rejected once.

  • June 5, 2020 at 12:55 pm

    #3760825

    It's just XML. Try renaming to .txt and uploading again.

    Edit: or .XML.

    • This reply was modified 4 years, 5 months ago by  Phil Parkin.
    • This reply was modified 4 years, 5 months ago by  Phil Parkin.

    Attachments:
    You must be logged in to view attached files.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • June 5, 2020 at 1:18 pm

    #3760841

    Or upload it to Paste the Plan and share the link.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 5, 2020 at 1:40 pm

    #3760846

    now? Thanks,H

  • June 5, 2020 at 1:42 pm

    #3760847

    sorry,now?

  • June 5, 2020 at 1:52 pm

    #3760850

    aureliu wrote:

    sorry,now?

    Nope, the report will clearly show if there's an attachement when you're sending it:

    Perhaps you'll be better off using Paste the Plan as you appear to be struggling here.

    (You can ignore the attached image, was just a random one on my PC)

    Attachments:
    You must be logged in to view attached files.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 5, 2020 at 1:57 pm

    #3760852

    maybe now

    Attachments:
    You must be logged in to view attached files.

  • June 5, 2020 at 2:02 pm

    #3760860

    A quick note, the clause pbRecordId NOT IN (SELECT DISTINCT pbRecordId FROM staging.sfSurveillanceRecordHeaderUpdated) doesn't need that (possibly expensive) DISTINCT. both 1 NOT IN (1,2,3,4,1,1,2,4) and 1 NOT IN (1,2,3,4) are gong to result in the same exclusions.

    Also, if you do have any NULL values for the column pbRecordId then that isn't going to work as you expect; it's why I prefer NOT EXISTS.

    On a related note, I strongly advise qualifying your column names for a clause like that. If for no other reason than for what Gail explains here: No, this is not a bug in T-SQL

    • This reply was modified 4 years, 5 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 5, 2020 at 2:19 pm

    #3760868

    I would change the WHERE in the last query to:

      FROM staging.sfSurveillanceRecordHeader s1
     WHERE NOT EXISTS (SELECT *
                         FROM staging.sfSurveillanceRecordHeaderUpdated s2
                        WHERE s2.pbRecordId = s1.pbRecordId);

    I think the following index will help:

     CREATE INDEX IX_sfSurveillanceRecordHeader_pbRecordId 
         ON staging.sfSurveillanceRecordHeader(pbRecordId) 
    INCLUDE (currentVersion,validTo);

    If that doesn't make a difference drop it:

    DROP INDEX IX_sfSurveillanceRecordHeader_pbRecordId ON staging.sfSurveillanceRecordHeader;

    and try this one instead:

    CREATE INDEX IX_sfSurveillanceRecordHeaderUpdated_pbRecordId 
        ON staging.sfSurveillanceRecordHeaderUpdated(pbRecordId);

     

     

     

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply