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.
