April 18, 2005 at 10:56 am
Hi everyone,
I have a query that takes about 13 minutes to execute and I was wondering if I could optimize this query so it's faster.
Here it is now:
INSERT Contacts(compID, first, last, phone, email)
SELECT compID, fname, lname, phone, email
FROM import_contact
WHERE (SELECT COUNT(C.contactID)
FROM Contacts C, import_contact ic
WHERE c.compID = ic.compID
AND c.first = ic.fname
AND c.last = ic.lname) = 0
I'm trying to insert a new record to a contacts table from an import table if it doesn't already exist.
(The contacts table has about 500,000 records)
Any suggestions are greatly appreciated.
Sandra
April 18, 2005 at 11:22 am
INSERT dbo.Contacts(compID, first, last, phone, email)
SELECT compID, fname, lname, phone, email
FROM dbo.import_contact IC
WHERE not exists (Select * from dbo.Contacts C where c.compID = ic.compID AND c.first = ic.fname AND c.last = ic.lname)
April 18, 2005 at 12:03 pm
Thank you Remi,
This query runs instantly, the only problem is it gives me a different record set. It finds more records to be inserted.
It may be because my old query was wrong, but I can't see why.
I tried this query:
INSERT Contacts(compID, first, last, phone, email)
SELECT compID, fname, lname, phone, email
FROM import_contact IC
WHERE not exists (select C.contactID
from Contact c, import_contact ic
WHERE C.compID = ic.compID
AND c.first = ic.fname
AND c.last = ic.lname)
and it gives me a lower record set and it also takes much longer than your suggestion. I'm just tried to understand why, it seems like it's because it has to do a join for every record I check...
Does that make sense?
April 18, 2005 at 12:16 pm
you are not linking the exists with the outer table!
INSERT Contacts(compID, first, last, phone, email)
SELECT compID, fname, lname, phone, email
FROM import_contact IC
WHERE not exists (select C.contactID
from Contact c, import_contact ic
WHERE C.compID = ic.compID
AND c.first = ic.fname
AND c.last = ic.lname)
in that case the ic is scoped to the inner table !! you must use what Remi posted "as is" and do not introduce the second "import_contact" table
INSERT dbo.Contacts(compID, first, last, phone, email)
SELECT compID, fname, lname, phone, email
FROM dbo.import_contact IC
WHERE not exists (Select * from dbo.Contacts C where c.compID = ic.compID AND c.first = ic.fname AND c.last = ic.lname)
hth
* Noel
April 18, 2005 at 12:24 pm
Thanks guys for all your help.
I see the error of my ways now.
🙂
Sandra
April 18, 2005 at 12:33 pm
How long does it take to run the query now?
April 18, 2005 at 12:45 pm
one second! It's great!
It's a part of a SPROC that has about 10 other queries so time is everything.
Thanks again!
April 19, 2005 at 9:09 am
This version uses the SQL92 "outer join" construct. It still resolves to the same thing as whar Remy wrote.
INSERT dbo.Contacts(compID, first, last, phone, email)
SELECT ic.compID, ic.fname, ic.lname, ic.phone, ic.email
FROM dbo.import_contact ic Left outer Join dbo.Contacts c ON c.compID = ic.compID AND c.first = ic.fname AND c.last = ic.lname WHERE C.compID is null
Habib.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply