prepares in 9.2

I recently went from 7.22 to 9.2 (big change, I know). In 9.2 statement cache is used so that similar query plans are shared. Does this mean that preparing statements is no longer needed in 9.2? Sent via Deja.com http://www.deja.com/ Before you buy.

Also, do you know if there is a benefit from preparing sql used repeatedly in a stored procedure? When sql appears in a stored procedure, doesn't it automatically save the query plan for future calls of the routine? If so, preparing sql in a stored procedure would be useless. Any help would be appreciated! Sent via Deja.com http://www.deja.com/ Before you buy.

cstefanick@koz.com wrote: > I recently went from 7.22 to 9.2 (big change, I know). In 9.2 > statement cache is used so that similar query plans are shared. Does > this mean that preparing statements is no longer needed in 9.2? > > Sent via Deja.com http://www.deja.com/ > Before you buy. SQL Caching helps, but preparing is still, by far, the best way to go. Tests that I conducted reveal the following in terms of CPU utilization by Informix (onstat -p's usercpu+syscpu) Prepare once, execute 1000 iterations : 100 (baseline) Vanilla SQL, cache on, 1000 iterations : 225 Vanilla SQL, cache off, 1000 iterations : 330 In other words, unprepared SQL (vanilla) with cacheing on needs 125% additional cpu time when compared to a "prepare once, execute many", while turning cacheing off requires a further 100% cpu time. My test was on HP10.20 with 9.2UC1. Each iteration involved 2 inserts (rowsize between 256 bytes and 4k), a couple of selects and 4 updates. Needless to say, the profile of statements will affect the results, though I can not imagine a situation in which the rankings would change. Keep in mind that preparing statements only helps if you can get your application to reuse those prepared statements - this is not necessarily a simple task. Rudy BTW, one cannot prepare statements in Stored Procedures. Earlier tests (on v7.31) indicate that Stored procedures rank behind Prepared statements, using about 100% more cpu - again, SQL profile dependant.

cstefanick@koz.com wrote: > I recently went from 7.22 to 9.2 (big change, I know). In 9.2 > statement cache is used so that similar query plans are shared. Does > this mean that preparing statements is no longer needed in 9.2? > > Sent via Deja.com http://www.deja.com/ > Before you buy. Prepares are still necessary (and really recommended for statements which are executed often), but preparing statements is now far more efficient than with pre 9.2 versions. Also, when the shared statement cache is enabled, less memory is used in the virtual segment if multiple sessions prepare the same statement. Hope this helps, Heiko