• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CF bug? or maybe jdbc driver something?

Community Beginner ,
Jun 28, 2019 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

213

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
community guidelines
LEGEND ,
Jun 28, 2019 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,

^ _ ^

Votes

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
community guidelines
Participant ,
Jun 29, 2019 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.

Votes

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
community guidelines
Community Expert ,
Jun 29, 2019 Jun 29, 2019

Copy link to clipboard

Copied

LATEST

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?

Votes

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
community guidelines
Resources
Documentation