Hi. I currently have a fairly large query which I have been optimizing
for later use in a function. There are things in the query which I
have been keeping constant whilst optimizing, but which are variables
in the function. When I run this query as sql, with the `variables'
constant, I get a runtime of about 3 or 4 seconds. However, when I
place this same sql in an sql function, and then pass my constants
from before in as arguments, I get a runtime of about 215 seconds.
I am trying to understand how this could be. How does putting the sql
in a function affect the query optimiser? Would putting it in as a
plpsql function help? How else can I retain the original speed?
I'm planning on using this functionality from JDBC, so I'd rather not
be forced to put the sql into the JDBC directly, but a difference of 2
orders of magnitude in speed can't be ignored.