4

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 >= 70 AND OldTable2.C8<= 89;
2

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 >= 70 
 AND OldTable2.C8 <= 89;
  • Thank you for your answer. Can you please elaborate on the transaction log. Is there a best practice for inserting, regarding the size? – bjornasm Feb 25 '17 at 21:21
  • 2
    I don't know about best practice. I try and keep it under 100,000. But I also go up to 1,000,000 in some situations. – paparazzo Feb 25 '17 at 21:42
1

Yes an index on the table you are selecting on will improve query performance (for the select).

However as the table you are creating is huge the query is going to take a long time anyway. Especially as a single insert.

Personally i would be looking for a way to break the insert into blocks or batches.

As pararazzi has suggested dropping the index on the target table will improve insert performance.

  • Thank you for your answer. Can you please elaborate regarding breaking it into blocks? – bjornasm Feb 25 '17 at 21:21
  • Do some analysis on your data and find something that you can limit your select query on so that each insert is smaller. For example: WHERE OldTable2.C8 = 70. then repeat for 71, 72 etc until complete. – Sir Swears-a-lot Feb 26 '17 at 0:04
  • How big a query is is relative to the server size, resources avaliable config and what else it is doing at the time. How much RAM has the server got abd is it doing anything else while you are doing this insert? – Sir Swears-a-lot Feb 26 '17 at 0:15
  • I just noticed the tags say sqllite. I thought this was about sql server. My comments about transaction logs probably no longer apply. – Sir Swears-a-lot Feb 27 '17 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.