Relational DBMS's are very good at performing just that: relational database queries. We found terrific performance enhancements when we converted long, iterative CF processes into relational stored procedures. However, when these same processes were converted into iterative stored procedures (using cursors and the like), the performance gain was virtually nil....and sometimes worse.
Brian
----- Original Message -----
From: Adrian Lynch
To: CF-Talk
Sent: Thursday, August 26, 2004 8:44 AM
Subject: RE: stored procedures
"That is where the benefit of SP's are, that once run all future runs are
significantly faster."
I think that this isn't always true. It depends what statements are within
the SP. It's possible that an SP could produce various execution plans and
the one that's cached might not be the best one for future calls. In the
past I've seen (and been guilty of writing) SPs that do significantly
different things depending on the incoming params. I did have some good
links with more specific details but I can't remember/find them at the
moment.
Anyone care to elaborate?
Ade
-----Original Message-----
From: Bill Grover [mailto:[EMAIL PROTECTED]
Sent: 26 August 2004 13:53
To: CF-Talk
Subject: RE: stored procedures
True, but coming up with the execution plan is what takes the longest and
has the greatest impact on the server. So by having a SP once it is
executed and that execution plan is cached they become very quick.
For example, I have a SP that uses about 10 select statements to retrieve
data from 4 different databases and about 15 different tables. The 1st time
it is run it takes about 10-15 seconds to return 2,100 rows. If I wait a
few seconds and run it again it takes about 3 seconds to return the same
rows. That is where the benefit of SP's are, that once run all future runs
are significantly faster.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
