Same Query Running Extremely Slower on CF 2018 vs CF 2016

February 8, 2019
Newbie 1 posts
Followers: 0 people
8

Same Query Running Extremely Slower on CF 2018 vs CF 2016

Newbie 1 posts
Followers: 0 people
February 8, 2019

I’m trying to scour the resources I have available to see if I can find the solution to this issue or at least some more pointers on where to look.  So we are moving a website from one server to another.  The machines are comparable and the network is the same network.  The only major difference is we’re going from CF 2016 to CF 2018.  On ’16, a query we have runs in about 1 second.  The same query on the ’18 machine runs in about 17 to 30 seconds.  I’ve been looking for ways to ensure that my ColdFusion on the new server is optimized.  The old server hasn’t had any special optimizations.  So I’m wondering if something changed about the way CF processes queries from 16 to 18?  And does anyone have any pointers or suggestions?

Thanks!

Comments (8)
2019-07-31 18:58:44
2019-07-31 18:58:44

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.)

Like
(1)
>
Charlie Arehart
's comment
2019-07-31 18:59:59
2019-07-31 18:59:59
>
Charlie Arehart
's comment

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.

Like
2019-06-04 20:53:26
2019-06-04 20:53:26

Never really did, no. However, it hasn’t caused any sort of major work issues, so we moved on from the issue.

Like
2019-06-04 16:01:04
2019-06-04 16:01:04

Justin, did you ever come to resolution? Did any of the below help?

Like
2019-02-10 21:03:53
2019-02-10 21:03:53

Thank you all for the valuable feedback and suggestions. I’ll explore these and reply back.

Like
2019-02-10 20:33:44
2019-02-10 20:33:44

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)?

  • Are the datasource definition characteristics identical? Especially the username and password, and and any query string args?
  • Are the db and server being talked to really exactly the same?
  • 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
  • Are all cf admin settings the same? Not all would matter, or course, but assess any that differ
  • 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)?
  • 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)
  • 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?
  • 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.

Those are just a few key things that may get you started.

Like
2019-02-09 08:08:25
2019-02-09 08:08:25

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

Like
2019-02-08 22:10:03
2019-02-08 22:10:03

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)?

Like
Add your comment