Forum Discussion

aarifshah's avatar
aarifshah
Copper Contributor
Apr 08, 2024

Improve query performance

I  have a stored procedure. It returns search results from involved join tables. One of the filter parameter is @vendorId.And, It has been used here : AND (@vendorIdParam IS NULL OR v.VendorId = @vendorIdParam),I want to understand why,  if i am passing value to @vendorId,query runs fast and if that is NULL, query takes around 40 seconds.v.VendorId has a cluster index defined on it.

1 Reply

Sort By
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Apr 09, 2024

    aarifshah 

    AND (@vendorIdParam IS NULL OR ...

    It's an optional parameter, if you pass nothing (NULL) and it should not filter anything.

    But with that less on information it is impossible to say, why it becomes slow.

    Check the execution plan of the SP.

Resources