• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 138
  • Last Modified:

Difference in Query Performance

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?

1 Solution
Network performance??
What is the result set you are getting?  Is it just a single row or a lot of them?

Its possible the query analyzer is optimizing an inefficient query before processing it. Usually I believe that SQL is supposed to be optimized by default, but perhaps this analyzer is doing something that SQL does not.

Just a thought. If you want to post the query, we could probably take a look at it for you.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mts1701Author Commented:
Well, I rebooted the webserver, and the sql server... and the problem went away.   Perhaps it was network performance.  I still don't know for SURE what caused this, and as a result, I don't know precisely what to watch out for in the future.  If anybody has any further thoughts I'd welcome them.
network congestion...
Hi mts1701,
may be your connections jdbc in cfadmin had some problem or operating system was slow for a moment ...

I would guess part of the problem could also have been related to CF 5.  That also explains why a reboot helped solve it.

Anyway, post here for PAQ/Refund:

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now