portal entry

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

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

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

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

Comment by Justin Cook

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

Comment by Charlie Arehart

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

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

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