passing multiple values with comma seperation in cfqueryparam causing issue. it is paasing as index scan instead of index seek

Question

I have a query where it passes the list value with comma separation using cfquery param. instead of index seek it is using index scan. I observed this is because of nvarchar 4000.

But the same query works in other environments, As we have 5 environments, CF version (exact), and DB version (exact)for all the environments. In 3 of the environments, the query works fine. In 2 environments we are getting 504 - Time out error. Facing this issue only for tracknumber condition on where clause.

It may not be Code issue. Is there any specific setting in CF Administrator for cfqueryparam or the setting we missed that may be causing this issue?

Code is below

SELECT *
FROM 
    PSOPackage PP with(nolock)
    JOIN PSO PINV with(nolock) ON PINV.PSOID = PP.PSOID 
    JOIN File F with(nolock) ON F.FileID = PINV.FileID 
    JOIN Company CY with(nolock) ON F.companyID = CY.companyID  
    left JOIN Package E ON PP.PackageID = E.PackageID
WHERE   
        <cfif form.PSOID neq 'None' and form.PSOID neq ''>
            PP.PSOID in 
                ( <cfqueryparam value="#form.PSOID#" cfsqltype="cf_sql_varchar" list="true" separator=","> )
        <cfelse>
            tracknumber  in 
                ( <cfqueryparam value="#form.tracknumber#" cfsqltype="cf_sql_varchar" list="true" separator=","> )
        </cfif>
ORDER BY pp.tracknumber, CY.abbr, f.file_Dt DESC

Tried passing the exact value like:

<cfelse>
    tracknumber  in 
        ('1V0Y780E0352033914','1M0Y780E0331023829','1S0Y780E0326482481','1P0Y780E0320555454',1X0Y770B0329555454 )
</cfif>

This works fine, With no error.

Answer 1

If you run this query with an execution plan, you'll see all the problems with this approach. You're sending in a list of strings that look like numbers and trying to compare those strings with numeric values. The other issue is that you could run into a limitation with the IN clause, which limits the number of items it can process from a list.

Instead, convert the list to a temp table using a SQL function. You can define a primary key, insert the list values as INT, and add an index to that lone column. Change the IN to a JOIN against that table. This approach bypasses the eventual IN clause limitation and should speed up the query considerably.

When you define a temp table, use the local definition #tempTable and not the @tempTable approach.

I see you've got logic to run the query based on either a list of ID numbers or strings. You will need to define the temp table based on those conditions as PSOID is a list of INT values, and tracknumber is a list of strings.

For PSOID, it should look something like this:

CREATE TABLE #TEMP_PSOID
(
    PSOID int PRIMARY KEY
)
CREATE CLUSTERED INDEX IX_PSOID ON #TEMP_PSOID (PSOID);

INSERT INTO #TEMP_PSOID
SELECT *
FROM split_string(<cfqueryparam value="#form.PSOID#" cfsqltype="cf_sql_varchar" list="true" separator=",">);

SELECT *
FROM
    PSOPackage PP with(nolock)
    JOIN PSO PINV with(nolock) ON PINV.PSOID = PP.PSOID
    JOIN File F with(nolock) ON F.FileID = PINV.FileID
    JOIN Company CY with(nolock) ON F.companyID = CY.companyID
    left JOIN Package E ON PP.PackageID = E.PackageID
WHERE 
    INNER JOIN PP.PSOID = #TEMP_PSOID.PSOID
ORDER BY pp.tracknumber, CY.abbr, f.file_Dt DESC

DROP TABLE #TEMP_PSOID;

The split_string SQL function depends on your version of SQL Server.

I would create two stored procedures and call each one depending on the condition in your WHERE statement. But that may be more than you need at the moment.

Answer 2

Finally, the issue has been resolved. We have the Setting in ColdFusion Administrator in Datasource Setting. CF2018 somehow is sending varchar parameters as nvarchar to SQL Server. So, indexes do not work correctly. We found that in the SQL monitor.

We checked the String Format check box in CF Admin, that checkbox is not enabled, so not sure why it would send varchar as nvarchar. We reset the CF admin setting, checked the box, saved, unchecked again, saved and reset the instance and it started working correctly.