Hi,
just had a little test on a Postgres 7.1 database running on Linux, Pentium 4 with 384 Mb RAM.
I have a couple of tables in this database. One with users (950 records), one with the sessions (every time they log on) (16000 records).
I have an index on the user_id in users and in sessions.
The queries which I ran are given below, as well as the code to measure the time it took them to run.
What surprises me is that it is better not to join the tables but to work with subqueries. I would have expected the first query to be the fastest? That is always the way anybody has teached me to run queries?
Is there a logical explanation for this? Is this always true? Am I doing something wrong?
Please have a look at my code and queries ?
Or is it really important to test all posibilities of queries first ? Will the outcome of this test change in case more or less records are added to sessions?
(output is given below the code)
<?php
/*
this page should test the difference in performance between
running 1 query or 2 to achieve the same result .
*/
include($DOCUMENT_ROOT . "/vars.inc.php");
// an adaption of Tim's database abstraction layer
include($classes_dir . "database.class.php");
$db = new database("pm");
if (!isset($runcycles)) {
$runcycles = 10;
echo "<p>Chose 10 runcycles. You can change this via the url var runcycles</p>";
}
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
function calculate_runtime($sql) {
global $db;
$start = getmicrotime();
if (is_array($sql)) {
for ($t = 0; $t < count($sql); $t++) {
if (!$result = $db->query($sql[$t])) {
echo $db->error();
}
}
}
else {
if (!$result = $db->query($sql)) {
echo $db->error();
}
}
$stop = getmicrotime();
return $stop - $start;
}
$sessieid = "32875dd9e6158652cbc8d334e49b416e";
// the join statement
$query[1][sql] = "select u.cash
from (users u join sessions s on u.user_id = s.user_id)
where s.session_id = '$sessieid'";
$query[2][sql][1] = "select user_id from sessions where session_id = '$sessieid'";
$query[2][sql][1] = "select cash from users where user_id = '$user_id'";
$query[3][sql] = "select cash from users where user_id = (select user_id from sessions where session_id = '$sessieid')";
echo "<p>testing performance with $runcycles times the query</p>";
for ($i = 0; $i < $runcycles ; $i++) {
// a couple of times
for ($t=1; $t <= count($query); $t++) {
// for all queries
$new_runtime = calculate_runtime($query[$t][sql]);
$query[$t]['runtime'] += $new_runtime;
$query[$t]['last_run'] = $new_runtime;
}
}
// output runtimes
for ($t=1; $t <= count($query); $t++) {
echo "<p>Query $t runned for ";
echo $query[$t]['runtime'] . " seconds</p>";
}
testing performance with 500 times the query
Query 1 runned for 6.0219280719757 seconds
Query 2 runned for 6.3680560588837 seconds
Query 3 runned for 5.055319070816 seconds
just had a little test on a Postgres 7.1 database running on Linux, Pentium 4 with 384 Mb RAM.
I have a couple of tables in this database. One with users (950 records), one with the sessions (every time they log on) (16000 records).
I have an index on the user_id in users and in sessions.
The queries which I ran are given below, as well as the code to measure the time it took them to run.
What surprises me is that it is better not to join the tables but to work with subqueries. I would have expected the first query to be the fastest? That is always the way anybody has teached me to run queries?
Is there a logical explanation for this? Is this always true? Am I doing something wrong?
Please have a look at my code and queries ?
Or is it really important to test all posibilities of queries first ? Will the outcome of this test change in case more or less records are added to sessions?
(output is given below the code)
<?php
/*
this page should test the difference in performance between
running 1 query or 2 to achieve the same result .
*/
include($DOCUMENT_ROOT . "/vars.inc.php");
// an adaption of Tim's database abstraction layer
include($classes_dir . "database.class.php");
$db = new database("pm");
if (!isset($runcycles)) {
$runcycles = 10;
echo "<p>Chose 10 runcycles. You can change this via the url var runcycles</p>";
}
function getmicrotime(){
list($usec, $sec) = explode(" ",microtime());
return ((float)$usec + (float)$sec);
}
function calculate_runtime($sql) {
global $db;
$start = getmicrotime();
if (is_array($sql)) {
for ($t = 0; $t < count($sql); $t++) {
if (!$result = $db->query($sql[$t])) {
echo $db->error();
}
}
}
else {
if (!$result = $db->query($sql)) {
echo $db->error();
}
}
$stop = getmicrotime();
return $stop - $start;
}
$sessieid = "32875dd9e6158652cbc8d334e49b416e";
// the join statement
$query[1][sql] = "select u.cash
from (users u join sessions s on u.user_id = s.user_id)
where s.session_id = '$sessieid'";
$query[2][sql][1] = "select user_id from sessions where session_id = '$sessieid'";
$query[2][sql][1] = "select cash from users where user_id = '$user_id'";
$query[3][sql] = "select cash from users where user_id = (select user_id from sessions where session_id = '$sessieid')";
echo "<p>testing performance with $runcycles times the query</p>";
for ($i = 0; $i < $runcycles ; $i++) {
// a couple of times
for ($t=1; $t <= count($query); $t++) {
// for all queries
$new_runtime = calculate_runtime($query[$t][sql]);
$query[$t]['runtime'] += $new_runtime;
$query[$t]['last_run'] = $new_runtime;
}
}
// output runtimes
for ($t=1; $t <= count($query); $t++) {
echo "<p>Query $t runned for ";
echo $query[$t]['runtime'] . " seconds</p>";
}
testing performance with 500 times the query
Query 1 runned for 6.0219280719757 seconds
Query 2 runned for 6.3680560588837 seconds
Query 3 runned for 5.055319070816 seconds