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?

mts1701Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tacobell777Commented:
Network performance??
0
INSDivision6Commented:
What is the result set you are getting?  Is it just a single row or a lot of them?

0
umbraeCommented:
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.

-Umbrae
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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.
0
Tacobell777Commented:
network congestion...
0
pinaldaveCommented:
Hi mts1701,
 
may be your connections jdbc in cfadmin had some problem or operating system was slow for a moment ...

Regards,
---Pinal
0
mrichmonCommented:
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:
http://www.experts-exchange.com/Community_Support/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.