Grant Fritchey (1/25/2013)
I agree. It sounds like parameter sniffing is leading to issues. I would suggest updating the statistics on the tables being referenced. In addition to the solutions for parameter sniffing, just getting better statistics frequently solves the issue.
I ruled out the parameter sniffing possibility, while within the code I have no such condition (@P1='yyyy-mm-dd' or @P1 is null). I will take the second suggested path, the update of the statistics (involved tables are really big and the customer does not care very much to maintain the database) and inform you.
Thank you all for the contributions and interest.
___________________________________________________________________
Is better shut up and look stupid than open your mouth and eliminate any doubt