SQL Query takes long time with parameter check [duplicate]

Question

I have a SQL query which is taking a really long time based on a parameter check....

The query it self is part of a stored procedure used for a search screen. Basically you fill out a form with a whole lot of text/combo boxes to search from. I have multi-select combo boxes that will pass through multiple values for the same field in way of a delimited field.

The following is a cut down version of the query to illustrate my problem...

select o.id, o.createdBy into #results from jmsTransOther o where o.(WorkOrderId IN (SELECT intValue FROM dbo.fn_SplitInts(@WorkOrderIds, ',')) <strong>OR @WorkOrderIds = ''</strong>)

I quite often use this parameter check OR @WorkOrderIds = '' which basically means if this is not true then perform the other side of the statement.

This works quite well in most cases but for some reason with this fn_SplitInts function which is basically converting the delimited list into a table and then performing a "IN" statement is taking a really long time.

This table has about 200,000 records in it - currently this takes around 40 seconds to search. However, if I remove the parameter check i.e. OR @WorkOrderIds = '', then it takes less than a second.

I can work around it but just wanted to know what is going on here....?

This is almost definitely a parameter sniffing issue. The short solution is to simply add WITH RECOMPILE to your stored procedure. I suggest you do some research on parameter sniffing in general though, just so you understand whats going on — Nick.Mc
– Nick.Mc, Commented Oct 20, 2017 at 1:24
@Nick.McDermaid ok didnt think of that. I have had this issue before. However, I am getting this same issue when just testing the query in a query window isolated by itself? Does this now exclude it from a parameter sniffing issue?? — glenho123
– glenho123, Commented Oct 20, 2017 at 1:34
No. Any client application (SSMS or a web app) can incur parameter sniffing — Nick.Mc
– Nick.Mc, Commented Oct 20, 2017 at 1:35
@Nick.McDermaid - Your suggestion is correct. If you post this as an answer I will mark it as correct. — glenho123
– glenho123, Commented Oct 20, 2017 at 2:59
Glad I could help. I might just post as a duplicate if thats OK — Nick.Mc
– Nick.Mc, Commented Oct 20, 2017 at 3:53

Gordon Linoff · Accepted Answer · 2017-10-20 01:28:49Z

0

I might suggest this formulation:

select o.id, o.createdBy into #results from jmsTransOther o where o.WorkOrderId IN (SELECT intValue FROM dbo.fn_SplitInts(@WorkOrderIds, ',')) union all select o.id, o.createdBy from jmsTransOther o where @WorkOrderIds = '';

The or is probably messing up the statistics. (I would also use NULL rather than the empty string to mean "all of them".)

answered Oct 20, 2017 at 1:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

glenho123 Over a year ago

Unfortunately I have about 6 of these same parameter checks for different criteria in the where condition which would mean this solution would get quite messy...

Gordon Linoff Over a year ago

@GlenHong . . . It is only possible to answer the question that you ask. If you have another question, then ask it as a new question. Editing this question will only server to invalidate this answer -- which can draw downvotes.

glenho123 Over a year ago

Well there is a question on the last line...I wasnt really looking so much for a solution as I can work around it. I was more looking to some insight as to what was causing the issue....as Nick Mermaid has pointed out what he thinks is the problem

Collectives™ on Stack Overflow

SQL Query takes long time with parameter check [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related