[SOLVED] Alternate to 'Except' in SQL with performance

Alternate to 'Except' in SQL with performance

I've a table TableA as in MS-Sql

 TrId   Status
 2345   3
  567   3
  567   0
 2345   0
   99   3
  778   0

Scenario is few TrIds have status as 3 as well as 0 , some have 3 only some 0 only. I need to find TrIds with status only 3.
One of the ways to do is :

Select TrnId From TableA Where flgStatus = 3
EXCEPT
Select TrnId From Tablea Where flgStatus = 0

There are over 100 million records and i don't have enough time window for except, any alternative for this would be appreciative.

Solution

Is the combination (TrnId,flgStatus) unique?

Then you might switch to EXCEPT ALL, similar to UNION ALL which might be more efficient than UNION because it avoids the DISTINCT operation.

Another solution which accesses the base table only once:

Select TrnId 
From TableA Where flgStatus in (0,3)
group by TrnId
having MIN(flgStatus) = 3