SQL queries in a loop

Question

The Google code suggests that you should AVOID SQL queries in a loop. The reason is that multiple return trips to the database significantly slow down your scripts. An example of the request they give is this.

$userData = array(); foreach ($userList as $user) { $userData[] = '("'.$user['first_name'].'", "'.$user['last_name'].'")'; } $query = 'INSERT INTO users (first_name,last_name) VALUES'.implode(',',$userData); mysql_query($query); 

My questions ... 1. How important is it to get your request out of the loop, and can it always be avoided? 2. How can you implement a SELECT statement using the same logic.

i.e. Say I have this query.

 $index=0; while ($index < count($id)) { $result[] = mysql_query("SELECT * FROM tblInfo WHERE site_id = '".$id[$index]."' "); $index++; } 

How can this SELECT statement be executed outside the loop? I have a large number of SELECT statements that are much more complex than that. Therefore, if it is deemed necessary, I would like to get these requests from the loops. If someone there agrees with google, could you please post some sample code.

Any answer would be greatly appreciated.

Answer 1

You can use the MySQL IN statement with a list of identifiers.

 SELECT * FROM table WHERE id IN (1,4,6,8,5,6) 

It can handle even very long lists of thousands of identifiers (certainly better than thousands of SELECT s). But in this case, you should also consider the design of your application. If you need to use IN with thousands of identifiers on every page load, something is very wrong with your design.

INSERT can also be condensed into a single query, see the documentation .

Typically, most queries in loops are usually rewritten as subqueries . however, in this case you need to choose between performance and readability / maintainability. Subqueries are usually hell to understand and optimize / debug.

Answer 2

You may find this article interesting: http://blog.fatalmind.com/2009/12/22/latency-security-vs-performance/

Answer 3

How important it is to avoid cost back and forth depends on a few things.

First, how many rounds do you make? If you do 3 or 4, you can probably ignore the advice if listening to it would be painful.

Secondly, how much will it cost you to travel to your setting? If switching back to db takes 100 ms, this should be avoided much more seriously than if it takes only 2 ms.

Thirdly, how time sensitive is the process that should fulfill requests? If you make the user wait, you really should pay attention to it - users hate to wait! If you use an Ajax process that starts off-screen and does some work, this may be less important (although you still have to keep track of timeouts, maybe).

Basically, Google’s advice is good in that wasted time is wasted. However, depending on your particular case, wasted time can be more or less serious for you, your system, and your users.