Conditional User Defined Function Slows Up Select 1

[EriRobert]

Morning

I have a stored procedure that contains a user defined function that slows considerably the performance of the SP. Extract as below

WHERE 
     dbo.usf_PostCodeRules(@tinPostcodeCheckType, C_Postcode) = 0

The problem is not with the performance dive, which is expected, but the fact that the usf is only necessary if (@tinPostcodeCheckType (input parameter) has a value <> 0.
I tried

WHERE 
     Case @tinPostcodeCheckType
         When 0 Then 0
         Else dbo.usf_PostCodeRules(@tinPostcodeCheckType, C_Postcode) 
     End = 0

but the sp is still slow if @tinPostcodeCheckType = 0.

Is my only option

IF @tinPostcodeCheckType = 0
Begin
      ...
      WHERE
      <no usf check>
End
Else
Begin
      ...
      WHERE 
          dbo.usf_PostCodeRules(@tinPostcodeCheckType, C_Postcode) = 0
End

Which is fast when @tinPostcodeCheckType = 0. Not particularly happy repeating the select with just the usf check different in each.

Is it just the presence of the usf in the select (even though logically never run) that triggers a non-set base run?

Any better approach?

Thanks

Robert

 

[vbkris]

hi,

one more approach could be:


WHERE
(
@tinPostcodeCheckType=0 OR
dbo.usf_PostCodeRules(@tinPostcodeCheckType, C_Postcode)
)



Known is handfull, Unknown is worldfull

 

[EriRobert]

Thanks - yes that is quick, but I'm now confused - what is the difference between your suggested 'Or' and my 'Case'? Isn't a case a different format or?

 

[vbkris]

hmm, maybe the way the case iw written is wrong, try this:

Case When @tinPostcodeCheckType=0
Then
   0
Else
 dbo.usf_PostCodeRules(@tinPostcodeCheckType, C_Postcode) 
End

but to really know the truth, take a look at the query execution plan...

Known is handfull, Unknown is worldfull