where clause

[sardinka2]

I have the following where clause...Right now it is slow.It is anyway I can speed up by rewriting differently??
and ( b.ID1 = a.ID1
or b.ID2=a.ID2
or (Replicate ('0',11-len(b.ID3)) + b.ID3)=a.ID3)
AND ( a.ID1 = @ID
or (Replicate ('0',11-len(a.ID3)) + a.ID3)=@ID
or a.ID2=@ID)

 

[obislavu]

That is equivalent to the following which is faster:
and (
a.ID1 = @ID
or (Replicate ('0',11-len(a.ID3)) + a.ID3)=@ID
or a.ID2=@ID
and EXISTS
(select * from b where b.ID1 = @ID
or (Replicate ('0',11-len(b.ID3)) + b.ID3)=@ID
or b.ID2=@ID
)
)

-obislavu-

 

[ESquared]

- Is the first and clause the criteria for a join? How about putting those criteria in the FROM clause?

- The construction

Right('00000000000' + b.ID3,11)

might be faster than the Replicate and Len way you have.

- Can you update your table b so that all the leading zeroes are already present in ID3? Then you don't have to calculate it for the where clause.

- For the second part of the clause, you could try:

@ID in (a.ID1, b.ID1, Right('00000000000' + a.ID3,11))



-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)