256 inserts in a for() loop - is that ok ?

steamPunk

I need to insert 256 records into a table - the query will look like this :

INSERT INTO `panierdef_001.produits` (`pr_id`, `admin_id`, `pr_nom`, `pr_prix`, `fam_id`, `pr_desc`, `pr_orig`, `pr_ref`, `pr_min`, `unite_id`, `pr_vol`, `pr_coeur`, `pr_offre`, `pr_image`, `pr_online`, `pr_rank`) VALUES ('', 13, 'Clementines corses', '3.95', '10', 'Riche en vitamine C, la Clémentine de Corse contribue à doper l\'organisme en hiver (41 mg aux 100 g, une orange apportant 50 mg au 100 g). La Clémentine de Corse est également riche en carotènes, en provitamine A, favorable à la bonne santé de la peau.', 'Elle doit cette générosité aux aléas des découvertes. Apparu en 1929, cet agrume est issu d\'un croisement de hasard entre un bigaradier (oranger amer) et un mandarinier.', '141', '0' '4', '', '', '', 'paniers-bio-nimes-70556df662c6bc8f.jpg', '0', '18')

if i do this from in a for() loop is it likely to fail due to too many queries in 1 go or something ? is there a better way ?

thanks

kakki

perhaps the queries will take longer than the timeout setting in php.ini ???

to fix that for sure, you may create a shellscript for the insert-statements and
use the mysql-shell utility like this

mysql --user=XXX < output_file_from_php-shellscript.txt

laserlight

if i do this from in a for() loop is it likely to fail due to too many queries in 1 go or something ? is there a better way ?

Should be okay if you wrap them in a transaction.

NogDog

You can insert multiple rows via one query. So what you can do is use your loop to create an array of value expressions, then implode() them into your query:

$values = array();
for('<whatever your loop is>')
{
   $values[] = "('$val_1', '$val_2', '...', '$val_n')";
}
$sql = "INSERT INTO table_name (`col_1`, `col_2`, `...`, `col_n`) VALUES " .
       implode(', ', $values);

The one caveat would be that you need to be aware of your database configuration limitations on query length.

laserlight

You can insert multiple rows via one query. So what you can do is use your loop to create an array of value expressions, then implode() them into your query:

I was thinking of this too, but it seems not worth the trouble. Wrapping the inserts in a single transaction seem much easier.

NogDog

It might be interesting to see if there is any appreciable performance difference between those two options, or if it's small enough to just go with whichever makes more sense from a readability/maintainability viewpoint. Personally, I'm too lazy right now to try. 🙂

laserlight

Another thing: is the multiple inserts in a single statement standard SQL?

NogDog

My quick research (i.e.: Wikipedia) indicates that the multiple values format has been standard since ANSI SQL92 (1992), so should be available in any reasonably current DBMS.

laserlight

My quick research (i.e.: Wikipedia) indicates that the multiple values format has been standard since ANSI SQL92 (1992), so should be available in any reasonably current DBMS.

That's good. Speed and statement length issues aside, I think that the choice might depend on the database API.

With prepared statements, one can begin the transaction, prepare the statement once and bind on each iteration, and finally commit the transaction. This makes for good reuse of the prepared statement, and also includes the escaping.

Without prepared statements, one has to build the statement anyway, so building it in a loop is a good idea.

Lars_Berg

My quick research (i.e.: Wikipedia) indicates that the multiple values format has been standard since ANSI SQL92 (1992)

That's incorrect. It was introduced in SQL99. To my knowledge it is not that common with support for this construction.

NogDog

Lars Berg wrote:
That's incorrect. It was introduced in SQL99. To my knowledge it is not that common with support for this construction.

Well, according to http://www.contrib.andrew.cmu.edu/~shadow/sql/sql2bnf.aug92.txt

[b]<insert statement>[/b] ::=
    INSERT INTO <table name>
      [b]<insert columns and source>[/b]

[b]<insert columns and source>[/b] ::=
      [ <left paren> <insert column list> <right paren> ]
            <query expression>
    | DEFAULT VALUES

[b]<query expression>[/b] ::=
      [b]<non-join query expression>[/b]
    | <joined table>

