> 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...:-)
Returning students get a $500 discount. :) > 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? Well that's one way to look at it -- and technically that is correct in a narrow view -- but it's not the way we approach the design of the database tier. There has to be a compelling reason to move logic to a stored procedure, and there can be many reasons in some cases and none in others. It has to be taken on a query-by-query basis, and there are other factors that affect the decision, one of which you refer to later on in this discussion: minimizing cross-tier chatter. > In my recent job interview (I got the job, btw), Congratulations! :) > 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 To the extent that we briefly covered database-related issues in the ColdFusion MX Master Class these are all correct (at a very high level), but there are *many* factors that can and will affect your design planning. One of which we have been discussing: selectivity and its effect on stored procedure performance. It's much like a balancing act in a circus, where each step presents a new necessity to carefully move the balance bar in a new direction, sometimes a little, sometimes more than a little, but never just a blanket "Throw it on over to the left!" If you can handle complex logic on the database tier by sending a single message to it, then it's almost always a good idea to do so because you can then minimize (if not totally eliminate) chatter between your app server and database server tiers, handle transactional control natively in the database tier, and certainly increase performance. Only when the application server tier must contribute directly to the transaction do I surrender to chattering back and forth. For example, when inserting a sales order and its related order items, there is no well-formed way to pass complex ColdFusion objects to SQL Server in a native format it can understand, so the sales order row must first be inserted and its newly-issued ID must then be communicated back to ColdFusion Server so that value can be used to insert the related (child) order item rows using that ID value as the foreign key to its parent. This then requires that transactional control be handled via CFTRANSACTION, which tells the database driver to interpret how the database wants to be told that it should spawn a transaction, then later (hopefully) it tells the driver to either commit or rollback that transaction. Very primitive, relatively unreliable (hiccups are not handled well), and crippled with respect to handling advanced transactional control (like nested transactions). So native transactional control within the database tier itself is far superior, and is therefore a compelling reason to *look at* moving the logic that you can feasibly move out of ColdFusion Server and into the database tier. > 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... Most likely his concern was regarding triggers in the database, which if not designed correctly can utterly ruin performance and even bring a database server to its knees. There is a careful balance regarding trigger logic, and it often dictates a change in schema in order to balance data integrity with performance. That's a tricky subject that we cover in the database class, but for a pointer imagine a trigger that fires on a table that is constantly pounded with multiple simultaneous transactions all day long... Not good. Heap bad mojo. We'll typically redesign that portion of the database in order to either substantially decrease the amount of logic the trigger needs to perform, or eliminate the need for the trigger altogether. Also, if a trigger might affect locking on rows or entire tables that are heavily involved in other transactions, we'll either have to redesign that portion of the database or come up with a way for the trigger to not block other transactions somehow. We've even moved to asynchronous methods when data latency wasn't an issue. > And I'd love to hear more about your workaround for recompiling of Sprocs > from CF! I'll try to get something together soon. > Thanks Adam, this is very valuable! > Aaron I hope it helps, my friend. :) 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 **********************************************************************
