Improving speed (long)

Lukas Smith wrote: > > > -----Original Message----- > > From: Alex Black [mailto:enigma@turingstudio.com] > > > Everything else about metabase, i.e. the API, loading, etc I think can > be > > either toasted and done over or tweaked until it's faster. I don't > think > > metabase will ever be as fast as ADODB (hock, spit), or PEAR:DB, > because > > it > > does so much more. At the same time I think there is quite a lot of > stuff > > that could be done to make it a lot faster. > > Actually I think if we find a nice solution to separate metapear into > packages then I think metapear can very well compete with PEAR DB in > performance > (I think PEAR DB probably has some overhead with their > result object which if I got tomas right is not something that really > did much for them). I don't know enough about ADODB. The PEAR DB design is very good but has the problem that PHP is not so good yet for handling it ;-). Let me explain my self a little on how PEAR DB works internally. Step by step in a common operation: $db = DB::connect('mysql://..'); $res = $db->query("SELECT ..."); $res->fetchInto($row); * $db = DB::connect('mysql://..'); connect() detects that mysql is the driver selected and does an include of the "DB/mysql.php" file. This file defines a class DB_mysql which extends the DB_common class (contained in the file DB/common.php). The resultant $db object has all the methods for handling the MySQL database. * $res = $db->query("SELECT ..."); At this point query() returns a DB_result object (defined in DB.php) which is only an interface for the $db object (the $res object has a property that is really a reference of that $db object). This is, each time you do a call like: * $res->fetchInto($row); what you are really doing is calling the $db->fetchInto($row) with certain params, so: $res->db->fetchInto($row); The DB_result object is usefull for making the API more easy to use (it internally pass the properly params needed by $db->fetchInto()) and well, also it avoids people to be able to call other methods from $db while playing with result sets. The main problem of this technique is the speed. The overhead caused by this interface is too high. Just see some benchmarks: // MySQL database, table with 50.000 rows 1) Using the DB_result interface: $res = $db->query("SELECT * FROM table"); while ($res->fetchInto($row)); 2) Overpassing the interface: $res = $db->query("SELECT * FROM table"); // The mysql result resource $result = $res->result; // Direct call to the method while ($db->fetchInto($result, $row, DB_FETCHMODE_ORDERED)); Results: 1 -> 26.209026 seconds 2 -> 13.831852 seconds A benchmark says more than thousand words :-) > Obviously seperating everything into packages is very easy. But I would > like the including to happen automagically somehow :-) To split the functionality is a good thing of course (always having in mind that to increase the IO overhead of having too many files is not a solution). I've made some stats of what things are more used of PEAR DB in two different proyects: 1) PEAR Web -> 60 php files [query] => 47 [getAssoc] => 16 [prepare] => 13 [execute] => 13 [getRow] => 11 [getOne] => 7 [nextId] => 6 [getAll] => 6 [getCol] => 5 [setFetchmode] => 5 [expectError] => 4 [popExpect] => 4 [affectedRows] => 3 [setErrorHandling] => 3 [nextID] => 2 [limitQuery] => 2 [dropSequence] => 2 [errorNative] => 1 2) A proyect from mine -> 155 php files [query] => 160 [setFetchMode] => 63 [getOne] => 62 [getRow] => 45 [getCol] => 23 [nextID] => 12 [prepare] => 12 [execute] => 11 [autocommit] => 6 [quoteString] => 3 [getAll] => 3 [affectedRows] => 3 [limitQuery] => 2 [rollback] => 2 [commit] => 2 [quote] => 1 So IMHO the functionality could be splited in three parts: No Manipulation: query, get*, prep/exec, datatypes (default) Manipulation: sequences, transactions Extra: schema managment, cache The "how" question is more difficult to answer :-). I see some ways: 1) by aggregating methods as Stig proposed (cool as it can happen automaticaly but no support for almost no PHP4 installations). 2) by extending and preselecting the mode (will need knowledge of what is avaible in each mode but will be supported by all PHP4 releases). 3) by having different objects for the different modes and some kind of "object manager" to create, comunicate and handle them (no need of preselecting the mode, supported by all PHP4 releases but perhaps tricky to code). Tomas V.V.Cox