trick for loops in database

Avoid Loops when Possible

Loops can be expensive especially the more operations that you run in that loop. For instance take the following scenario where you need to retrieve a set of integer values from the browser that correspond with a particular record in that database and then output the row.

Poor Performance Looping Example
if (isset($_GET['ids'])) {
    foreach($_GET['ids'] as $id) {
        $rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . (int) $id);
        $row = mysql_fetch_assoc($rs);
        print_r($row);
    }
}
High Performance Looping Example
if (isset($_GET['ids'])) {
    $ids = array_map('intval', $_GET['ids']);
    $ids = implode(',', $ids);
    $rs = mysql_query('SELECT * FROM my_table WHERE my_id IN (' . $ids . ')');
    while($row = mysql_fetch_assoc($rs)) {
        print_r($row);
    }
}

The examples above simulate a common mistake you see often in code. The first example shows us executing the same query per every ID where in the second we only use one query. This cuts down considerably in the time it takes. To generate the below I passed 15 id’s to hit a single table that had only 40 records (6 id’s did not actually exist).

Number of IDS: 15
The Bad Way: 0.0044221878051758 seconds
The Good Way: 0.0011670589447021 seconds

Add on quite a bit more id values and a few joins in there and you have yourself a bottleneck building up.

Read it at performance tuning blog post here.

2 comentarios en “trick for loops in database

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s