February 6, 2008 at 3:55 am
Hi all,
I was wondering if someone might be able to shed some light on this, cause i'm confused.
My goal is to populate a table (A), table A has a primary key on an auto id column, but no other indexes and is made up 6 columns, couple of ints, some small varchars and a datetime, nothing special.
I have a select which runs on 1 table (B) and left joins 3 others (C,D,E) to return approx 12 million records, running the select takes approx 7 minutes, if i add the insert syntax and attempt to insert into tabla A then the process takes 3 hours. I wasnt expecting that.
However decided to change the insert statement top populate a temp table (#) this takes 15 minutes, then to transfer the data from the temp table to table A takes 2 minutes. But methods use the same select, the only difference is the temp table.
I'm confused as to why the difference is so big, the only thing i can think of is the use of the temp db.
Any idea's?
February 6, 2008 at 4:46 am
The primary key will use a clustered index, your temptable will most likely be only a heap.
Also, are you the only one accessing the table you are inserting data into? Also, you may have a disk IO bottleneck. Is your tempdb on a different disk?
Andras
February 6, 2008 at 5:02 am
Hi thanks for the response.
1. Tempdb is located on a different physical disk, which i think like you say maybe the reason for the difference.
2. I am the only person who should be using the table. I cant see any other users, locking etc.
Thanks,
Nic
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply