On Mon, 2006-07-24 at 15:15, Kilbride, James P. wrote: > While that kind of query structure can be a huge issue the biggest > problem, and performance penalty, occurs if the programmer is opening > and closing actual real connections. A good structure would use a > singleton connection that is opened only the first time it is called and > the rest of the time returns the connection object already opened. Or, > use pooling or similar types of things. You will get orders of magnitude > increase in speed by not reopening the database multiple times per page > especially if you are seeing 15,20 or especially hundreds of > connections. Sometimes though you have no real choice but to build a > system that has to generate multiple queries, so don't judge the page > simply on the number of queries but on the completity of the reporting > and the number of database connections opened(you will occasionally need > more than one if you arn't pulling all the results at once, using > cursors or partial retrievals instead and have to have multiple result > sets open at any given time). Well the system was using one of those not so insightful global database connections so opening up the connection was not the problem. I'm well aware of where bottlenecks occur, but I assure you such a system is not very scalable over a LAN even if you only open one connection. But I guess it doesn't matter if scalability isn't your concern... until further down the road anyways... YOUCH. Either way, using a factory to produce the objects from the row data is much more efficient since you can use a single query to get all children in one shot. This is still not perfect due to recursive nature (there are ways to do it without as some forum discussion have pointed to but I forget the links) but depending on the branchiness of the tree structure will provide a much better response time. As for judging, I guess I was the only one there to see something as simple as retrieval of product categories and products get really out of hand. I reduced the queries to under 200... and since we were still only opening one connection I know the time cost was reduced to about 1/5. The queries could have been reduced more but it was more work than the customer felt was worth the additional savings. Knowing what choices are available is part of competence. If all you see is one choice, there may in fact only be one choice, or you may be too inexperienced to see the others that exist. Cheers, Rob. -- .------------------------------------------------------------. | InterJinn Application Framework - http://www.interjinn.com | :------------------------------------------------------------: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `------------------------------------------------------------' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
