[Tickets #1439] DataTree sql is not Oracle compatible

[bugs at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=1439
-----------------------------------------------------------------------
 Ticket             | 1439
 Updated By         | selsky at columbia.edu
 Summary            | DataTree sql is not Oracle compatible
 Queue              | Horde Base
 Version            | 3.0.3
 State              | Feedback
 Priority           | 2. Medium
 Type               | Bug
 Owners             | Horde Developers
-----------------------------------------------------------------------


selsky at columbia.edu (2005-03-10 10:26) wrote:

Some notes from my DBA on bind variables:

i do notice one specific problem with the queries.  the application is
submitting literal sql with hardcoded variables to the server, instead of
using bind variables.  we have seen this cause problems in the past. memory
allocated to parsing sql statements is being saturated with non-reusable
code.

to show you what i mean, the following sql has hardcoded values for it's
variables (26, 126, 1028, 11206).  this sql must be parsed by the server to
generate an execution path, then this parsed query is remembered in cache
for the next time it's executed.   however, the next time it's executed the
variables change, so the server cannot re-use the execution path that it
figured out, and must re-parse a new query and cache that parsed query
also.

[...]

this statement is prepared once, then called repeatedly, replacing all the
:bind_vars with different values each time.  the query text doesn't have any
hardcoded variables so the server can reuse this parsed code.

[...]

we have seen significant performance improvements from taking these steps,
and i'd encourage you to ask the vendor to make these changes.