SQL Server Query Performance with a lot of data

Question

I have the following query which inserts the results in another table

Select Distinct * From(                        
select t.RuleId ,t.Table3Id,Null as RiskLeveltypeId,                                                       
(case when r.Count>=t.highlimit  then 60 else                                                                            
case when r.Count>=t.mediumlimit then 30 else                                                         
case when r.Count>=t.lowlimitthen 15 ELSE 0 end end end) as Score                                                     
,CreatedUser,GETDATE() as CreatedDate,CreatedUser as LastActivityUser,GETDATE() as LastActivityDate,                                    
t.Table2Id,
t.Table1Id,
CardId,
249 as ClientId,  
t.StmtDate                                             
from ( (select Table2Id,Table3Date ,COUNT(Distinct Table4.[State]) As Count 
from Table3Data 
join Table4  on Table3Data.Table3MerchantDetailId=Table4.Table3MerchantDetailId                                      
where Table3Data.ClientId=249                                                                                                   
Group By Table2Id,Table3Date  
having COUNT(Distinct Table4.[State])>1 
)r 

join

 (Select ar.CreatedUser,ar.highlimit,ar.mediumlimit,ar.lowlimit, ar.RuleId,                                  
t.Table2Id,ar.RiskLeveltypeId, t.Table3Id,t.Table3date,e.Table1Id,                        
ch.CardId,t.StmtDate  
from Table2sData ch 
    join Table1 e on  e.Table1Id=ch.Table1Id and e.clientid =ch.clientid 
    join Table3Data t on ch.Table2Id=t.Table2Id  and t.ClientId=ch.Clientid and     t.run is null
    left join Table5 ar on e.AuditProfileId=ar.AuditProfileId 
    where ar.RuleUsed=1 and e.AuditProfileId= 205  and ch.CardId  = 1       
    and ar.CardId  = 1   and ar.RuleId=23  and t.StmtDate=CONVERT(varchar,'04/02/2015',112)  and t.run is null  and t.ClientId=249 ) t on r.Table2Id=t.Table2Id                                                            
and r.Table3Date=t.Table3Date) 
)r             where r.Score<>0   

Table3Data has 147260 records, Table2sData has 6142 records. The first sub query which counts the number of states results in 270 records, where as the second sub query which is after the join(which selects the limits) results in 124619 records.

This query is taking about 16 minutes to execute. The execution plan shows a 70% cost for hatch match(inner join) for table4. I have a index already on table4 which is as follows:

CREATE NONCLUSTERED INDEX IX_1 ON [dbo].table4 
(
    [ClientId] ASC
)
INCLUDE ( [State],
[table3MerchantDetailId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO 

Please help me with this query!

Answer 1

I was able to reduce the time to 1 second with the following query. I am not sure why this takes 1 second and the previous one took 16 minutes

    select Table2Id,Table3Date ,COUNT(Distinct Table4.[State]) As Count into #temp
    from Table3Data 
    join Table4 on Table3Data.Table3MerchantDetailId=Table4.Table3MerchantDetailId 
    where Table3Data.ClientId=249 
    Group By Table2Id,Table3Date 
    having COUNT(Distinct Table4.[State])>1

 select * from ( Select Distinct * From( 
                                select t.RuleId ,t.Table3Id,Null as RiskLeveltypeId, 
                                (case when r.Count>=t.highlimit then 60 else 
                                case when r.Count>=t.mediumlimit then 30 else 
                                case when r.Count>=t.lowlimit then 15 ELSE 0 end end end) as Score 
                                ,CreatedUser,GETDATE() as CreatedDate,CreatedUser as LastActivityUser,GETDATE() as LastActivityDate, 
                                t.Table2Id,
                                t.Table1Id,
                                CardId,
                                249 as ClientId, 
                                t.StmtDate 
                          from (
                                select  
                                    ar.CreatedUser,
                                    ar.highlimit,ar.mediumlimit,ar.lowlimit, ar.RuleId, 
                                    t.Table2Id,ar.RiskLeveltypeId, t.Table3Id,t.Table3date,e.Table1Id, 
                                    ch.CardId,t.StmtDate 
                                from Table2sData ch 
                                join Table1 e on e.Table1Id=ch.Table1Id and e.clientid =ch.clientid 
                                join Table3Data t on ch.Table2Id=t.Table2Id and t.ClientId=ch.Clientid and t.run is null
                                left join Table5 ar on e.AuditProfileId=ar.AuditProfileId 
                                where 
                                ar.RuleUsed=1 
                                and e.AuditProfileId= 205 
                                and ch.CardId = 1 
                                and ar.CardId = 1 
                                and ar.RuleId=23 
                                and t.StmtDate=CONVERT(varchar,'04/02/2015',112) 
                                and  t.ClientId=249 
                                and exists (select 1 from #temp t1 where t1.Table2Id=t.Table2Id and t1.Table3Date=t.Table3Date) )t

join
(select [Count],Table2Id,Table3Date  from #temp) r on t.Table2Id=r.Table2Id and t.Table3Date=r.Table3Date

)s            where s.Score<>0   



Drop table #temp