from another thread : "NEVER EVER use a query in a loop"

steamPunk

I just saw a comment "NEVER EVER use a query in a loop" in another thread and i'm now a worried man because that's exactly what I've done

I have a function that opens a text file containing a load of ecommerce orders and then puts all them all into a big array

then it loops through the array and inserts each order into the main order table whilst getting the insert_id() and using that to insert all the individual items into the order items table

this results in a LOT of queries being generated inside the loop

is this really a bad thing to do ? and what do i need to do to rectify the problem ?

thanks

laserlight

You are not querying (in the sense of select statements) in a loop, but inserting in a loop, which is fine if you wrap all the inserts in a transaction and use prepared statements. Alternatively, you construct a single SQL statement that consists of multiple insertion clauses (e.g., INSERT INTO tablename (col1, col2) VALUES ('val1', 'val2'), ('val3', 'val4')), but this does not seem appropriate for what you want to do since you are also inserting "all the individual items into the order items table".

steamPunk

At the moment my tables are MyISAM type

I found a tutorial about transactions here but it says to run the test

mysql> show variables like '%have%';

in my case the result is

"have_bdb NO"

so it would appear that i won't be able to use transactions with this db 🙁

and you're right about the multiple insertion clauses not being an option because i have to get the insert_id when each order is inserted so that i can use it to insert the order items

it looks like I'm stuck with the slow method and maybe try to break it up into several packets of orders

anyway this transaction thing looks very interesting for future developments - so thanks for that !

laserlight

it looks like I'm stuck with the slow method and maybe try to break it up into several packets of orders

You can still use prepared statements, but you will miss out on the atomicity offered by transactions.

Piranha

What I ment with that statement is what it says, that you should not use queries in a loop.

However there are exceptions. The never ever part was ment when normal users are involved or the page is run all the time. With a script that is run maybe once a day, and the person running it know that it takes some seconds, it is ok. But when users run the script maybe 10 times every second it is not ok. And, as laserlight says, it depends on what kind of query it is as well.

I hope that I managed to clarify it a bit.

steamPunk

ok i read all of that and it looks pretty interesting

but it raises another couple of questions : if the prepared statements thing is like using a normal class does this mean that each prepared statement is an instance of that class and will therefore be totally independent of the other instances ? and are the instances queued in a buffer of some kind ?

steamPunk

@ - yes that's much clearer, thanks, and i can see the logic of what you're saying

my problem is that I'm working on a thing that was only designed at the outset to handle no more than a hundred-or-so updates/inserts per hour and i found out a few days ago that it may be in batches of several thousand :mad:

and i really don't want to have to re-write the whole thing to cope with this so i'm trying to find solutions that will enable the thing to run reliably before the page times out and it all goes pear-shaped

steamPunk

btw the loop-containing-queries is in a php file that is triggered by a CRON once every hour

laserlight

if the prepared statements thing is like using a normal class does this mean that each prepared statement is an instance of that class and will therefore be totally independent of the other instances ? and are the instances queued in a buffer of some kind ?

They are not quite totally independent since they depend on the same database connection. Basically, you create a PDO object to represent the database connection. Then you use the PDO object's prepare() member function to create a new PDOStatement object. Since you have two different SQL statements, you need two different PDOStatement objects. You then bind PHP variables to each parameter in the SQL statement. On each iteration of the respective loops, you set the values of these variables and then execute the statement.

steamPunk

This page says i need php 5.0 to run PDO - and i just checked the php version of the hosting and it's 4.4.8 ...

I think I'm going to end up by upgrading the hosting

thanks for all your help