Zend_Db_Table time overhead - about 25%

[Viper X]

here I will post my results from a test which I ran to check how much time adds zend_db_table to my code..

In http://zfsite.andreinikolov.com/2008/08/zend_db_table-time-overhead-about-25-percents/ this post in my blog I have posted the full code for the tests and have described exactly what I have done. Here I will post just the results and the conclusion.

I will be happy if you have notes, arguments and remarks. If I miss something about Zend_Db_Table and do not use it in the best way - please do not hesitate to comment :)

I ran the test in 4 different variants. These 4 variants proved to distinguish one from another in the time they took to run.
These 4 variants are:
1) using Zend_Db_Table without metadata cache
2) using Zend_Db_Table with APC as metadata cache
3) writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli
4) writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli

I tested with level of concurency 5 with 100 requests (ab -c 5 -n 100)
Requests per second:
method 3 - writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli: 12.93 rps
method 4 - writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli: 12.66 rps
method 2 - using Zend_Db_Table with APC as metadata cache: 10.2 rps
method 1 - using Zend_Db_Table without metadata cache 9.2 rps

conclusion: Zend_Db_Table is not good for heavy loaded web applications. My personal choice is to go with something like variant 4. The time overhead here is minimal and the code in the controller is most readable.

[Bill Karwin]

Viper X wrote:

conclusion: Zend_Db_Table is not good for heavy loaded web applications. My personal choice is to go with something like variant 4. The time overhead here is minimal and the code in the controller is most readable.

I agree with this.  All ORM solutions are notorious for adding performance overhead.  Their value is in extending the object-oriented paradigm to database programming.

There's no substitute for writing SQL queries by hand, if performance is your top priority.  In fact, if performance is the only concern, you shouldn't be using an RDBMS at all -- you should be managing data using a custom storage module that you write, specialized to your application.  Also, you shouldn't be coding in PHP in the first place -- you should be writing all your code by hand in assembler.

The point being that often a trade-off exists between development convenience and runtime speed.

Regards,
Bill Karwin

[Viper X]

Bill Karwin wrote:

In fact, if performance is the only concern, you shouldn't be using an RDBMS at all -- you should be managing data using a custom storage module that you write, specialized to your application.  Also, you shouldn't be coding in PHP in the first place -- you should be writing all your code by hand in assembler.

Hi Bill, thank for the answer, you are of course right with this statement, but when the performance is not the only concern, but it is main concern - what is the situation then? Coding in assembler is not a question at all, we are writing a web applications, so we have choosen PHP for this. We want some coding standarts and some structure to follow, so we use Zend Framework. It have not small overhead alone, so when we have choosen to use Zend Framework we already have made some trade-off between speed and development convenience.

But. Most of the web applications make heavy use of database. While scattering your code with SQL queries is bad and with no doubt you should avoid it, placing direct SQL queries in some class in your Model layer is not bad design for me.
I don't want to start a flame here, I just want to share some thoughts and to see the opinion of the others. My point of view is that insisting to use another and another layers of abstraction just for the abstraction itself does not lead to good things. Table Gateway and Row Gateway patterns have their use, but they have limitations also and are not the best solution for everything. For example when we want to represent tabular data and edit it row by row this is great approach. Also if we want to have some kind of code generation, for example for quick generation of admin modules, allowing editing, inserting and deleting of rows in and from DB tables the Table Gateway and Row Gateway pattern is nice. Together with Zend_Form this can be very powerfull way to quickly generate very complex applications. But when it comes to speed, writing in assembler is not the only way to improve it..

[Bill Karwin]

Viper X wrote:

My point of view is that insisting to use another and another layers of abstraction just for the abstraction itself does not lead to good things. Table Gateway and Row Gateway patterns have their use, but they have limitations also and are not the best solution for everything.

Who is insisting that you use Table/Row Gateway for everything?  Not me.  I definitely agree that OO abstractions for data access are less speedy than writing SQL queries.  I agree that they have their use, but no solution should be employed in all situations.

Not every database query needs to be optimal.  Some queries are done infrequently.  Many performance problems cannot be solved in a scalable way with code alone -- you have to rearchitect, or use caching, or do server-level scale-out.  Performance optimization is a holistic process, involving every level of software architecture (and some hardware architecture too), so don't focus too much on any one part of it.  Sometimes writing SQL by hand is the best way to improve database access.  Sometimes it may be caching the query results.

You're right that encapsulation is a good OO principle, and centralizing the database queries makes them easier to maintain than scattering SQL throughout your code.  But that doesn't necessarily mean that we need to abandon the OO interface and write *every* SQL query by hand.  What I recommend is that we use OO classes for simplicity and convenience, and when we find some code that is a bottleneck in the app because of using the OO interface, then we can refactor that code to improve its performance.  

My example of writing your own data store module, or abandoning PHP is hyperbole on my part.  I just mean to say that there's always a point of diminishing returns when you choose coding techniques for the sake of performance over development efficiency.  Switching to programming in assembler is of course a bad idea for 99.9% of web apps.  But in very rare cases, it could be exactly the right thing to do.

Where the balance point lies is determined on a case by case basis.  Sometimes you're writing a tiny little intranet app that has only six users and doesn't need to be optimized -- but developing that app needs to be very quick and easy, because it would senseless to invest months of effort for an app to serve only six people.  Other times you're working on a high-end content management system for a highly scalable news site like Yahoo!  Then you can invest a lot of engineering time to optimize more for performance.

I think you and I are probably on the same page...

By coincidence, Rasmus Lerdorf gave a keynote at Drupalcon this week in Hungary, and reportedly he said some things that support this too.  He compared a bunch of frameworks, and observes that they all have overhead that makes them unsuitable for high-performance, high-scalability applications.  He even says that any scripting language (like PHP) is unsuitable for very high-scale apps!  
Here's an article reporting his speech:
http://www.sitepoint.com/blogs/2008/08/29/rasmus-lerdorf-php-frameworks-think-again/

Regards,
Bill Karwin

[monk.e.boy]

Bill Karwin wrote:

Who is insisting that you use Table/Row Gateway for everything?  Not me.  I definitely agree that OO abstractions for data access are less speedy than writing SQL queries.  I agree that they have their use, but no solution should be employed in all situations.

Regards,
Bill Karwin

I found this quite interesting. I dislike the encapsulation of SQL, I prefer to just write it.

I don't think the distinction is made in the ZF documents. It simply shows you how to use all the DB objects and implies you *should* be using them. This is where the confusion lies. I had a 'feeling' that I was somehow doing it wrong, but the more I hang out in this email list the more I realise that perhaps I'm doing it right.

Like you say, for simple queries, use what suits you best. For slow pages think about just using SQL and for very slow pages think about memcache or something.

monk.e.boy