Old threads die hard...
One point that Jeff didn't bring up, but Hal recently did (subject:
Documentation System for Cold Fusion), is that by using stored procedures,
the actions and intricacies of the database are removed from my
slash-and-burn CF coding when I'm on a rampage. I can yell at my database
lackey, "Boy, make me a stored procedure named blah that takes a
blah-blah-ID and returns 2 blah-blah-blah recordsets formatted like
blah-blah-blah-blah. And make it snappy, dammit!" I can go off and create
the query or action Fuse, write the cfstoredproc call to go in it, and be
done with it. I'm not concerned about how the database boy gets it to me,
only that it happens. He's got his job, I've got mine.
And no, I'm not going to spend time becoming my own database boy. I'd rather
figure out how to make the Studio Debugger work (or watch paint dry).
If your database supports stored procesdures, you should be using
cfstoredproc above cfquery. The database should live in its own world and
the CFML in it's own world.
Now you smarty-pants people might bring up things like the cachedwithin
attribute of cfquery. Well by focusing your concentration on CF instead of
Quake III Arena, you can figure out plenty of much better ways to store
returned resultsets in memory-resident application-scoped lists, variables,
queries, etc than the "easy" way out of cachedwithin. And if you REALLY put
forth some effort, you can stick the query resultsets into nested structures
which are fastER than nuts.
See you in September,
Nat Papovich
ICQ 32676414
"Whatever you do may seem insignificant,
but it is most important that you do it." -M. Gandhi
-----Original Message-----
From: Jeffrey Marsh
Sent: Saturday, August 26, 2000 12:41 PM
To: '[EMAIL PROTECTED]'
Subject: RE: CFStoredProc
Absolutely you see a performance difference. Think about the path the query
must take.
With <cfquery> it goes through ODBC (no speed demon) or OLEDB, SQL gets hold
of it and has to create a query plan (akin to compiling the query), execute
the query, send the results back through ODBC or OLEDB.
With Stored procedures, the SP call w/ parameters goes through ODBC or
OLEDB, SQL receives the call, executes the SP, sends the results back
through ODBC or OLEDB.
With stored procedures you avoid SQL having to compile it first. When you
create an SP, the query plan is already stored and the call simply has to
execute it. The performance difference you see gets more dramatic the more
complicated your queries are. Of course the nice thing about SPs is that you
can perform very complicated processing with only one trip to the database.
Using <cfquery> usually requires several trips to the database and in-line
processing that SQL Server could do faster. Take as few trips to the
database as possible and make SQL Server do as much of the work as you can.
--Jeffrey
-----Original Message-----
From: Kevin Bridges [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 18, 2000 8:19 AM
To: [EMAIL PROTECTED]
Subject: RE: CFStoredProc
I'd like to expand on this question and ask when you will actually start to
see a performance difference between using a stored procedure and a query?
I'm guessing that you need to get a bit more complex than a standard select
statement to see a difference?
-----Original Message-----
From: Noel Flatters [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 18, 2000 10:53 AM
To: [EMAIL PROTECTED]
Subject: CFStoredProc
I was wondering if anyone knows of any performance advantages/differences
when invoking a stored procedure using CFSTOREDPROC vs CFQUERY??
Cheers | Noel.
Noel Flatters
tdgINTERACTIVE | agribiz.net
(e) [EMAIL PROTECTED]
(p) 519.821.5787 ext. 5232
(p) 1.800.794.1773 ext. 5232
(f) 519-821-4868
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/fusebox or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
