Very interesting... I hope my new company will pay to let me take your
CFMX/SQL Server 2000 class (I'm trying not to foam at the mouth...:-)
I know my questions opened a few can of worms, so any and all thoughts are
much appreciated, especially considering how busy you 2 are...
So, with the simplified example you gave, it sounds like good candidates for
Sprocs v. CFQUERY calls are any common queries that don't need to be
recompiled (I'm somewhat familiar with how this works already), such as
queries that either always get all the data from a table(s), or a query that
always gets just one record... Am I on the right track?
In my recent job interview (I got the job, btw), I seemed to impress the
interviewer by answering his questions about how to enhance performance in a
CF application by harping the "proximity theory", moving as much business
logic to the DB as possible (thus, getting it closer in "proximity" to the
data) and improving performance. I used the following reasons to back up
this theory, which up until now I thought was pretty good...
1) Sprocs on the DB enhance security
2) If you have all queries in Sprocs, triggers, and Views on your database,
then your query logic is all in one place (which I guess you can do with
CFC's, too)
3) Sprocs on the DB are cached, and if you put multiple queries/transactions
into a single Sproc, then you minimize chatter between your app and DB
servers
He seemed impressed, but had some questions about if this is really ideal
under very intense load. I was certain I had the best answers at the time,
but now I'm wondering. Going forward, it'll be very important that we build
apps to be completely scalable and able to handle load. So, do the answers
I gave in the interview have at least some merit? And if I'm asking too
much, don't be afraid to say so. I'm just very interested, and I would pass
this info on to many CF'ers once I understand more fully...
And I'd love to hear more about your workaround for recompiling of Sprocs
from CF!
Thanks Adam, this is very valuable!
Aaron
----- Original Message -----
From: "Adam Churvis" <[EMAIL PROTECTED]>
To: "Joe Frank" <[EMAIL PROTECTED]>
Cc: "Plum Discussion List" <[email protected]>
Sent: Monday, March 28, 2005 7:51 PM
Subject: Re: [plum] Plum under load?
Wouldn't mind myself finding out why this piece of accepted gospel is
incorrect........
Here's the short short *short* version of just one example...
1) You run a stored procedure for the first time, and its search criteria
select a large number of records.
2) The cost analysis suggests that under those conditions, no index should
be used because a full table scan would achieve the best performance.
3) This fact is compiled into the query execution plan.
4) You run the stored procedure again, but this time the search criteria
select a very small number of records.
5) The query execution plan that was compiled for the stored procedure is
still cached, and is executed.
6) The query execution plan instructs the database server to again do a
*full table scan* in order to retrieve the six rows out of eight million
you
really wanted.
7) If the stored procedure were recompiled when it was run the second
time,
it would have known to use a specific index (assuming one was present) to
select and retrieve those six rows, and it would have done so. In a case
like this, the difference in performance could have been measured with an
egg timer.
There are two ways to specify that SQL Server recompile a stored procedure
at runtime:
A. In the definition of the stored procedure itself using the WITH
RECOMPILE
directive, or
B. When calling the stored procedure, again using the WITH RECOMPILE
directive.
"B" is more flexible, but ColdFusion currently has no way to communicate
this directive through the driver. I keep asking for it on each new Beta
cycle, but my requests fall on deaf ears because apparently nobody else is
even aware of how all this works.
If you just specify "A" on all your stored procedures, you universally
eliminate much of the efficiency of a stored procedure in the first place
because you're preventing the query execution plan from being cached.
It all comes down to the selectivity metrics of one stored procedure's
result set against those of the queries that have been executed before.
Even as simple an act as a stored procedure selecting all rows where the
last name begins with "M" versus the exact same stored procedure using an
"X" as the value of that search attribute will cause this to happen, and
unless you are aware of it and are carefully controlling recompilation,
you
can screw performance badly.
I invented a workaround to handle this from ColdFusion a few months ago,
but
I never published it. I'll try to put something together about it.
Respectfully,
Adam Phillip Churvis
Member of Team Macromedia
http://www.ProductivityEnhancement.com
Download Plum and other cool development tools,
and get advanced intensive Master-level training:
* C# & ASP.NET for ColdFusion Developers
* ColdFusion MX Master Class
* Advanced Development with CFMX and SQL Server 2000
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
**********************************************************************
You can subscribe to and unsubscribe from lists, and you can change
your subscriptions between normal and digest modes here:
http://www.productivityenhancement.com/support/DiscussionListsForm.cfm
**********************************************************************
