portal entry

select a category, or use search below
(searches all categories and all time range)

Same Query Running Extremely Slower on CF 2018 vs CF 2016

| View in Portal
February 08, 2019 09:35:29 PM GMT
<p>Same Query running 17x slower on CF 2018 than CF 201</p>
<p>The post <a rel="nofollow" href="https://coldfusion.adobe.com/2019/02/query-running-extremely-slower-cf-2018-vs-cf-2016/">Same Query Running Extremely Slower on CF 2018 vs CF 2016</a> appeared first on <a rel="nofollow" href="https://coldfusion.adobe.com">ColdFusion</a>.</p>
Labels: Application Performance, Performance, Question, 2018, application performance, cfquery, performance, question


From the related discussion on the Slack team, I'll add the detail that you are querying against a SQL Server. My only thought would be to use the SQL Server Profiler to trace the queries coming from just those two ColdFusion servers and compare the actual SQL statements being passed.  It's possible ColdFusion 2018 is using a newer JDBC driver for SQL Server, and it may have an issue. Also, are you using the SQL Server driver that comes with ColdFusion, or the Microsoft JDBC driver (using the "Other" driver type in CF Administrator)?
Comment by Carl Von Stetten
1654 | February 08, 2019 10:10:03 PM GMT
It's probably something to do with casting nvarchar to varchar, a nvarchar value won't use a varchar index, try casting the paramater to the same datatype as the index
Comment by zac.spitzer
1657 | February 09, 2019 08:08:25 AM GMT
Justin, I realize you feel the only difference is the cf version. I would bet it is not. What else could differ (and that you should check and report)? <ul> <li>Are the datasource definition characteristics identical? Especially the username and password, and and any query string args?</li> <li>Are the db and server being talked to really exactly the same?</li> <li>Are both cf versions running as the same service user? If the dsn has no username, then cf uses the cf username for a trusted sql server connection</li> <li>Are all cf admin settings the same? Not all would matter, or course, but assess any that differ</li> <li>Does the troubled machine have exactly the same anti-virus implemented as the other? And are they identically configured (in terms of any exceptions, especially)?</li> <li>Do you have any cf monitoring on each? Are they the same monitoring, and configured the same? (obviously the cf2018 pmt is not available on anything but 2018)</li> <li>When you say the query is slower, do you have proof that the slowness is really in the query? Like you are dumping the cfquery executiontime? Or could it be the whole page?</li> <li>And do you have anything monitoring the query from the sql server side? Carl mentioned profiler, but there are other means to watch query executions time in sql server, live or in the recent past.</li> </ul> Those are just a few key things that may get you started.
Comment by Charlie Arehart
1660 | February 10, 2019 08:33:44 PM GMT
Thank you all for the valuable feedback and suggestions. I'll explore these and reply back.
Comment by Justin Cook
1661 | February 10, 2019 09:03:53 PM GMT
Justin, did you ever come to resolution? Did any of the below help?
Comment by Charlie Arehart
2079 | June 04, 2019 04:01:04 PM GMT
Never really did, no. However, it hasn't caused any sort of major work issues, so we moved on from the issue.
Comment by Justin Cook
2085 | June 04, 2019 08:53:26 PM GMT
Justin, did you ever get to the bottom of whatever was the problem? If not, did you get to consider all the questions I asked? And if you did resolve it, did it prove to not be a "cf2018" issue after all? Inquiring minds want to know. :-) (Someone today pointed me to this post of yours asking if I knew of how things turned out. I do not.)
Comment by Charlie Arehart
2206 | July 31, 2019 06:58:44 PM GMT
Ah, ignore my request, Justin. I was looking for updates at the bottom (as replied to older messages), and I see now that instead I had asked as a new comment in June, and you answered--and these are up here at the top.
Comment by Charlie Arehart
2205 | July 31, 2019 06:59:59 PM GMT