tracker issue : CF-4204693

select a category, or use search below
(searches all categories and all time range)

wierd query performance issue

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/CannotReproduce

Reporter/Name(from Bugbase): Yuliang R. / ()

Created: 06/28/2019

Components: Database, CFQuery

Versions: 2018

Failure Type: Performance Issue

Found In Build/Fixed In Build: 2018.0.04.314546 /

Priority/Frequency: Normal /

Locale/System: / Win 2016

Vote Count: 0

Problem Description:
on a very specific query with specific data input,  the query returns very very slow.   with slightly different but equivalent sql statement, the query returns instant.  

Steps to Reproduce:
notes (Datasource=db2, Time=39320ms, Records=20) in  @ 08:05:54.054

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 and itemID.   some other itemID's work instant.  some other itemID's are slow like this.
it doesn't make sense.

Actual Result:
super slow query depending on inputs 

Expected Result:

Any Workarounds:



Hi Yuliang, I have tried reproducing this issue with the database query that you have shared but I am unable to repro it. I need more information to investigate this issue further: # Are you using ColdFusion's driver or any other third-party database driver? # It is happening with specific type of data? # Can you share the isolated test case that we can use to repro this issue? # Have you tried executing the same query directly on the database server, if yes, then what was the outcome? -Nimit
Comment by Nimit S.
30977 | July 01, 2019 09:37:22 AM GMT
1) using CF out of box driver for SQL Server 2) yes i think it's somehow data tied. I have a gut feeling it's something in the driver but unable to provide any evidence of such 3) i'll play around and see what i can isolate 4) the query and its variants run instantly when i execute them directly on the mssql studio console.
Comment by Yuliang R.
31009 | July 09, 2019 09:40:22 PM GMT
Yuliang, just checking,,, anything new from any isolation tactic you might have tried? will be awesome if you can share the dataset as well. you could also try enabling spy logging for dig deeper.. instructions below: To enable Spy and Snoop add the following to the connection parameters to your URL. For Snoop : ddtdbg.ProtocolTraceEnable=true;ddtdbg.ProtocolTraceMaxline=16;ddtdbg.ProtocolTraceLocation=Snoop;ddtdbg.ProtocolTraceShowTime=true; If connecting to DB/2, please also include the following option: ddtdbg.ProtocolTraceEBCDIC=true For Spy : spyAttributes=(log=(file)Spy.log;timestamp=yes); This will create log files SnoopX.out and Spy.log in the application's working directory. There could be more than 1 SnoopX.log files.
Comment by Piyush K.
33331 | March 24, 2020 10:15:12 AM GMT
Yuliang, Pls feel free to leave a comment here or email me, at the details requested in my last note, if this still needs to be pursued. closing. as per nimit's note, he's already tried unsuccessfully to reproduce the issue.
Comment by Piyush K.
33332 | March 29, 2020 07:31:20 PM GMT