Skip to main content
Participating Frequently
Jun 28, 2019 1:21 PM UTC
Question

CF bug? or maybe jdbc driver something?

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 is closed to new replies. Start a new post to keep the conversation going.

    3 replies

    BKBK
    Community Expert
    Community Expert
    Jun 29, 2019 1:04 PM UTC

    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?

    Translate
    Inspiring
    Jun 29, 2019 12:25 PM UTC

    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.

    Translate
    WolfShade
    Legend
    Jun 28, 2019 1:35 PM UTC

    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,

    ^ _ ^

    Translate