Something else came to mind regarding Stored procs. A lot of people use them as sort of an "evaluate" function for an SQL string. They pass in a string variable or a series of them and then build an SQL statement - then use "sp_executeSql( )" to execute them. Or perhaps they switch off on an order by or group by statement. many times these approaches will degrade rather than enahance performance and they would have been better off with traditional CF Query. Don't get me wrong - I'm a big fan of stored procs - they are just not a panacea. I have tip from Builder.com on the subject on my blog - regarding the "with recompile" option and how it affects pre-compiled execution plans
http://blog.mxconsulting.com/mxc-blog/archives/entries/B17FBDFD-EBB8-DFFC-87 E24B10A97A0BA2.shtml -mk -----Original Message----- From: Mark A Kruger [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 8:41 AM To: CF-Talk Subject: RE: Database performance - Java Q Chris, If the database is under too much of a load the first place to look is the database and database code. Migrating to CFMX might aleviate some issues if the database queries perform better with the JDBC drivers than the current drivers - so in that sense "Java" is helping you. But that is what testing is for. You can of course implement various caching techniques to improve the load on the db server. All the items you listed below (EJB's CFX tags, java classes) are application based approaches and can indeed help you improve performance - or they can add tremendous overhead. It's impossible to tell without identifying the real bottlenecks in your system and then reviewing all the possible performance enhancements. I would also say that Stored procedures are not the reason your db is performing poorly. In general (note: "in general") stored procedures tend ot enhance the DB's ability to pull execution plans from the cache and hence they remove some of the load from the db server. Obviously that depends on the contents of the stored procs and how they are written. -Mark Mark A. Kruger, CFG, MSCE www.cfwebtools.com www.necfug.com http://blog.mxconsulting.com -----Original Message----- From: Chris Bestall [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 7:34 AM To: CF-Talk Subject: Database performance - Java Q What is the best way to add performance to an application that is currently utilizing a ColdFusion application server (CF5 -soon to go to MX) and a database server (SQL Server 2000). The database server is under too much of a load currently as all transactions are stored procedures. I've heard that Java can be utilized. My question is would this be the best route to take? Will it improve performance, as that is very important. What methods are best suited for this task? EJB's, CFX tags, java classes? Thanks in advance CB ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183866 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
