Will a clustered index speed up this query? (Inner join with a where clause) I'm inserting data into a new table in a new database, based on a select to an existing table in an other database. The existing table has some 1500 million rows, and I am inner joining a table with about 10% of that. Both existing tables, are indexed on C5, while the the new table is indexed on C2, which is an unixtime column (I am going to do aggregates over time with the new table). This is taking horrendously long time. Its been running for over 3 hours, and the new table is just over 750 mb big, I expect it to be about 50GB when its filled up. This is also about 50% of the original table.

Is it because I'm indexing the new unixtime column?

Or could it be that I've not indexed the C8 column, that I use in my inner join? If I clustered C8 with C5, I'd probably get a faster inner join?

Here is the query:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) 
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7 
FROM OldTable1 
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5 
WHERE OldTable2.C8 &gt;= 70 AND OldTable2.C8&lt;= 89;
You are totally trashing the index on NewTable.C2
Just drop / disable the index and then build / rebuild it after the insert is complete   

And you need to break this up as you are putting a massive load on the transaction log

If you want to optimize the select then just run the select alone  

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7) 
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4
     , OldTable1.C5, OldTable1.C6, OldTable1.C7 
FROM OldTable1 
JOIN OldTable2.C5 
  ON OldTable2.C5 = OldTable1.C5 
 AND OldTable2.C8 &gt;= 70 
 AND OldTable2.C8 &lt;= 89;