Charlie, If you're using MSSQL and <cfqueryparam />, sometimes running a query directly in SSMS may lead to misleading information. I wrote a blog issue a while back that talks about one issue:
http://blog.pengoworks.com/index.cfm/2010/12/8/Performance-issues-with-cfqueryparam--on-first-execution-when-using-Microsoft-SQL-Server Essentially what can be happening is that MSSQL is having performance problems running sp_prepexec to analyze the best way to run your SQL, so the problem isn't running the query, it's with MSSQL trying to figure out the best way to run the query. You might also check out this lengthy article: http://www.sommarskog.se/query-plan-mysteries.html -Dan On Thu, Apr 7, 2011 at 7:40 PM, Charlie Stell <charlie.st...@gmail.com>wrote: > > So this was a weird one... the error is resolved, but I was wondering if > anyone could explain what was causing my error. > > I had a query that was timing out (its one of those nice long monster > queries - so I'm not pasting it in its entirety) - but when I copied/pasted > out of the cfcatch.sql (from error thrown on timeout) into SQL Query > Analyzer - or another template, it completed in under a second. > > In the query there was a join that looked like this - > > LEFT JOIN tableA on ColX > <cfif condition...> > = ColY > <cfelse> > = ColZ > </cfif> > > Changing it to either of the following resolved the issue > Solution 1: > LEFT JOIN tableA on ColX <cfif condition...> > = ColY > <cfelse> > = ColZ > </cfif> > > Solution 2: > <cfif condition...> > LEFT JOIN tableA on ColX = ColY > <cfelse> > LEFT JOIN tableA on ColX = ColZ > </cfif> > > So I'm assuming there was something going on related to the carriage return > following "ColX " - and in addition to the two solutions above, the process > of copy-pasting also must have resolved it. > > If it makes any difference - I'm using SeeFusion for monitoring and so all > my datasources use: > JDBC URL: > jdbc:seefusion:jdbcwrapper:{jdbc:macromedia:sqlserver://server1:1433; > databaseName=dbname; SelectMethod=direct; > sendStringParametersAsUnicode=false; MaxPooledStatements=1000}; > Driver Class: com.seefusion.Driver > > Anyone have any idea on the root problem? > > Thanks! > charlie > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343623 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
