Codeigniter's insert_batch() function performance and limitations?

Question

I’m writing an application that sends sms through an sms gateway and I’m keeping (obviously) a copy of the message on the database. Till now, for prototyping purposes, I was inserting each message into the database one by one with Codeigniter's Active Record class, but for bulk sending messages I will use insert_batch() to fasten the whole process. I’m talking about 1000-5000, and maybe more, sms (inserts) per user request at one time.

My question is, is insert_batch() going to be sufficient, fast enough and error free to such big amount of inserts? Or is it being used for less inserts per batch job. Is it safer or faster to make a custom mysql query with multiple rows in one INSERT? Are there any limitations on the amount of rows that can be inserted with insert_batch()?

Has anyone else used it for big amount of inserts before? If yes what's your experience?

Answer 1

After one week of insert_batch() testing and handling about 1.000.000 records with insert_batch() I came to the following problem as described in another SO question : Codeigniter's insert_batch() with thousands of inserts has missing records

So in my personal experience insert_batch() is not reliable for massive insert of 10.000+ rows as It sometimes isn't inserting everything that can lead to db inconsistencies. For the performance I had no problems and I'm so far satisfied. 10.000 rows at around 100 secs in an old PC (p4 w/ 1GB RAM) with CentOS installed.

Answer 2

Insert_batch is good as you just use 1 query to insert information to the database.

That's far better than looping a series of statements.

As you know, each statement is connect -> query -> close.

I suggested you would want to connect to your db once than connecting to it repeatedly.