Thread: ADODB VS Zend_DB ??

Adodb is pretty slow, especially when you don't have Adodb extension installed. I've been using Adodb for a long time beacause it makes database handling very easy, but I wouldn't use it with Zend Framework. Zend_Db is great choice for database handling too and it's faster and more portable then Adodb.

Hi borec, thank you for your reply

I have search after I posted this thread and I have questions about your statement.

Would you mind to tell me more details about the reason of Zend_db faster than Adodb and more portable ?

Firstly, I don't agree zend_db is more portable.
I think the adodb is more portable because it independent with the framework, which mean that we just plugin and play the database and adodb into new framework when we wanna change the framework. If my opinion is not true, please let me know

On the other hand, is it possible to implement active record in Zend_db by ourselves ?

Why faster? Well, first of all, adodb is written in about 28702 lines of code and Zend_Db in about 2000. Also, Zend_Db is written entirely for PHP5 and Adodb for PHP4, which is much slower.

Why more portable? It's not so easy to switch to another RDBMS with Adodb, you have to write with some things in mind (see Tips on Writing Portable SQL for Multiple Databases for PHP). With Zend_Db, you can just change adapter and it works.

I think the adodb is more portable because it independent with the framework, which mean that we just plugin and play the database and adodb into new framework when we wanna change the framework.

You can do exactly the same thing with Zend_Db. In fact, you can use every of Zend Framework components as a stand-alone component for any of your application not built using ZF.

On the other hand, is it possible to implement active record in Zend_db by ourselves ?

Why not? You just have to know how

Thank you, your reply is very helpful

I have one more question about the portability of the Zend_Db.

I know that Zend has an Class, Zend_Db_Expr which allow to use SQL function inside a SQL statement. But, sometimes, there are some SQL functions were supported in specified DBMS. If I applied an expression / functions which is not supported by others DBMS, does Zend_Db_Expr will resolve this heterogeneous problem ? If not, how can I prevent this problem to make the database more portable ?

Originally Posted by GeniusTse

I know that Zend has an Class, Zend_Db_Expr which allow to use SQL function inside a SQL statement. But, sometimes, there are some SQL functions were supported in specified DBMS. If I applied an expression / functions which is not supported by others DBMS, does Zend_Db_Expr will resolve this heterogeneous problem ? If not, how can I prevent this problem to make the database more portable ?

Zend_Db uses PDO, therefore this is also true when using it:

Originally Posted by PHP documentation

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

So if you want your application to be completely portable, you should avoid using pure SQL statements in your code (with Zend_Db_Statement, not Expr). Instead, use only abstracted classes like Zend_Db_Table.

Of course, if you want to use some features that are supported by one of the DMBS but not by others, you could write your own abstraction to this feature that will emulate it for DMBSs not supporting it.

If we are using the pure abstract class, Zend_Db_Table, it seems inefficient when I want to join tables. Refer to the Reference Guide - Zend Framework: Documentation,
[php]
/*line 1*/ $accountsTable = new Accounts();
/*line 2*/ $accountsRowset = $accountsTable->find(1234);
/*line 3*/ $user1234 = $accountsRowset->current();
/*line 4*/ $bugsAssignedToUser = $user1234->findDependentRowset('Bugs', 'Engineer');[/php]
it joins Accounts to Bugs with two find method (line 2 and line 4)

Q1: Does it open&close database connection 2 times or just 1 ?
Q2: Compare with pure SQL, how's the performance of this style to query databases?

Please forgive my foolish and poor english
LoL

I cannot answer your questions right away but to effectively see if two connections are opened and how the SQL output looks like, I recommend working with the log files of your database if this is possible.

This way you can exactly see how often a connection is opened or which queries are issued to the database.

About the performance of this query style: This heavily depends on the SQL output when using Zend_Db_Table and more or less about how you design your database.

