1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
SQLite is the first extension that makes use of SPL automatically by simply
enabling both.
SQLite offers four access strategies:
1) sqlite_query + sqlite_fetch_array
2) sqlite_unbuffered_query + sqlite_fetch_array
3) sqlite_query + iterators (sqlite_current)
4) sqlite_unbuffered_query + iterators (sqlite_current)
5) sqlite_array_query
1) and 3) do "over eager evaluating" since they fetch all rows directly.
2) does "eager evaluating". It always fetches the next row but doesn't
keep the current row, so that it must be stored elsewhere if it must be
accessed more then once. For instance this happens when you need to access
columns separately.
4) does "eager evaluating". But in contrast to 2) it keeps the current row
hence its name.
There is no efficient way for "lazy or just in time evaluating" so 4) should
be the best case. And 4) also enables the foreach trick.
5) does a full buffered fetch and returns the complete result into an array.
As long as you only have a few rows in your result this is very fast and of
course it is very flexible since you can access any column/row as often you
like and in any order you like. But it needs to store the full result what
is called "eager evaluating".
Speedwise analysis:
I compared a database using a table of round about 200 rows with 3 columns.
I measured the case where 10 rows are returned, since i guess this is a
value often taken as default list size in web applications. However i did
that 10 times because the loop initialization is the slowest part of foreach
overloading. Since we are only interested in the relative effect foreach
overloading has i stiped the setup part and used a query result iteration
that does nothing. That means i run 'php -h' in the profiler first and then
profiled every single case. For completeness i also computed the values
including the setup process.
Method, without setup, with setup
1) 100.00% 100.00%
2) 89.32% 97.16%
3) 88.35% 96.90%
Furthermore i did some more checks and found out that the loop process using
foreach overloading (2) takes a constant time while it seems that the time
needed to add more rows to the array increases with the number of rows being
already in the array. As a result (2) is faster than (3) after round about 45
rows.
The loop codes used:
1) Unbuffered query
<?php
$dbname = dirname(__FILE__).'/profile.sqlite';
$db = sqlite_factory($dbname);
for ($i = 0; $i < 10; $i++) {
$res = $db->unbuffered_query("SELECT idx, name, size from files LIMIT 10", SQLITE_NUM);
while ($res->has_more()) {
// var_dump($res->current());
$res->current();
$res->next();
}
}
echo "DONE!\n";
?>
2) Unbuffered query using foreach overloading
<?php
$dbname = dirname(__FILE__).'/profile.sqlite';
$db = sqlite_factory($dbname);
for ($i = 0; $i < 10; $i++) {
foreach($db->unbuffered_query("SELECT idx, name, size from files LIMIT 10", SQLITE_NUM) as $row) {
// var_dump($row);
}
}
echo "DONE!\n";
?>
3) Array query method
<?php
$dbname = dirname(__FILE__).'/profile.sqlite';
$db = sqlite_factory($dbname);
for ($i = 0; $i < 10; $i++) {
foreach($db->array_query("SELECT idx, name, size from files LIMIT 10", SQLITE_NUM) as $row) {
// var_dump($row);
}
}
echo "DONE!\n";
?>
|
