On 9/27/07, Fred Moyer <[EMAIL PROTECTED]> wrote: > I think prepare_cached() is mostly used for dbs that don't prepare > statements on the server side, like mysql and sqlite, but I could be > wrong there.
I don't know how it is commonly used, but I've seen it be useful with databases that do prepare statements on the server side. It takes resources to check whether or not you've prepared a query before. Those resources include unnecessary round trips to the database, CPU time on the checks, internal latching within the database and so on. While individually these are cheap, they add up for a busy transactional database. If you use prepare_cached rather than prepare, you can get rid of this overhead. I forget the exact savings we had on a busy Oracle database, but using prepare_cached cut CPU usage by over 10% and significantly reduced network traffic. As the site got busier, it still wasn't enough. (Particularly after migrating to the CPU hungry Oracle 10.) In fact we ran into problems because we were having latching issues on the ping statement! We eventually solved those by injecting some code into DBD::Oracle causing only one ping in 10 to actually ping the database. (When we had the issue, we found from Google that a number of other people have had that exact problem with Oracle. But our solution couldn't be donated back to the community because we were pulling some fast games with timestamps on specific files so that if any ping failed, every other connection would have to really ping. This made failovers happen in a timely fashion.) I haven't stressed PostgreSQL enough to know whether it would benefit from a similar treatment. Cheers, Ben