[b]<non-join query expression>[/b] ::=
      <non-join query term>
    | <query expression> UNION  [ ALL ]
          [ <corresponding spec> ] <query term>
    | <query expression> EXCEPT [ ALL ]
          [ <corresponding spec> ] <query term>

[b]<non-join query term>[/b] ::=
      [b]<non-join query primary>[/b]
    | <query term> INTERSECT [ ALL ]
          [ <corresponding spec> ] <query primary>

[b]<non-join query primary>[/b] ::=
      [b]<simple table>[/b]
    | <left paren> <non-join query expression> <right paren>

[b]<simple table>[/b] ::=
      <query specification>
    | [b]<table value constructor>[/b]
    | <explicit table>

[b]<table value constructor>[/b] ::=
    VALUES <table value constructor list>

[b]<table value constructor list>[/b] ::=
    <row value constructor> [color=red][b][ { <comma> <row value constructor> }... ][/b][/color]

[b]<row value constructor>[/b] ::=
       <row value constructor element>
    | [b]<left paren> <row value constructor list> <right paren>[/b]
    | <row subquery>

[b]<row value constructor list>[/b] ::=
    <row value constructor element>
        [ { <comma> <row value constructor element> }... ]

Of course that still does not have any bearing on which DBMS's support it, but any that claim to be fully ANSI SQL92-compliant should.

Lars_Berg

You are correct, it was present in SQL 92.

NogDog

Lars Berg wrote:
You are correct, it was present in SQL 92.

It was good practice, though: I hadn't made myself read through one of those BNF notations in a long time. (And now I remember why!) 🙂

rulian

yes, assuming you are using mysql, mysql was designed to deal with SETS of data, therefore it is much more practical and economical to set up your query statement to contain blocks of inserts rather then runing each insert by itself. Performance difference is huge gain in sets. I once inserted 2 million rows in a database in record time using blocks of 5k rather then 2 million queries as I originally tried to do it.
i upped my maxtimeout in php to 2 hours on my dev server and 2 hours later it still had not finished. But with the sets it did it in what I recall was about a minute or so

laserlight

Performance difference is huge gain in sets. I once inserted 2 million rows in a database in record time using blocks of 5k rather then 2 million queries as I originally tried to do it.

Did you wrap the inserts in a single transaction? Or at least every 5K insertions in a transaction?

steamPunk

this is interesting reading - i don't know what a transaction is but i'm going to find out

after reading these replies i remembered that when i do a dump of my db using PhpMyAdmin it makes blocks of multiple inserts in one query - so i guess i should have thought of that before posting my question

but thanks anywway 🙂

hungarfen

steamPunk wrote:
if i do this from in a for() loop is it likely to fail due to too many queries in 1 go or something ? is there a better way ?

I understand that you're asking this question because of future query performance problems.

I can tell you just this : While inserting many or so many data into a table mySQL optimizes the queries according to their indexes, table schema and how many tables will you use.

In the past i've done so many stress tests on mySQL. This means i've inserted so so many data to tables in a row. I can clearly see that if you're going to insert a data to only one table the main problem is not the count of your queries but the usage of table indexes. If you're not using so many indexes or all of your indexes are proper (this means you're indexing only the needed columns) to insert so many data will not result as a growing query timing or timeouts.

So i can only suggest you to use proper indexes. I cannot suggest you transaction or other abilities that RBDMS' have. Why because only inserting a data to a table is nothing for databases. Thus if you're preparing that table for presentation and not work on it with SELECT queries before presentation you have a choice to not use indexes before presentation.

( Tip : Indexes become more important on SELECT before INSERT. If you're controlling data (if it's over there) by using SELECT before inserting it i can clearly say that whole selected columns must be indexed. This will increase performance while working with many many data )

laserlight

So i can only suggest you to use proper indexes. I cannot suggest you transaction or other abilities that RBDMS' have. Why because only inserting a data to a table is nothing for databases.

I disagree. Obviously one should index columns appropriately as indices increase the cost of inserts while reducing the cost of selects, but things like transactions play a part. To say that "only inserting a data to a table is nothing for databases" thus you "cannot suggest you transaction" ignores things like the unnecessarily opening and writing of journal files that can be avoided by the use of a single transaction - not to mention the gain in atomicity.