February 14, 2009 at 2:44 pm
Hi,
I am using the following statement to insert to my new table called Employee but it took too long. Processing time is roughly 3 hours for 600,000 Records. Anyway can we make it faster?
Thanks.
INSERT INTO Employee(Name, CardNo)
SELECT (Name, CardNo)
From Emp_Detail T
LEFT JOIN Stuff_Detail I
ON T.CardNo = I.CardNo COllate database_Default
WHERE I.CardNo IS NULL
Regards,
Leo
February 15, 2009 at 12:19 am
[font="Verdana"]First execute the select statement separately with execution plan on. Find out where query is taking too much time to execute. Fix it and then try to insert.
Mahesh
[/font]
MH-09-AM-8694
February 16, 2009 at 6:05 am
If you can, post an actual execution plan for the query. Also, if you can, post the structure of the tables, especially any indexes on them. Too few, too many, or badly constructed indexes could be the cause of the slowdown. You may also have out of date statistics or index fragmentation. I'd check both those too.
----------------------------------------------------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
February 16, 2009 at 8:43 am
Maybe one of you can help me to understand how this would be an okay comparison?
ON T.CardNo = I.CardNo COllate database_Default
WHERE I.CardNo IS NULL
If I.CardNo is Null then how could it equal T.CardNo?? I would think the join is pretty much useless - no?
Toni
February 16, 2009 at 9:08 am
It's a LEFT JOIN.
Unmatched records have NULLs in all the fields from the unmatched table. See http://msdn.microsoft.com/en-us/library/ms177634(SQL.90).aspx
Derek
February 16, 2009 at 9:20 am
Leo (2/14/2009)
INSERT INTO Employee(Name, CardNo)SELECT (Name, CardNo)
From Emp_Detail T
LEFT JOIN Stuff_Detail I
ON T.CardNo = I.CardNo COllate database_Default
WHERE I.CardNo IS NULL
This is an invalid query because:
1. "SELECT (Name, CardNo)" is not valid syntax. Should be "Select Name, CardNo".
2. The above is still incorrect because "CardNo" is ambiguous (Do you mean T.CardNo or I.CardNo?)
It's also not clear what the purpose of specifying the collation is for an equijoin.
Can you provide more information about the table structure and the actual query?
Derek
February 16, 2009 at 10:06 am
*** EDITED By Toni/// removed as I had picked the wrong column to try the null where clause with
February 17, 2009 at 10:10 pm
INSERT INTO Employee (Name, CardNo)
SELECT T.Name, T.CardNo
FROM Emp_Detail T
WHERE NOT EXISTS (SELECT TOP 1 1
FROM Stuff_Detail I
WHERE T.CardNo = I.CardNo
)
Since you are not selecting data from the Stuff_Detail table, it's better to use a WHERE EXISTS or WHERE NOT EXISTS clause.
When I.CardNo is unique, I'm not sure if there is a big difference from SELECT TOP 1 1 and SELECT 1, but if I.CardNo is not unique, it runs slightly faster.
I'm supposed to do it this way at work, and it's taken me a while to easily do it, but my first instinct is still to do the LEFT JOIN.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply