> I know I'm a less sophisticated user than Philip, but it seems TSQL can be
> just about as dynamic as you want it to be. But it's partly about
> where you want the logic, and reducing trips to the server.
You can build Transact SQL commands and call them in a CFQuery - just not as
powerfully as using a SP - I do this when altering table structures (like
adding an Identity field)
TSQL commands are fairly restricted in generated SQL, but some can be
used...
> So my only thing to add to the list of "when not to" is... what about
> cached queries? You can't cache SP output can you? You'd want to
> CFQuery it in this case.
You can use CFQuery to call a stored proc, and thus cache it - but you have
to use sp_exec to do it... also, using CFStoredProc you can get multiple
result sets back, but CFQuery can't... caching adds to the speed, but if
it's a non-cacheable query, then CFStoredProc can add to this a lot...
Don't just expect a SP to run faster than a CFQuery - test it a lot - as I
said earlier, I have one query that is about 10% faster in a CFQuery than in
a SP (using CFStoredProc) as there are so many indexes on the tables...
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
