June 24, 2008 at 11:34 am
Hello All
Does anyone have any thoughts on how to better optimize this comparison? This query locks all CPU on this server and I know that there must be a better way. Does SQL SERVER at least have a hint to disable multiprocessor support for just a single query?
SELECT user_key AS User_id, update_date AS createDate
FROM accounting.dbo.user
WHERE user_key NOT IN (SELECT DISTINCT CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) AS user_key
FROM Prod.dbo.return_vendors)
AND (ISNULL(update_date, '') <> '' AND update_date <= GETDATE() - 1)
Both tables contain about 800000 row.
Let me know what you guys think.
June 24, 2008 at 12:16 pm
Convert the NOT IN statement to a LEFT JOIN and only select values where the key from the second table are NOT NULL. That's much more likely to make good use of existing indexes.
What does your execution plan look like?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 24, 2008 at 12:32 pm
In 2005 there is a new operator called EXCEPT which is equavilent to TABLE A column not in TABLE b column.
SELECT a.User_id as user_id
,A.update_date as create_date
FROM accounting.dbo. A JOIN
(SELECT user_key AS User_id
FROM accounting.dbo.
EXCEPT
SELECT DISTINCT CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) as User_id
FROM Prod.dbo.return_vendors) B ON (a.user_id = b.user_id)
WHERE a.update_date IS NOT NULL
AND a.update_date <= GETDATE() - 1
June 24, 2008 at 12:35 pm
You don't have to use Distinct in the select after Except. Just adds more processing, doesn't actually change the results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 24, 2008 at 12:41 pm
You don't need the DISTINCT for an IN(..), and you can make the update_date test's more sarg-able:
SELECT user_key AS User_id, update_date AS createDate
FROM accounting.dbo.user
WHERE user_key NOT IN (
SELECT CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) AS user_key
FROM Prod.dbo.return_vendors
)
AND NOT update_date is NULL
And update_date <> ''
AND update_date <= GETDATE() - 1
I'm not so sure that the LEFT JOIN is a good idea.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 24, 2008 at 1:53 pm
Ummm, dates cannot be blank... so doing this...
AND (ISNULL(update_date, '') <> ''
... is a total waste of CPU clock cycles. Further, putting ISNULL on the date column will usually (not always) interfere with using an index correctly... it normally forces an index scan even though it could do an index seek...
IS NULL can be a bit slow, sometimes...
So, convert the statement above to...
AND UpdDate_Date > 0 --goes all the way back to 1900-01-01
The real key is that you don't need that at all be cause you have this...
update_date <= GETDATE() - 1)
Because you can't compare anything to nulls, that will make it so nulls are not included, anyway.
--Jeff Moden
June 24, 2008 at 2:13 pm
Thank you everyone for your help. I made some modifcation based on your suggestions and am having much sucess.
Thanks again.
June 24, 2008 at 3:43 pm
Try this...
SELECT
user_key AS User_id,
update_date AS createDate
FROM accounting.dbo.user
WHERE user_key IN
(
SELECT
DISTINCT CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) AS user_key
FROM Prod.dbo.return_vendors)
AND update_date is null or update_date > GETDATE() - 1
)
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 25, 2008 at 2:41 am
I have another suggestion: create a computed persisted column for CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) and index this column. Make the left join on this and exclude the the useless isnull() part as suggested and you will have fast enough query
June 25, 2008 at 1:33 pm
SELECT
user_key AS User_id,
update_date AS createDate
FROM accounting.dbo.user with(nolock)
WHERE user_key NOT IN (SELECT DISTINCT CONVERT(INT, RIGHT(vendor, LEN(vendor)-1)) AS user_key
FROM Prod.dbo.return_vendors with(nolock))
and (ISNULL(update_date, '') <> ''
AND update_date <= GETDATE() - 1)
June 25, 2008 at 11:49 pm
with(nolock) on select statement are useless
I suppose you already solve the problem but... for the art 🙂 you can try this script and post the select execution time after
alter table Prod.dbo.return_vendors
add user_key as convert(int, right(vendor, len(vendor)-1)) persisted
go
create index ix_return_vendors_user_key on Prod.dbo.return_vendors (user_key)
go
create index ix_user_update_date on accounting.dbo. (update_date,user_key)
go
select u.user_key AS User_id,
u.update_date AS createDate
from accounting.dbo. u
left outer join Prod.dbo.return_vendors rv on u.user_key = rv.user_key
where (u.update_date <= getdate() - 1) and (rv.user_key is null)
go
June 26, 2008 at 5:32 am
Catalin Enescu (6/25/2008)
with(nolock) on select statement are useless
Alright... you need to tell us why you say that... useless for what?
--Jeff Moden
June 26, 2008 at 8:05 am
Jeff Moden (6/26/2008)
Alright... you need to tell us why you say that... useless for what?
nolock hint on select statement
according to BOL :"This is the default for SELECT operations"
June 26, 2008 at 9:11 am
Catalin Enescu (6/26/2008)
Jeff Moden (6/26/2008)
Alright... you need to tell us why you say that... useless for what?nolock hint on select statement
according to BOL :"This is the default for SELECT operations"
Could you point out that reference in BOL? I am pretty familiar with BOL, but I have never seen that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 26, 2008 at 11:31 am
rbarryyoung (6/26/2008)
Catalin Enescu (6/26/2008)
Jeff Moden (6/26/2008)
Alright... you need to tell us why you say that... useless for what?nolock hint on select statement
according to BOL :"This is the default for SELECT operations"
Could you point out that reference in BOL? I am pretty familiar with BOL, but I have never seen that.
sorry, my mistake the nolock hint it's default only for Compact Edition:
http://msdn.microsoft.com/en-us/library/ms172398.aspx
I did not find a clear statement about default table hint on other editions, I suppose readcommited it's default
@jeff Moden
nolock - could be useful on the RubyRed's solution, I don't know if will count on 2 index joins, I'll will test this until the end of the week
You - both - are more experts than me 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply