Long running left join query

  • digivince

    SSC-Addicted

    Points: 420

    This query runs for 20+ minutes on a powerful 4 CPU server wtih 16GB RAM and DBs on fibre channel SAN. It is not being blocked. I looked at the plan and the hash join is the largest cost. I ran it through tunning wiz and there are no index recommendations. Table1 has close to 1 million rows. I know with a left join it has to scan through all records. Is it possible to write this using a subquery, temp table or other method?

    update dbo.table1

    set field1 = 'flag'

    FROM dbo.table1 LEFT JOIN

    dbo.table2 ON dbo.table1.ID = dbo.table2.ID

    WHERE (dbo.table1.ID > '0') AND (dbo.table2.ID IS NULL)

    go

    Columbus, GA SQL Server User Group
    http://columbusga.sqlpass.org/

  • digivince

    SSC-Addicted

    Points: 420

    Guest Columnist Ken Simmons suggested I look at EXCEPT. Sounds just like a left join. I'll post back and let readers know it it improved my query exec time.

    From MSDN: "EXCEPT

    Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query."

    Columbus, GA SQL Server User Group
    http://columbusga.sqlpass.org/

  • Grant Fritchey

    SSC Guru

    Points: 396614

    If you can, post the structure and the execution plan. Just because the DTA didn't find a good index doesn't mean one shouldn't be created. That tool is spotty at the best of times.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720383

    I assume you have the ID columns indexed on both tables. A "not exists" subquery might help, but as Grant said, you'd need to post more details and do some testing.

  • digivince

    SSC-Addicted

    Points: 420

    SELECT ID

    FROM table1

    EXCEPT

    SELECT ID

    FROM table2

    Runs MUCH faster. Thanks.

    Columbus, GA SQL Server User Group
    http://columbusga.sqlpass.org/

Viewing 5 posts - 1 through 5 (of 5 total)

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