In general no one can say right now that Zend DB is faster than AdoDB because there are no real benchmarks out yet. Just less code lines and PHP5 do not make a DB abstraction faster.

For example: I recognized that ALL queries using Zend_Db_Adapter are prepared. This sounds like a good idea, but in fact it isn't.

When you're doing a simple insert query:

[PHP]
<?php
$data = array(
'created_on' => '2007-03-22',
'bug_description' => 'Something wrong',
'bug_status' => 'NEW'
);

$db->insert('bugs', $data);
[/PHP]

the query is prepared first and then executed which ends up in TWO SQL queries send to the database server. Now imagine inserting 20.000 rows in a loop = 40.000 db queries with a huge amount of prepare overhead.

Usually you would prepare the statement by yourself one time and then just change the values being issued and executed the former prepared statement, which is 20.001 queries Prepareing a statment is not bad, but in this case you're just not aware that it IS prepared and you have no chance to disable automatic prepares in ZF right now.

So I would suggest you do excessive benchmarks regarding the requirements of your application before you blindly trust a specific db abstraction. But one could say, usually the db design, correct indices and normalizing / denormalizing the db really matter.


About huge libraries like AdoDb or the Zend Framework:
you might also use eAccelerator: PHP Accelerator, optimizer, dynamic content cache - Trac which caches compiled PHP scripts and is especially effective when using a great amount of static include files which rarely change.

Originally Posted by chriswest

When you're doing a simple insert query:

[PHP]
<?php
$data = array(
'created_on' => '2007-03-22',
'bug_description' => 'Something wrong',
'bug_status' => 'NEW'
);

$db->insert('bugs', $data);
[/PHP]

the query is prepared first and then executed which ends up in TWO SQL queries send to the database server. Now imagine inserting 20.000 rows in a loop = 40.000 db queries with a huge amount of prepare overhead.

Usually you would prepare the statement by yourself one time and then just change the values being issued and executed the former prepared statement, which is 20.001 queries Prepareing a statment is not bad, but in this case you're just not aware that it IS prepared and you have no chance to disable automatic prepares in ZF right now.

While this is true it is possible to cache the table metadata removing the second query thus returning you to 20,000 queries

i.e.

<?php

// First, set up the Cache

require_once 'Zend/Cache.php';

$frontendOptions = array(
'automatic_serialization' => true
);

$backendOptions = array(
'cacheDir' => 'cacheDir'
);

$cache = Zend_Cache::factory('Core', 'File', $frontendOptions, $backendOptions);


// A table class is also needed

require_once 'Zend/Db/Table/Abstract.php';

class Bugs extends Zend_Db_Table_Abstract
{
// ...
}


// Configure an instance upon instantiation

$bugs = new Bugs(array('metadataCache' => $cache));
?>

Sorry, maybe you understood me wrong or you just wanted to show how table metadata caching is achieved (which is nice!) - the problem I wanted to show lies deeper (within the adapter implementation) and has nothing to do with a table's metadata.

to pick up your example, if you do this:
[PHP]
$bugs = new Bugs(array('metadataCache' => $cache));
[/PHP]

This just solves the following "issue":

By default, Zend_Db_Table_Abstract queries the underlying database for table metadata upon instantiation of a table object. That is, when a new table object is created, the object's default behavior is to fetch the table metadata from the database using the adapter's describeTable() method.

But, if you do a simple insert:

[PHP]
$bugs->insert($data);
[/PHP]

The query will internally be prepared every time you call the insert method. The mysql log looks like this then:

[PHP]
Prepare INSERT INTO ...
Execute INSERT INTO ...
[/PHP]

for every call to insert() query.

While this is good for Oracle database backends (which benefit from prepared statements) it really is a performance bottleneck regarding to MySql backends which do only benefit from a prepared statement if you prepare it once and then use it to insert e.g. 10.000 rows with the same prepared statement.

hope I was able to explain the problem

(just turn on your mysql log file if you use a mysql database, to see what really happens when using insert())