-
May 10th, 2019, 09:03 PM
#1
Thread Starter
Addicted Member
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
-
May 11th, 2019, 06:00 AM
#2
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
-
May 13th, 2019, 06:48 AM
#3
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
-
May 13th, 2019, 08:32 AM
#4
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
-
May 16th, 2019, 06:28 PM
#5
Thread Starter
Addicted Member
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|