> The problem with using stored procedures is that if you
> decide to switch databases, you have to re-write all of them
> because almost all database vendors use different stored
> procedure code and structure. By writing the queries in the
> program itself (included as a query file or in the actual
> file, whichever), you're not quite as locked into the
> database - as long as you're using a relational database that
> supports SQL standards and you write standard SQL, you should
> be ok to plug and play databases as you please - stored procs
> screw that all up.
That's certainly true, but in the case of most complex applications, you're
not likely to switch the database on a whim, hopefully. I've usually found
that the cost of using proprietary SQL extensions in writing stored
procedures is far outweighed by the gains in performance and application
partitioning.
> Another problem with putting too much business logic in
> stored procedures is that you put too much strain on the
> database if the site is hit hard - I worked on a site that
> gets upwards of a million hits a day (www.casio.com) and
> using stored procedures to do too much business logic would
> have bogged down the database servers far too much to make
> it worthwhile. Instead, we load balanced it all onto several
> really fast ColdFusion servers and they do the business logic.
While I can imagine that this is possible, I'd argue that in most cases,
you're going to get better overall performance by placing all data
manipulation code (including the parts of your business logic that involve
data manipulation) in stored procedures. I think you're more likely to end
up queueing database requests from your CF servers with lots of little
queries that bring back recordsets for further manipulation in CF than you
are to simply let the database do what it does best. At least, that's been
my experience so far. I guess that if you get to the point where the stored
procedures are causing problems, you'd be better served with an object tier
between your database client and server, rather than simply putting data
manipulation logic in CF.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
