CF bug? or maybe jdbc driver something?

[yuliangr39032863]

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

[WolfShade]

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.

V/r,

^ _ ^

[paule12345]

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.

[BKBK]

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?