Highlighted

CF bug? or maybe jdbc driver something?

Community Beginner ,
Jun 28, 2019

Copy link to clipboard

Copied

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

Views

161

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CF bug? or maybe jdbc driver something?

Community Beginner ,
Jun 28, 2019

Copy link to clipboard

Copied

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

Views

162

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jun 28, 2019 0
LEGEND ,
Jun 28, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 28, 2019 0
Explorer ,
Jun 29, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 29, 2019 0
BKBK LATEST
Adobe Community Professional ,
Jun 29, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jun 29, 2019 0