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:
WHERE BLAH BLAH BLAH
Can someone offer me some advice on what can cause this or how to find the issue?
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)
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
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?
Ah, sorry. I was thinking of SSMS. I'll run the profiler and get you a result. I can tell you that they are both patched to the most recent version of CF10, update 15 I think? I've tried running the query without CFParam because I thought that could be the issue based on past experience.
Ok, I ran the sql query 3 times from the application and 3 times from studio and the results are below..
Duration directly from SQL server:
Duration from CF Application:
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.
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.
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.
Just an update. It ended up being "something" to do with the CF machine, although still not sure what exactly.
Ended up solving the issue by installing CF on identical hardware and patching to the same windows and CF versions. The site is now running like a champ and loading pages in less than 1 second.
Steve, I have a dynamic filter that I run a query of query on the 45k rows to generate. Once the first option is selected, it takes the distinct rows from the original query were x = selection and there can be 10-12 sub options to narrow down inside of that data. It seems to load much faster this way than by returning to SQL server each time a response is selected.
Thanks for your suggestions. Hopefully we don't have the issue again... whatever it was.