On 2008.04.16, Jeff Rogers <[EMAIL PROTECTED]> wrote: > Do you have test code and results to back this up?
Not any longer. I did, ~5 years ago, when I worked as a developer on a web app on Oracle 8i. > I've been told this by every oracle dba I've worked with and the > performance gains I've seen by moving to prepared statements is > generally quite small unless the sql is horrendously complex, I was dealing with a query that was taking ~200 ms to execute. That didn't leave much breathing room for any of the other queries necessary to build the page. After some benchmarking, we had empirically deduced through repeated tests that the query in question was spending ~180ms in parse (incl. execution plan) and only ~20ms to execute and return the results. Pushing the query into a stored procedure (effectively the same as a prepared statement, as the stored procedure is parsed/compiled once) dropped the execution time to ~30ms, which is within the window of what was acceptable. > Not that I doubt query parse time is a hit, but without numbers I > won't buy that it is a big performance hit. Generally, it's a non-issue. However, in certain cases, it can be a material difference. In those cases, _not_ having the _capability_ to use prepared statements because they aren't implemented, is a defect, IMHO. > One possible although complicated way around this might be to implement > a new tcl type for cached statements, using the sql as the string rep > and the internal handle as the "other" type. Then ns_db could shimmer a > sql string to a prepared statement as necessary. This might cause > issues with sharing tho, since I nsv only stores the string > representation to avoid thread-local data problems. OTOH, if the cached > statement has thread local data (unlikely but possible) this could be > just fine. As Bas pointed out, prepared statements are tied to the actual database connection. -- Dossy Shiobara | [EMAIL PROTECTED] | http://dossy.org/ Panoptic Computer Network | http://panoptic.com/ "He realized the fastest way to change is to laugh at your own folly -- then you can let go and quickly move on." (p. 70) -- AOLserver - http://www.aolserver.com/ To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
