Post reply

SQL optimization

  • April 27, 2015 at 9:21 am

    #317032

    hi,

    My query takes hours to perform this operation. can anyone tell me how to optimize or rewrite this query?

    DELETE FROm #sjy_Daily5_Commonalities

    where email like '%yahoo%' and userid not in (select userid from #temp2)

    Thanks in advance.

  • April 27, 2015 at 9:41 am

    #1793692

    Anything that uses a percent at the beginning (%yahoo%) is going to require a table scan of every value. No way around that.

    Perhaps full text indexing could help, but really you are not going to change performance much unless you can remove the leading wildcard.

  • April 27, 2015 at 10:20 am

    #1793718

    If you really need to manage stuff by domain name, I'd suggest adding a column to the table. Either load the domain from the email address, or make it a calculated column that pulls the domain from the email address. Then, you can just go with an equals operation and put an index on that column to help things out.

    Otherwise, you're kind of stuck as Steve says.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 27, 2015 at 10:44 am

    #1793738

    As that's a local temp table, couldn't you avoid inserting those rows in the first place? You would save time inserting and deleting.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 28, 2015 at 8:36 am

    #1793956

    Luis Cazares (4/27/2015)

    As that's a local temp table, couldn't you avoid inserting those rows in the first place? You would save time inserting and deleting.

    +1000

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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