Difference in Query Performance
Posted on 2004-10-21
I've been developing in ColdFusion for 5 years and have never come across this. I have a cfquery that runs poorly for my needs (10 seconds plus)... but when I paste the SQL into Query Analyzer... the same EXACT query runs in less then 2 seconds (I am aware of SQL's caching ability, and have ruled that out as a possible explanation)
Here's the deal... I'm really wanting to understand what's going on here. I'm already aware of a number of different ways to solve this problem (such as creating a stored procedure or maybe even using query parameters) but that's not my goal here. I've literally created thousands of queries, and have never had this happen. Even if the performace of Query Analyzer and my ODBC connection aren't the same, they are always at least comperable.
Why the vast difference?