Tell me more ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a big table with 1,400,000 rows, and I need to insert 3000 rows daily in it.

When I insert 3000 rows daily should

  • I first insert to a temporary table than dump that temporary table to the main table.
  • insert directly to the big table.

Which approach is fast and why?

share|improve this question
1  
I changed "14 lakhs" to "1,400,000" as Wikipedia tells me that a lakh is one hundred thousand. – ChrisF♦ Mar 28 '11 at 9:50
2  
There's no standard answer. You have the numbers, why don't you actually try both and profile it to see which is faster? – faif Mar 28 '11 at 10:36
Where are the 3000 rows coming from? Are there potential data integrity issues? – JeffO Mar 28 '11 at 13:08
1  
Do the 3000 inserts need to be atomic? – Chuck Stephanski Mar 28 '11 at 16:07

4 Answers

up vote 5 down vote accepted

  1. Are the 3000 rows entered evenly throughout the day? If so, that's a row inserted every 29 seconds -- so inserting data as it comes by is hardly anything to worry about.

  2. You'll only benefit creating a temporary table if that table is in MEMORY and then at the end of the day dump that to the main table.

Edit: in reference to the comment "the data 3000 rows is inserted at once , then what to do ?"

In which case just insert straight into the main table. Adding to another table then dumping that table is just an overhead and completley pointless. Look into LOAD DATA if your host supports it, or just do one big INSERT with LOW_PRIORITY (unrecommend in a high-enviroment server) or DELAYED (recommended). See: http://dev.mysql.com/doc/refman/5.5/en/insert.html

share|improve this answer
the data 3000 rows is inserted at once , then what to do ? – WebDev Mar 28 '11 at 10:21
is delayed insert is one big insert or single insert each time ? – WebDev Mar 28 '11 at 11:05
Well it can be one big insert in the format VALUES ('row1'),('row2') which will be the most efficient way of doing it, rather than INSERT, INSERT, INSERT ;-) -- you should read the link I gave. – Gary Green Mar 28 '11 at 11:17
okay for using load data what is required ? – WebDev Mar 28 '11 at 11:22
tinyurl.com/46pqcjw – Gary Green Mar 28 '11 at 11:45
show 1 more comment
up vote 1 down vote

the first option is batching the inserts this is faster if the database's indexes support batched inserts but if they don't or if there are no indexes it provides no benefit

and if you need the table to be always up-to-date and the rows are inserted over time this would be a bad idea but if you insert 3000 rows all at once like this it wouldn't

share|improve this answer
what you want to say ? – WebDev Mar 28 '11 at 10:19
@Rahul Mehta i guess you can't read conditionals, what i mean is: if the table has no indexes or if the indexes don't support batched inserts, batching doesn't help. – Dan D. Mar 28 '11 at 11:28
up vote 1 down vote

Generally speaking, it seems more logical to do the regular insert rather than creating temporary table and then at some point in time dump to the main table.

If we're talking about the actual temporary table that MySQL creates with CREATE TEMPORARY TABLE statement, then you might run into various issues when you actually dump the data for permanent storage - since temporary tables are session-based (per connected user), then you might have issues if you experience disconnection during your session so you'll lose data, hence the integrity will be compromised.

You also didn't mention what storage engine you're using and why you're even considering creating a batch job for dumping the data to the main table. Without more insight into what's the actual problem, inserting data directly to the "main" table seems like a better choice.

share|improve this answer
no i am not talking about actual temporary table , and storage engine i am using is myisam , i need to create the batch for transfereing summary data to the table – WebDev Mar 28 '11 at 10:36
MyISAM is fast with insert operations, 3000 inserts a day shouldn't take so much time that it'd be required for you to do a batch insert job. Gary Green has suggested LOAD DATA type of insert and MySQL documentation says it's usually 20 times faster than regular INSERT statement so that might be the best approach if you want to do a batch insert at high speed - more info at dev.mysql.com/doc/refman/5.1/en/insert-speed.html – Michael J.V. Mar 28 '11 at 10:55
up vote 1 down vote

When I stage large amounts of data I usually send them to a staging table first if they need clean up or data validation. I can't think of any files I've ever gotten that didn't need that prior to going into my database. If you don't plan to adjust the data in anyway, then you can dump straight to the table unless it is so many records you find you need to process them in batches to avoid blocking.

share|improve this answer
+1, I wouldn't want to do this without a layer of indirection in the middle to clean things up. – Wyatt Barnett Mar 28 '11 at 19:11
We find it not only useful for the cleaning (which we can do in SQl Server SSIS without staging tables) but for researching data import issues because we load all files to two tables, one for the raw data and one that is cleaned. Then we can see if the problem is in their data or our clean up. Makes maintenance a lot easier. Of course we load hundreds of huge files every week (many daily). Not such a big deal if you don't need to research issues with several million record import files. – HLGEM Mar 28 '11 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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