multiple database queryception performance

Question

Imagine there's a SQLite database with two tables, "users" and "ranks", one holds user names and their rank as a number (and the userid), the other holds the same rank numbers and the name of the rank. The goal is to output a html table with the name of the rank and the user names (and their userid).

<?php
$db1 = new PDO('sqlite:database');
$db2 = new SQLite3('database');
$result = $db1->query("SELECT * FROM users ORDER BY rank DESC");

echo '<table><tr><td>rank</td><td>username</td><td>id</td></tr>';
$data = "";
foreach($result as $row){
    $data .= 
    '<tr><td>'.$db2->querySingle("SELECT rankname FROM ranks WHERE ranknumber =".$row['rank']).'</td>'.
    '<td>'.$row['name'].'</td>'.
    '<td>'.$row['id'].'</td></tr>';
}
echo $data;
echo "</table>";
?>

this code works perfectly fine on a small scale, the question is of its performance once the database is highly populated. Essentially it's making a new request to the database for every outputted row of the first query, right? I added the use of $data instead of echoing every single row one by one solely in case it might reduce requests somewhere along the road somehow, but I'm not sure if it'll even make any positive difference. I'm too inexperienced with database handling and php to know how much strain this code would put on the server and/or network compared to simply having 2 queries side by side, will my code essentially perform hundreds if not thousands of database requests and how would that affect performance? Additionally, if it's bad, what can I do about it? Is there any different way to achieve the same results without performance issues?

Even more importantly, what if I also replace the user name with a displayname from an entirely different non-SQLite (MySQL) database altogether? 201 queries to display 100 users, does that mean massively multiplied resource usage?

Sounds like a job for "join". If you're not already familiar with SQL joins, here is a good tutorial: tutorialspoint.com/sqlite/sqlite_using_joins.htm. As far as "efficiency": you generally want to make as few queries is possible. Hence the suggestion to consider using a join. — paulsm4
– paulsm4, Commented Oct 20, 2014 at 2:50
@paulsm4 join did the trick, works like a charm almost as if all my data was all in one table, thanks! — KittenCodings
– KittenCodings, Commented Oct 20, 2014 at 7:07

Mureinik · Accepted Answer · 2014-10-20 06:08:21Z

1

As a general rule of thumb, issuing a query is a costly operation - it involves a lot of work on the database side (parsing, syntax checking, permission checking, constructing an execution plan, disk I/O, network traffic to and from the DB, etc).

Of course no rule is 100% bullet proof, and you should always benchmark your particular usecase, but in most cases, performing a single "large" query will be considerably better than performing N "small" ones. In your usecase, you can use the join syntax to get all the results you need in a single query:

SELECT   users.*, ranks.rankname
FROM     users 
JOIN     ranks ON users.rank = ranks.ranknumber
ORDER BY users.rank DESC

answered Oct 20, 2014 at 6:08

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KittenCodings Over a year ago

Wow, SQL queries being such an old and easy to use functionality on the surface I didn't think it was actually such a heavy action to perform... then my "queryception" performing a query for every result of another query would be a really bad idea, this is what I was afraid of and what answers the question, thanks. Also, JOIN did the trick for me for the two tables in the same database and I'm now trying to figure out how to attach the other database for my query, many thanks, you made my day better and I hope you'll have a good one too

Collectives™ on Stack Overflow

multiple database queryception performance

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related