I have this query which is causing a lot of deadlock error messages from SQL Server. When I look at it, every where clause fields and inner join related fields are indexed. But, we have this deadlock situation appearing about 4 times a day. Tonight, I took a look at what could cause this. I have found something interesting by changing the SQL command a little bit. From the SSMS interface, it takes about 2 seconds to return 45 records. This is when all my where clauses values are parameterized. But, one of those where clauses is from a INNER JOIN table. At that specific location, if I remove the parameterized value and put the value as is, the result is instant.

Here is an example:

SELECT Client.Numero FROM Client
 INNER JOIN Invoice ON Client.Numero=Invoice.NoClient
 WHERE Client.NoEntity=@NoEntity AND Client.Fic_Date<@Fic_Date AND Invoice.Amount=@Amount AND Client.Loss_Code=@Loss_Code AND Client.CType=@CType

This would take 2 seconds to return 45 records. But, the following would be instant:

SELECT Client.Numero FROM Client
 INNER JOIN Invoice ON Client.Numero=Invoice.NoClient
 WHERE Client.NoEntity=@NoEntity AND Client.Fic_Date<@Fic_Date AND Invoice.Amount=1000 AND Client.Loss_Code=@Loss_Code AND Client.CType=@CType

I tried to change a lot of things in there. The only way I can have my query to be optimized is by avoiding the parameter on the foreign table. But, I really need to have everything parameterized. What can I do?