Im confused - T-SQL

  • 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?

  • 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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • 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