On 02/04/2004 06:09 PM, Michael Cesar wrote:
Thanks a million...
"BTW, if you are really putting the SELECT text in the script,
prepare()ing a SELECT by itself and executing that would be more
efficient and avoids having a cursor returned from a package."
Why is this so? My DBA wants everything in s/p or packages for speed.
I also thought that it would already be in memory and therefore
faster. Could you explain.
If all the work was being done inside the function or procedure it would
be faster because all that work would happen in one round trip to the
RDBMS, but you have to repeatedly execute() the procedure calls to
retrieve the data one row at a time which requires extra round trips.
Fetching from a cursor or SELECT allows multiple rows to be cached in
the client by a single call to the RDBMS. Calls to fetch() then
retrieve rows from that cache which is replenished as necessary.
Calling a procedure for each row does not allow caching on the client so
a separate set of RDBMS calls have to be made for every single row.
Please keep your responses on dbi-users. I am not the sole source of
all wisdom.
--
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
