Sometimes explicitly specifying the join hints can help if MSSQL is having problems determining the best execution plan at runtime:
http://blog.pengoworks.com/blogger/index.cfm?action=blog:567 I'd only recommend specifying join hints if you're seeing a huge difference in execution times. The most optimal plan can also change over the lifetime of your database, so while a join hint may help you today, it might cause performance issues down the road as your database and schema changes. It's just something to keep an eye on when you do manually specify join hints. -Dan >-----Original Message----- >From: Jochem van Dieten [mailto:[EMAIL PROTECTED] >Sent: Thursday, December 28, 2006 2:58 PM >To: CF-Talk >Subject: Re: cfqueryparam DECREASES performance? > >Greg Luce wrote: >> OK, I must have something wrong here. I've only heard good things about >> cfqueryparam on this list for both security and performance. A client >sent >> me an ugly report that times out for them. I spent an hour going through >it >> and applying cfqueryparams to each variable in the many queries with >> appropriate datatypes. I threw a cfsetting tag in to increase the request >> timeout and the report runs in roughly about 512687 ms, restarted MSSQL >> server and CFMX7, then with the cfqueryparams the same query that was >> running in 5282ms in the old code, now takes 15094ms. > >Happens on occasion. The most common cause is that the database is unable >to optimize the execution plan when the cfqueryparam is present. See >http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx for more >details on the (re)compilation and optimization strategies in MS SQL Server >or put the query and the execution plan with and without the cfqueryparam >online somewhere. > >Jochem > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265464 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
