CODEWITHSUNDEEP
sql-servercoldfusion
CFML_Developer
CFML_Developer

Reputation: 1605

cfquery taking much longer

A simple query like

SELECT TOP 1 ColName FROM <TABLE> WITH (NOLOCK) WHERE SomeFieldName='xxxxx'

is taking lot of time in CF. Same query when run in management studio, runs without any issues. It has the index on SomeFieldName. I have FusionReactor installed. It shows it taking 25-35 seconds. The query plan in dev doesn't suggest anything. What is going wrong here? Could indexes an issue? (I do not have access to them in prod).
ColdFusion 2018. Edit: Same queries run alright on CF2016

Upvotes: 0

Views: 242

Answers (1)

CFML_Developer
CFML_Developer

Reputation: 1605

We finally found the answer for slow running queries though still do not know why does that happen.

The SomeFieldName column is data type varchar. CF2018, somehow is sending varchar parameters as nvarchar to SQL Server. So, indexes does not work correctly. We found that in SQL monitor.

We checked the String Format check box in CF Admin, that checkbox is not enabled, so not sure why it would send varchar as nvarchar. We reset the CF admin setting, checked the box, saved, unchecked again, saved and reset the instance and it started working correctly.

Upvotes: 3

Related Questions