Improve query performance

[aarifshah]

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.

[#2]

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