index seek while calling the UDF itself, index scan while wrapping the UDF in sp
I have a UDF that does most of the joins and when I run it, it read about 100-150 pages (from sql profiler) but if I wrap this UDF in sp then the sp will do the read about 243287 p开发者_C百科ages.
Furthermore, the UDF itself performs index seek but the sp performs index scan on one of the searched columns.
Can anyone please advise?
Based on your comments about (custid = @param1 OR @param1 is null)
vs. (custid = @param1)
, I think Gail Shaw's blog posting on Catch-all queries will provide some insight into what you're seeing here. Basically, I think you're getting an execution plan cached for the @param1 is null
case that then performs poorly when you pass in a real @param1
.
精彩评论