As all developers know, one of the most common bottlenecks in a web application is the database. Database optimisation is therefore a huge part of building a streamlined application capable of handling more concurrent users.
Often you might have the need to perform multiple INSERT’s against a database from a single user form submission.
Let’s say you had a form allowing a user to add their own web bookmarks (think a simple del.icio.us). To make things easier for the user they can add multiple ‘bookmarks’ at a time.
For the sake of our argument when a user submits this form we transform the data into an array of structures (but it can really be anything you can loop over) so that each bookmark row from the form is an element of the array.
Normally you might INSERT the data with something like the following:
While this works fine it certainly isn’t the best option because it sends multiple requests (INSERT’s) to the database. So if the array count is 4, MySQL will process 4 separate INSERT statements which really isn’t optimal.
Fortunately MySQL gives you a super cool way of handling these types of INSERT situations:
It’s quite simple, we have 1 <cfquery> tag, but we loop over the VALUES part of the DML statement. The best thing about this is that MySQL only processes 1 INSERT statement! That’s a huge performance increase particularly in a high traffic environment.
The only real thing to note is that each VALUES block must be in parenthesis (as normal) and must be comma separated (except the last block). To handle this we just look to see if we are at the final array index. If not then add a comma.
The more I use MySQL the more I’m impressed with it. I’m not sure if you can do this in other database engines though.
have you benchmarked this?, i would be curious to know the overhead for the network calls versus the single statement.
usually the db write is the slowest part, including updating the indexes.
wrapping a transaction around the 4 inserts would make difference smaller
zac spitzer
April 3rd, 2008
Cool tip. Thanks for sharing that. I didnt know that could be done. Its a pity we cant do that in MS SQLServer.
Anuj Gakhar
April 3rd, 2008
Can you do a bulk update if the foreign IDs are the same?
David Buhler
April 3rd, 2008
What I do is I put the that generate the insert statements INSIDE the , then there will only be only 1 cfquery / connection.
Henry
April 3rd, 2008
@Zac - I haven’t benchmarked this but I may over the weekend. Agreed re: indexes.
Not sure how a transaction would help with performance, a transaction is an “all or nothing” type of operation (with the option to rollback to a particular point of course)
@Anuj - Yeah I didn’t think you could do this with MSSQL! Oh well.
Michael Sharman
April 3rd, 2008
@David - I assume you mean BULK INSERT?
My understanding is that you use that for importing data from files on the hard disk (BULK INSERT for MSSQL and LOAD DATA INFILE for MySQL).
Michael Sharman
April 3rd, 2008
@Henry - Sorry, blog cut off your comment. Can you retry?
Michael Sharman
April 3rd, 2008
Transactions actually make a huge difference. Each insert statement actually means stop and write out to disk. Thus four inserts means stop and write out to disk four times. MySQL might be a bit different depeneding on the table type, but this is definately true for Oracle.
Wrapping inserts up in a transaction means that the db will then only write out to disk once.
Whenever you are inserting or updating a lot of data, wrapping the queries up in a transaction will speed things up a lot, just watch your hard disk light with and without a transaction.
http://zacster.blogspot.com/2007/10/faster-inserts-and-updates-with.html
PS: any chance of a larger comment field?
zac spitzer
April 4th, 2008
@Zac - ah that makes sense! Great tip and thanks for clearing that up for me.
Heh, sorry about the textarea size. I need to upgrade my ‘theme’ soon so I’ll be doing it then.
Michael Sharman
April 4th, 2008
Do you happen to know if this works with MySQL 4 and 5?
Tony Garcia
April 4th, 2008
@Tony - Sorry I should have mentioned that, it works for both.
http://dev.mysql.com/doc/refman/5.0/en/insert.html
http://dev.mysql.com/doc/refman/4.1/en/insert.html
Michael Sharman
April 4th, 2008
@Zac - I just remembered that the MyISAM storage engine for MySQL (which is what I mainly use) doesn’t support transactions, so that wouldn’t work unfortunately.
Michael Sharman
April 21st, 2008