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!
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