Although I use stored procedures for about 95% of my database calls, I would
agree that there are some situations in which you might choose not to use
them.
You can do quite a bit of complex programming inside your SPs (e.g., using
Transact-SQL in SQL Server), but you may not know that language. If you
don't have the time or inclination to learn a new language, you might stick
with using CF for some complex processing that could otherwise have been
encapsulated within an SP.
I both agree and disagree with Philip regarding building dynamic SQL. I
cannot think of many cases where you _cannot_ do it in an SP, but if you are
actually building your SQL statement from within the SP, then you lose one
of the performance benefits of SPs (it cannot be pre-compiled).
It is surprising just how much you can do within an SP when you start to
investigate the programming language. There are also a few tricks you can
use to create somewhat dynamic SQL statements that _can_ be precompiled.
I'd strongly encourage anyone who is using a database that supports SPs to
learn the language of their database and challenge themselves to move as
much of the processing as possible into the database. You do end up with
huge performance gains in some scenarios - but with a simple SELECT
statement, it's not such a big deal.
Bob
-----Original Message-----
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: March 29, 2001 3:54 AM
To: CF-Community
Subject: RE: Stored procedures...when would you
> NOT want to use stored procedures to run queries etc. on a database?
>
> Wouldn't a stored procedure almost always run faster than one done through
> CF?
If you're building a completely dynamic bit of SQL, then you just can't do
it in a SP
Also, SPs aren't always faster - I have some SQL where the tables are
Indexed to hell and back - the CFQuery version is faster than the SP by
about 10%, don't ask me why though
Also, also, quite often we have tons of queries in a site and it's easier to
manage without hitting hundreds of SPs than allow the SQL to be updated in
the templates
Also, also, also (this is just getting silly now), you just HAVE to do some
things in CFQuery - like altering a table structure - it'd be silly making a
SP for a one-shot SQL statement
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
