{"id":127,"date":"2011-03-12T13:13:12","date_gmt":"2011-03-12T13:13:12","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=127"},"modified":"2011-03-12T13:13:12","modified_gmt":"2011-03-12T13:13:12","slug":"bulk-inserts-coldfusion","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/bulk-inserts-coldfusion\/","title":{"rendered":"Bulk inserts to reduce trips to database"},"content":{"rendered":"<p>By combining multiple inserts into one <a class=\"target-blank\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/insert.html\">bulk insert<\/a> statement, you can reduce the total number of statements sent to the database, speeding up combined execution of your script.<\/p>\n<p>The below example illustrates this concept using a ColdFusion code snippet.<\/p>\n<p><code>&lt;cfquery datasource=&quot;#application.strConfig.dsn#&quot;&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; INSERT INTO mytable (field1, field2)<br \/>\n\t&nbsp;&nbsp;&nbsp; VALUES<br \/>\n\t&nbsp;&nbsp;&nbsp; &lt;cfloop list=&quot;#mylist#&quot; index=&quot;listitem&quot;&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; (<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &lt;cfqueryparam value=&quot;#field1value#&quot;&gt;,<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &lt;cfqueryparam value=&quot;#listitem#&quot;&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; )<br \/>\n\t&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &lt;cfif (listitem neq ListLast(mylist))&gt;,&lt;\/cfif&gt;<br \/>\n\t&nbsp;&nbsp;&nbsp; &lt;\/cfloop&gt;<br \/>\n\t&lt;\/cfquery&gt;<br \/>\n\t<\/code><\/p>\n<p>With each loop iteration, we check that the current list item is not the last item in the list. If it is not the last item, we place a comma after the insert, to specify that another insert is to follow.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By combining multiple inserts into one bulk insert statement, you can reduce the total number of statements sent to the database, speeding up combined execution&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[15,34,41,13],"tags":[58,57],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/127"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":2,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"predecessor-version":[{"id":129,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/127\/revisions\/129"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}