Read the following article on stored procedures in SQL server. It covers how to optimise SQL servers caching mechanism for stored procedures.
http://www.sqlservercentral.com/columnists/bkelley/procedurecache_printv ersi on.asp The site also seems to have a good coverage of other SQL server issues, although this is the first article I have read there. Jared Clinton NEC Australia -----Original Message----- From: Jason Powers [mailto:[EMAIL PROTECTED]] Sent: Monday, 25 February 2002 9:31 AM To: CF-Talk Subject: RE: Writing Reports - Query vs StoredProc Amanda, I use stored procedures as much as possible, due to the fact that stored procedures tend to run faster because the server, unless I'm mistaken, creates a run path the first time you run a query/stored procedure, and then each time after, will refer to that run path, which speeds things up. Perhaps someone else can lend more detail about this process, I admit, I'm not an expert. Unless you have a query for which you need to write dynamic sql statements (infrequent, but possible), you will probably see a performance gain by using stored procedures. All of the reports I write, with the exception of QBE style reporting, are written as stored procedures. Jason Powers Certified ColdFusion 5 Developer Certified Flash 5 Developer Fig Leaf Software -----Original Message----- From: Amanda Stern [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 24, 2002 5:41 PM To: CF-Talk Subject: Writing Reports - Query vs StoredProc Hello, The application I am working on has about 10 reports that are being run every month. The queries for these reports are written in the CFML page using <CFQUERY>. I was wondering if it would be better(execution timewise) to write a stored procedure on the MS SQL side and calling it in the CFML page. The few applications that I have seen have not used stored procedures...does anyone out there use stored procs for report writing...is this not common practice??? Thanks! __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
