Results 1 to 5 of 5

Thread: not in Faster solution

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    197

    not in Faster solution

    Below is the code I used NOT IN, the speed is a bit slow, who is better? Thank you!

    select top 500 sDate,sXLH,sKhmc,sState from tblOrders a WHERE sdate between Format(DateAdd("D", -7, getdate()), 'yyyy-MM-dd hh:mm:ss') AND
    Format(getDate(),'yyyy-MM-dd') + ' 23:59:59' AND sXLH NOT IN (SELECT TOP 1000 sxlh FROM tblSpec WHERE a.sXLH=tblspec.sxlh ORDER BY rq desc) order by sDate desc
    QQ: 289778005

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: not in Faster solution

    What about a Left Join with right hand side IS NULL?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: not in Faster solution

    I would calc the two dates place in var instead of having the function in the where clause
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: not in Faster solution

    There's a few options for handling exclusions. It's hard to predict which will perform best so try them out against production data and see for yourself.

    1. The Not In approach generally doesn't perform very well in my experience. It requires the engine to resolve the entire sub query, though I think any approach is going to require that in your case due the Order By.
    2. The Left Join Zvoni is proposing is a very common pattern and generally does pretty well.
    3. You can use a Not Exists which usually out-performs a Not In in my experience because it doesn't require the whole inner select to be resolved if the engine falls back on a RBAR. I'm not sure it will help much in this case because the order by will force the inner select to be fully resolved.
    4. Finally, you can use an Except to exclude and sXLHs you don't want. In my experience this usually provides the best performance but I usually use it on Primary Keys, which you're not, and, again, I'm not sure how the inner order by is going to impact things.

    As Gary has pointed out, Getting the two dates into variables is likely to help. the query engine might be clever enough to recognise they only need resolving once but I wouldn't guarantee it. And having them as function calls in the Where is very likely to make that criteria non-sargable.

    Also, you don't need to format the dates at all. That's turning them into varchar types but a Between is perfectly capable of operating against DateTimes. From the fact that you're adding 23:59:59 my guess is that you want to include the whole of the ned date. A better approach is to cast them as Dates and use < on the second date + 1 Day, e.g.
    Where SDate >= DateAdd(Day, -7, Cast(GetDate() as Date))
    And SDate < DateAdd(Day, 1, Cast(GetDate() as Date))
    (I've left the functions in-line for illustrative purposes but that doesn't mean you shouldn't follow Gary's Variable suggestion)
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2016
    Location
    China
    Posts
    197

    Re: not in Faster solution

    Thank you, I have a solution. The reason for the slowness is that the amount of query data is relatively large. Now I use the temporary table to solve it: put the two query results into two temporary tables, and finally query the results from the temporary table. The number of two temporary tables is small, so the query speed is very fast.
    QQ: 289778005

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width