tracker issue : CF-4204693

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

wierd query performance issue

| View in Tracker

Status/Resolution/Reason: To Track//PRNeedInfo

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

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 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:
<50ms

Any Workarounds:

Attachments:

Comments:

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