Skip to main content
AaronB1
AaronB1
Participating Frequently
April 9, 2015
Question

Very Slow SQL query - Fast in SSMS

  • April 9, 2015
  • 2 replies
  • 3358 views

We have a very simple query that executes instantly in SSMS, returning about 45,000 rows.  Whenever I run the same query on a CFM page it takes about 47 seconds to execute.

I've tried reindexing, turning it into a stored procedure, etc.. but nothing seems to help.   My other queries are also running a little slower, but not nearly as noticeable because they are smaller queries.
I have an additional CF10 server that seems to run the query perfectly fine on a test page.

The query is pretty much this:

Select X,Y,Z
FROM TABLE
WHERE BLAH BLAH BLAH


Can someone offer me some advice on what can cause this or how to find the issue?

Thanks!

    This topic has been closed for replies.

    2 replies

    S
    Steve Sommers
    Legend
    April 13, 2015

    SSMS is not loading up the entire query result set; for the most part it only loads what is showing on the screen. CF on the other hand loads the entire result set into various Java arrays. If possible have the result set returned in pages and only display a single page of data. If this is not doable, try limiting the columns to only the columns you need and/or have SQL do some of the calculation work for you if you are summing up row data. Without knowing your data or what you are trying to accomplish, this is my best guess as to the problem and how to solve it. Good luck.

    AaronB1
    AaronB1Author
    Participating Frequently
    April 13, 2015

    That doesn't seem accurate Steve.  I can scroll through the entire result set in SSMS if I wanted too.  Also, this problem was not occurring at all a few weeks ago and the query and result set have remained unchanged in that time.  Additionally, I can run the same exact query on our development machine, which is a much slower machine, and get the results pack near instantaneously. 

    S
    Steve Sommers
    Legend
    April 14, 2015

    Then maybe you need to focus on performance monitoring. 45,000 rows is quit a bit of data and if the server is running low on resources or is not doing the "correct" (whatever that is for your app) java garbage collection or heap optimizations then that could lead to the symptoms you describe. Assuming you do find the culprit, I would still put some thought into the necessity of retrieving 45,000 rows of data and maybe do some SQL query refactoring.

    H
    haxtbh
    Inspiring
    April 10, 2015

    Are you sure its definitely the SQL query? Have you dumped the cfquery result to get the sql execution time? Is the server that CF is running on located on the same server the SQL database is?

    Have you tried using the SQL Server profiler to see the query being executed and any issues that may come out of it? (High cpu / reads etc)

    AaronB1
    AaronB1Author
    Participating Frequently
    April 10, 2015

    I did a dump of the result and it had an execution time of nearly 48 seconds.   The same exact query running off the same exact DB, but on a our development CF machine ran it in less than 1.   
    We have a dedicated CF and DB machine, so the web app shouldn't be interfering with any of DB operations.

    Thanks for your help

    H
    haxtbh
    Inspiring
    April 10, 2015

    Does your query use cfqueryparam? Do you get the same result with and without them? A connectivity issue to the DB server could be a factor as well, have you checked connectivity?

    Like I mentioned in the last post, does the sql profiler also show it taking this long to run the query?

    Are you running the same update to CF10 on both servers? what update are they patched too?

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices