Sql Queries in a foreach slows down the page

Question

I have a problem where I get an array of words and I have to insert every one of them in DB via a foreach, but the whole thing is running too slow.

foreach($words as $word) {
    // even more checks
    if(strlen($word) < 3) continue;
    $word = $this->db->escapeString(strtolower($word));

    // word not found?
    $result = $this->db->querySingle("SELECT id FROM words WHERE word = '{$word}'");
    if(!$result) {
        $this->db->exec("INSERT INTO words (word) VALUES ('{$word}')");
        $this->db->exec("INSERT INTO search (reply, word) VALUES ('{$id}', '{$this->db->lastInsertRowID()}')");

    // word found
    } else $this->db->exec("INSERT INTO search (reply, word) VALUES ('{$id}', '{$result}')");
}

Basically lets say $words = array('hello', 'kitten'); Within that foreach, it will take my first word (hello) and check if it exists in the words table. If it cannot find the word, it will then insert it there and also insert it into another table with a variable $id that will not change within the foreach. If however it found the word it will insert it directly in the 2nd table. After that it will take the 2nd word (kitten) and do the same thing.

Using php5 with sqlite3

$this->db = new \SQLite3(ROOT . DS . 'db' . DS . 'app.db');

The tables in question are very light

CREATE TABLE words (id INTEGER PRIMARY KEY AUTOINCREMENT, word TEXT);

CREATE TABLE search (reply INTEGER, word INTEGER);
CREATE INDEX search_reply_idx ON search (reply);
CREATE INDEX search_word_idx ON search (word);

This works fine most of the time for 10-15 words, but if i have over 150 words it will be as slow as 8 seconds. Can i combine the queries into just one? Am i missing something?

Thanks.

Try taking out the `INSERT` statements and test the speed. You could do the `SELECT` statement first and remove any unfound words words from the original array, then use a single `INSERT` statement with all values left in the array. — mulquin, Nov 14 '13 at 00:08
If i take out all of the insert queries its blazing fast, 150 words in 100 ms (ajax load time, this function runs as a ajax request). All words are used. If a word is not found it will use 2 inserts, if it is found it will use 1 insert. Even if there are no unfound words i still need to insert them, I don't understand your suggestion. — Highstrike, Nov 14 '13 at 00:17

score 2 · Answer 1 · answered Nov 14 '13 at 01:37

This method is based on the premise that SQLite support this syntax for insertion (this syntax works in MySQL):

INSERT INTO table (column) VALUES ('value1'), ('value2'), ('value3'), ('valueN');

What you can do is use PHP to build the SQL insert string using logic from the SELECT statement, and then at the end perform two queries; One on the words table and one on the search table:

$sql_words = 'INSERT INTO words (word) VALUES ';
$sql_search = 'INSERT INTO search (reply, word) VALUES ';

$count = count($words);
$i = 0;
$last_insert_id = '';
$delim = '';

foreach ($words as $word)
{
    $result = $this->db->querySingle("SELECT id FROM words WHERE word = '{$word}'");

    if ($i < $count) {
        $delim = ', ';
    } else {
        $delim = '';
    }

    if ($result) {
        $sql_search .= "('{$result}','{$word}')".$delim;
    } else {
        $sql_words .= "('{$word}')".$delim;
        $sql_search .= "('{$result}','{$last_insert_id}')".$delim;
    }

    $last_insert_id = $result;
    $i++;
}

$this->db-exec($sql_words);
$this->db->exec($sql_search);

As for the validity of this code I am unsure, I was a bit confused where $id came from, and assumed it was the same as $result.

"with a variable $id that will not change" so the $id would stay the same regardless of the foreach, you don't have to worry about that variable. Also the SQL syntax for multiple inserts in SQLITE is NOT the same as the one in MYSQL (see here: http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database). I have figured it out in the meantime how to do this with transactions (its sort of like your answer, but better :)). Thanks for taking the time to give me a straight answer with a code attached. I will post my answer in just a minute — Highstrike, Nov 14 '13 at 02:03

Highstrike · Accepted Answer · 2013-11-14T02:14:59.743

So in order to achieve blazing fast performance some things had to be changed. Firstly, the use of transactions (sort of like mulquin's answer) and secondly I've added a unique index for the column word in the words table. This will help me skip the sql that checked if a word already existed and just ignore the insert statements from that transaction.

This is the new table which will create my unique index automatically

CREATE TABLE words (id INTEGER PRIMARY KEY AUTOINCREMENT, word TEXT UNIQUE)

And here is the modified foreach

$t1 = $t2 = null;
foreach($words as $word) {
    // even more checks
    if(strlen($word) < 3) continue;
    $word = $this->db->escapeString(strtolower($word));

    $t1 .= "INSERT OR IGNORE INTO words (word) VALUES ('{$word}');";
    $t2 .= "INSERT INTO search (reply, word) SELECT '{$id}', id FROM words WHERE word = '{$word}';";
}

// run transactions
if(!is_null($t1)) $this->db->exec("BEGIN TRANSACTION;" . $t1 . "COMMIT;");
if(!is_null($t2)) $this->db->exec("BEGIN TRANSACTION;" . $t2 . "COMMIT;");

So, when in doubt, use transactions :)

Sql Queries in a foreach slows down the page

2 Answers2