0

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....?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
glenho123
  • 579
  • 2
  • 6
  • 21
  • 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 Oct 20 '17 at 01: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 Oct 20 '17 at 01:34
  • No. Any client application (SSMS or a web app) can incur parameter sniffing – Nick.Mc Oct 20 '17 at 01:35
  • @Nick.McDermaid - Your suggestion is correct. If you post this as an answer I will mark it as correct. – glenho123 Oct 20 '17 at 02:59
  • Glad I could help. I might just post as a duplicate if thats OK – Nick.Mc Oct 20 '17 at 03:53

1 Answers1

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".)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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... – glenho123 Oct 20 '17 at 01:43
  • @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. – Gordon Linoff Oct 20 '17 at 01:58
  • 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 – glenho123 Oct 20 '17 at 02:07