Optimizing Stored Procedure
-
Actually, why do You join paymentprocessing.dbo.recon at all in this query!?
I can see no information in this table being used anywhere in the query, or am I just blind ???
Since it is a left join, You will get all the rows from paymentprocessing.dbo.cctrans & paymentprocessing.dbo.ccsettle
that are joined : ccs.number = cct.number, and paymentprocessing.dbo.recon has no use in this query..
wonder... wonder...
/rockmoose
You must unlearn what You have learnt -
Looks like because of
where
r.ccrespid is null
Wanting I assume to know where there is no data for a particualr item in the recon table.
There all alternate ways to do this but it is a matter of what is fastest.
-
Right Antares, didn't click at first.
The left join could be replaced with an exists clause:
where not exists( select * from paymentprocessing.dbo.recon r where r.ccr_trxno = cct.number and r.ccrespid is not null )
Don't know if this would be faster though...
/rockmoose
You must unlearn what You have learnt -
Like I said there are alternatives and that is one. There is various things that need to be tested and I don't feel the difference between not exists and doing join then where is null is the issue. In general I find the join and where is null is faster that not exists in most cases so I am working under the assumption something else is the slow down.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply