On 2/10/06, Paul Johnston <[EMAIL PROTECTED]> wrote: > >How did you optimize your db connections? > > Optimising db connections: cutting down the number of DB connections per > request. I remember load testing and doing quality assurance on a UK > government website (to remain nameless), and the front page (and pretty > much every subsequent page) was making 50+ database calls per request. > They were wondering why it was going slow. So optimising the database > connections is about figuring out how to get the Database to do the work > it should be doing, and doing as few calls as possible within each request.
Just wanted to comment that reducing the number of DB calls should not be done blindly. I have seen several cases where a programmer reduced the number of queries, only to drastically increase the time it takes to execute the newly re-written queries and in turn slow down the app. As a quick example, I was tasked with maintaining an application that did a lot of statistics processing and analysis. Data was dumped into a logging table, and then a daemon process picked up the next N number of rows and summarized the info into a multitude of tables. In order to reduce the number of queries sent to the DB, the programmer decided to write two really convoluted queries that would pull out existing data regarding these N new records out the database. So if N was 100, these two queries would pull out at most 200 records out of the DB. During testing with hardly any data in the DB, these queries appeared to work very quickly, but once the database started filling up these two queries were taking minutes each to complete. I ended up rewriting the code so that it did 1 query per row (ie 200 queries) to pull out the exact same data, and since they were simple keyed access queries, they completed in under 2 seconds. So we went from 2 queries taking between 2 and 5 minutes to 200 queries taking ~ 2 seconds. Often, a whole bunch of queries that properly use indexes will outperform a few queries that can't use indexes. Of course in most instances reducing the number to trips to the database will increase performance, just make sure it really does by testing it. Cheers, Cees --------------------------------------------------------------------- Web Archive: http://www.mail-archive.com/cgiapp@lists.erlbaum.net/ http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2 To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
