have a specific query that for unknown reason takes >20sec to load from a CF page but it's instant when ran directly on mssql studio
notes (Datasource=db2, Time=39320ms, Records=20) in @ 08:05:54.054
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT TOP 20 id,productId,itemID,createdOn,message,createdBy,comments,manual,logType,qty,dataid,qtyOnHandAdj
FROM inventoryLog WHERE itemid = ?
AND logType <> 9
ORDER BY createdOn Desc
Query Parameter Value(s) -
Parameter #1(CF_SQL_INTEGER) = 40483
i played around with the TOP N. ONLY TOP 20 takes long. TOP 19,21,25,50 whatever all take milliseconds
i played around with column list.
if i exclude the dataID column, it's instant.
if i alias dataID as something, it's instant.
if i alias dataID as dataID, it's instant.
so it's only bad when TOP 20 and dataID without alias.
in CF2018 update 4
That is an odd and creepily specific issue. The fact that only when TOP 20 vs TOP anything else is particularly head-scratching.
I'd submit a bug to Tracker. With all the details you provided, and even some log entries if there are any that might point to the reason.
^ _ ^
I had a similar problem with a certain query in CF11 using MSSQL. Query was fast in SQL studio but very slow in CF. I don't remember where I found the suggestion, but adding OPTION (FAST 1000) to the end of the SQL statement solved it.
Test by selecting just one column, dataID, but otherwise with exactly the same query.
Is there a difference between times taken by SQL Studio and ColdFusion?