I often “bundle” my mysql inserts – instead of doing one at a time, I’ll do 5,000 for example. (Or more usually, do a user-specified number at a time). The only trick is remembering to do any leftovers at the end. Here’s the code:<\/p>\n
my $count=0;\r\nmy $max_inserts = 15000;\r\nmy @inserts=();\r\nmy $printed_example=0; #will be 1 when we print an example of the insert;\r\nmy $insert_sql = \"insert into some_table (col1,col2,col,etc) values \";\r\nwhile(my @things=$sth->fetchrow_array())\r\n{\r\n $count++;\r\n my $bigline=\"('\".join(\"','\",@things).\"')\";\r\n push(@inserts,$bigline);\r\n #flush the @inserts after 1k (or whatever) rows get pushed onto it\r\n if(scalar(@inserts)>$max_inserts)\r\n {\r\n $dbh->do($insert_sql.join(\",\",@inserts))\r\n or die \"Couldn't insert: $!\\n\";\r\n @inserts=(); #empty back out the array.\r\n }\r\n}\r\n#don't forget to insert any leftovers :)\r\n$dbh->do($insert_sql.join(\",\",@inserts)) if scalar(@inserts);\r\n@inserts=();<\/pre>\nWhat it’s doing: Getting a bunch of rows from a database (or whatever). While it’s looping through the giant list it gets, it’s pushing part of an insert statement onto an array. Basically, the back half of an insert statement. Once that array gets big enough, it “flushes” it into the database and empties it out again. Finally, after it’s done looping through the giant list, it “flushes” any left over. I find this is faster than preparing and executing one at a time by a factor of 10 to 50. Must do some number crunching soon…<\/p>\n","protected":false},"excerpt":{"rendered":"
I often “bundle” my mysql inserts – instead of doing one at a time, I’ll do 5,000 for example. (Or more usually, do a user-specified number at a time). The only trick is remembering to do any leftovers at the end. Here’s the code: my $count=0; my $max_inserts = 15000; my @inserts=(); my $printed_example=0; #will […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15],"tags":[10],"_links":{"self":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/50"}],"collection":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=50"}],"version-history":[{"count":6,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/50\/revisions"}],"predecessor-version":[{"id":88,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=\/wp\/v2\/posts\/50\/revisions\/88"}],"wp:attachment":[{"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.imaginarybillboards.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}