Lee Crain (10/25/2012)
This observation may or may not be helpful.I couldn't help but notice your WHERE clause:
"WHERE
a.P_STATUS <> 'R'
and b.TICKET_NUM is not null
and b.TXN_DT is not null"
All NOT comparisons cause scans instead of seeks. If you can find some way to avoid performing NOT comparisons, it would likely improve the performance of your query, if you have properly implemented supporting indexes. To what degree will depend on the sizes of your tables.
This may have been the case at one time, I do not believe this is the case any longer. It was once argued to use NOT EXISTS, but that would only matter if NULLS are considered. In some simple tests, I am getting the same query plan for a IN (criteria) as a NOT IN (criteria) on a 4 million row table. I think the optimizer in 2005 and up is doing an better job than the old version with regards to this.