June 12, 2015 at 4:09 am
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
(
[table3MerchantDetailId] ASC
)
INCLUDE ( [State],
[ClientId]) 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!!! I have attached a part of the execution plan for table4
June 12, 2015 at 4:30 am
Can you attach an actual execution plan as a .sqlplan attachment please? Each graphical component on the plan has a whole bunch of properties which provide essential clues for query tuning. A picture doesn't provide this - it's like phoning for a taxi and someone knocking on your door 10 minutes later and holding up a picture of one.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2015 at 4:53 am
-- Please check that this formatted version of your query still works,
-- then put table aliases before each column reference in the
-- inner derived table 'r'.
SELECT DISTINCT *
FROM ( -- r
SELECT
t.RuleId, t.Table3Id, NULL AS RiskLeveltypeId,
CASE
WHEN r.COUNT >= t.highlimit THEN 60
WHEN r.COUNT >= t.mediumlimit THEN 30
WHEN r.COUNT >= t.lowlimit then 15
ELSE 0 END AS Score,
CreatedUser,
GETDATE() AS CreatedDate,
CreatedUser AS LastActivityUser,
GETDATE() AS LastActivityDate,
t.Table2Id,
t.Table1Id,
CardId,
249 AS ClientId,
t.StmtDate
FROM ( -- ??
( -- r
SELECT
Table2Id,
Table3Date,
COUNT(DISTINCT Table4.[State]) AS COUNT
FROM Table3Data
INNER JOIN Table4
ON Table3Data.Table3MerchantDetailId = Table4.Table3MerchantDetailId
WHERE Table3Data.ClientId = 249
GROUP BY Table2Id, Table3Date
HAVING COUNT(DISTINCT Table4.[State]) > 1
) r
INNER JOIN
( -- t
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
INNER JOIN Table1 e
ON e.Table1Id = ch.Table1Id
AND e.clientid = ch.clientid
INNER 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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2015 at 5:16 am
Thank you for the reply!! the execution plan has real table names, which is why i did not post the full plan earlier. The image attached is the only part of the execution plan which is the most expensive.
I tried the query you sent, no luck with the performance yet.
June 12, 2015 at 5:23 am
T2512 (6/12/2015)
Thank you for the reply!! the execution plan has real table names, which is why i did not post the full plan earlier. The image attached is the only part of the execution plan which is the most expensive.
Plans can be obfuscated. Part of the plan may be insufficient even if it covers what appears to be the most expensive operator.
I tried the query you sent, no luck with the performance yet.
I haven't done anything to change the query, just formatted it somewhat. Folks reading this thread will want to investigate what the query is doing, and how. A good first step is to make the query readable, and that includes ensuring that all referenced columns have aliases so we can see which tables they are from.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 12, 2015 at 5:45 am
-- Style 112 for the convert function is 'yyyymmdd' which doesn't match the supplied string.
-- The CONVERT function doesn't make sense anyway - you're converting a string to a string.
-- What datatype is t.StmtDate, and what date is '04/02/2015', is it February or April?
-- Table 5 is an INNER JOIN. Columns are referenced in the WHERE clause.
-- Try this as an alternative to your expensive query.
-- It probably won't work but it should give you some ideas.
-- Remember - there's no data to code against, and no ERD for the tables.
SELECT
ar.RuleId,
t.Table3Id,
NULL AS RiskLeveltypeId,
CASE
WHEN x.COUNT >= ar.highlimit THEN 60
WHEN x.COUNT >= ar.mediumlimit THEN 30
WHEN x.COUNT >= ar.lowlimit THEN 15
ELSE 0 END AS Score,
ar.CreatedUser,
GETDATE() AS CreatedDate,
ar.CreatedUser AS LastActivityUser,
GETDATE() AS LastActivityDate,
t.Table2Id,
e.Table1Id,
ch.CardId,
t.ClientId,
t.StmtDate
FROM Table2sData ch
INNER JOIN Table1 e
ON e.Table1Id = ch.Table1Id
AND e.clientid = ch.clientid
INNER JOIN Table3Data t
ON t.Table2Id = ch.Table2Id
AND t.ClientId = ch.Clientid
AND t.run IS NULL
CROSS APPLY (
SELECT COUNT(DISTINCT Table4.[State]) AS COUNT
FROM Table4
WHERE Table3Data.Table3MerchantDetailId = Table4.Table3MerchantDetailId
) x
INNER JOIN Table5 ar
ON ar.AuditProfileId = e.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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 17, 2015 at 5:51 am
Thank you so much for the reply! 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.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
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
June 17, 2015 at 6:45 am
T2512 (6/17/2015)
Thank you so much for the reply! 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.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
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
This would be better (or something very similar). It's better because it's far simpler and also because it's likely to be faster - you are only reading the #temp table once. You could probably simplify it further by including Table4 into the CROSS APPLY block which calculates the score:
SELECT DISTINCT -- CHECK if this is necessary!
ar.RuleId,
t.Table3Id,
NULL AS RiskLeveltypeId,
x.Score,
ar.CreatedUser,
GETDATE() AS CreatedDate,
ar.CreatedUser AS LastActivityUser,
GETDATE() AS LastActivityDate,
t.Table2Id,
e.Table1Id,
ch.CardId,
t.ClientId, -- = 249
t.StmtDate
FROM Table2sData ch
inner JOIN Table1 e
ON e.Table1Id = ch.Table1Id
AND e.clientid = ch.clientid
inner JOIN Table3Data t
ON ch.Table2Id = t.Table2Id
AND t.ClientId = ch.Clientid
AND t.run IS NULL
inner JOIN Table5 ar
ON e.AuditProfileId = ar.AuditProfileId
INNER JOIN #temp r
ON t.Table2Id = r.Table2Id
AND t.Table3Date = r.Table3Date
CROSS APPLY (
SELECT Score = CASE
WHEN r.COUNT >= t.highlimit THEN 60
WHEN r.COUNT >= t.mediumlimit THEN 30
WHEN r.COUNT >= t.lowlimit then 15
ELSE 0 END
) x
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 x.Score <> 0
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply