Skip to main content
Participating Frequently
June 28, 2019
Question

CF bug? or maybe jdbc driver something?

  • June 28, 2019
  • 3 replies
  • 369 views

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

    This topic has been closed for replies.

    3 replies

    BKBK
    Community Expert
    Community Expert
    June 29, 2019

    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?

    Inspiring
    June 29, 2019

    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.

    WolfShade
    Legend
    June 28, 2019

    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,

    ^ _ ^