RE: Improving speed (long)

> -----Original Message----- > From: cox@idecnet.com [mailto:cox@idecnet.com] > 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: Metabase follows the same design without the result object. > The main problem of this technique is the speed. The overhead caused by > this interface is too high. Just see some benchmarks: I don't know how much php5 will reduce the overhead and this sort of leads to the next question: what versions of php do we want to support. I think we should try and support php4 and php5. > 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: yes, a plugin architecture like smarty with one function per plugin will see quite a heavy hit on performance ... so in reply to Peters email: no most likely we will not have a single function per include file. > 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 we also have LOB support, which is the only thing that Metabase currently keeps separate. I am a little unsure if I would not want to keep sequences in the core package. Schema management and caching should definitely not be part of the core package. Schema management and cache should probably be separated because they will be used in very different situations (cache will always be included on a site that needs it, while schema will most likely only be included in some admintool). Some other functions are for grabbing metadata (in PEAR for example: tableInfo, getListOf). Where should those go? On that note: What is the situation with getting associative arrays in all RDBMS ... recently there were some issues with interbase. How does it look for all other RDBMS? Btw: I think I am now quite clear about the advantages of prepare. The important thing about prepare is that it will actually be slower if you only run the query once, which will happen quite often. So prepare (ore prepare based methods like get*) should not be the only choice in the core package to run a query. Another thing about get* is that I will see if I can rewrite get* so that it will be based on Metabases FetchResult* methods, so that we can provide both fetch method styles with fewer code. I have already modified the FetchResult* functions to be able to return associative result arrays and I have also written the necessary functionality for getAssoc(). Best regards, Lukas Smith smith@dybnet.de _______________________________ DybNet Internet Solutions GbR Alt Moabit 89 10559 Berlin Germany Tel. : +49 30 83 22 50 00 Fax : +49 30 83 22 50 07 www.dybnet.de info@dybnet.de _______________________________ > > $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. > > > // 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 :-) > > > 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 > > > 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