>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?
Michel,
Putting timing aside, does this query really give you what you want? To me it looks like Invoice join has no role other than checking if the client has an invoice with that amount (if a particular client has 2 or more matches?). Maybe a query like this would be more appropriate:
set transaction isolation level read uncommitted
SELECT Numero
FROM Client
WHERE
NoEntity=@NoEntity AND
Fic_Date < @Fic_Date AND
Loss_Code=@Loss_Code AND
CType=@CType AND
Numero in
(SELECT NoClient FROM Invoice WHERE Invoice.Amount=@Amount)
Cetin
