Dynamic Sql Method 4 Performance
Ben Ethridge
Hello there, Oracle Afficionados:
We are benchmark-testing Select/Fetch cursor performance of Oracle Dynamic
SQL Method 4 (pro*cob/Unix) vs VisualBasic/RDO/ODBC vs "normal" non-dynamic
Oracle pre-compiled Cobol (again pro*cob/Unix).
Sql Method 4 and VB/RDO/ODBC are neck-and-neck. Non-dynamic pre-compiled
cursors are way out in front. We would like to make sure we understand why
precompiled cursors are so much faster than Dynamic Sql Method 4 cursors.
A few years ago, Oracle tech support told us that dynamic sql's
performance would be close to that of non-dynamic. Now they are telling us
that dynamic should much slower. They are telling us that it is because
pre-compiling eliminates the need for a PREPARE statement, data type
checking, Select and Bind descriptions, etc. But, they were somewhat vague
in their answer, saying that no documentation existed on the subject, and
that they couldn't give us an answer in writing, so we remain somewhat
skeptical, and would like a second opinion.
( ) Has anyone done similar performance benchmarking?
( ) Does anyone know exactly why dynamic method 4 is slower?
( ) Does anyone know a way, other than Oracle Objects for Ole, to
end-around the less-than-stellar performance combination of ODBC and
sql*net v2?
Any help would be most appreciated.
Ben Ethridge
Financial Data Planning Corp.
Thomas J. Kyte
What kind of network are you on? Host based system or client server? that will
affect the performance somewhat.
The reason is that a statement like:
exec sql select n into :x from T;
will cause the statement to be prepared/opened executed and all that in
basically one SQL call. dyanamically this would be:
exec sql prepare
exec sql describe bind variables for
exec sql describe select variables for
exec sql open
exec sql fetch into
exec sql close
Lots more potential round trips...
Also in the 'static' are you using array fetches and not so in the dynamic?
There really isn't a difference between static and dynamic tho. There are no
precompiled cursors in Oracle at all. if you look at the generated code from
pro*cobol, you should see the query in a character string constant being passed
to a SQL function for the 'static' sql. It's just that static sql, since you
the programmer don't need interact with it as much, can do more in less trips.
if you start array fetching the dynamic queries, you will see their performance
go way up as well.
Thomas Kyte
Oracle Government
tk...@us.oracle.com
http://govt.us.oracle.com
---- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation