tracker issue : CF-4205369

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

Slow database performance - Windows 2012 vs Windows 2016/2019 - ColdFusion 2018

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/PRHaveInfo

Reporter/Name(from Bugbase): Michael T. / ()

Created: 10/04/2019

Components: Database

Versions: 2018

Failure Type: Performance Issue

Found In Build/Fixed In Build: ColdFusion 2018 Update 3-5 /

Priority/Frequency: Normal / Some users will encounter

Locale/System: English / Win All

Vote Count: 0

We are having an issue when executing either queries or stored procedures with CF2018 running on Windows 2016/2019. The same code running on Windows 2012 runs fine, however when executed on Windows 2016/2019 the execution times are 5-10x (at least) as long. The database server is MS SQL Server 2012. 

Enabling logging on the datasource indicates the slowdown occurs at PreparedStatement[n].execute() . On Windows 2012 it takes 15ms vs 265ms on Windows 2016/2019. We have tested this using CF2018 update 3-5 and recevied the same results, looping the same query up to 200 times. 

Could this possibly be related to nvarchar?  See attached screenshot. I am sure the query could be optimized but we can not determine a valid reason of the slow performance due to the Window OS.

Attachments:

Comments:

Received patch hf201800-4205269 from support, seems to have resolved the issue of slow database performance.
Comment by Michael T.
31719 | October 28, 2019 01:51:58 PM GMT
Problem still remains, tested on Windows 2012 no performance issues. Tested on Windows 2016 db performance issue returns.
Comment by Michael T.
31720 | October 28, 2019 02:04:01 PM GMT
ColdFusion support recommended updating the JDBC driver. We updated the JDBC driver to Microsoft JDBC Driver 7.4 for SQL Server and the performance issue was resolved on Windows 2019. There seems to be an issue with the built in drivers for CF.
Comment by Michael T.
31721 | October 28, 2019 06:37:02 PM GMT
Michael, Can you please share the spy logs for further investigation? -Nimit
Comment by Nimit S.
31870 | November 25, 2019 04:45:39 AM GMT
Michael, In case this issue still stands, do you have the spy logs that was requested earlier.
Comment by Piyush K.
33326 | March 24, 2020 11:11:20 AM GMT
To resolve this issue, we ended up downgrading the servers to Windows 2012 R2 for now. The Windows 2019 test instance we were using no longer existed when the request for the spy logs was made, sorry :( Again, the updating the JDBC driver to 7.4 resolved the issue for us. However, it introduced a different issue with SET NOCOUNT being required (something that would have required a change on the database level).
Comment by Michael T.
33327 | March 25, 2020 01:22:15 AM GMT
Thanks for getting back, Michael. I'll close this. If you fee like the NO COUNT thing is a buggy behavior pls. feel free to log a new bug with the relevant details...
Comment by Piyush K.
33328 | March 27, 2020 07:07:29 AM GMT