January 12, 2011 at 12:49 am
/*
set statistics time on
set statistics io on
*/
select distinct a.keyinstn, e.keyperson
from snl_new..instn a
inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
and e.keyanalysttype = 0
and e.updoperation < 2
left join snl_new..instnreg f on a.keyinstn = f.keyinstn
and f.keyownershipstructure in (1,5,9)
and a.instndoesnotprice = 1
and d.keyindustry = 35
and a.gaapdomainclass <> 12
and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
inner join snl_new..finleop g on a.keyinstn = g.keyinstn
inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
inner join snl_new..sourcetaggingdataitem i on ((g.keyfinleop = i.oid and i.keytable = 560) or (h.keyfinl = i.oid and i.keytable = 107))
inner join snl_new..sourcetagging j on i.keysourcetaggingdataitem = j.keysourcetaggingdataitem
inner join lookup..fiscalperiod k on h.fiscalquarter = k.fiscalquarter
inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
and j2.keytable = 560
and j2.firstnormalapproval = 1
and j2.statustrackingdate >= '2009-12-04'
and j2.statustrackingdate < '2010-03-20'
and j2.updoperation < 2
where d.keyindustry = 34
and a.gaapdomainclass not in (12,13)
and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
and j.keysourcetaggingconstanttype in (7,8,9)
and k.lookupindicatorforderivedrow = 0
and a.updoperation < 2
and d.updoperation < 2
and g.updoperation < 2
and h.updoperation < 2
and i.updoperation < 2
and j.updoperation < 2
and k.updoperation < 2
and j2.updoperation < 2
and f.keyinstn is null
union
select distinct a.keyinstn, e.keyperson
from snl_new..instn a
inner join lookup..industry d on ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
left join internaluseonly_new..analystsinstn e on a.keyinstn = e.keyinstn
and e.keyanalysttype = 0
and e.updoperation < 2
left join snl_new..instnreg f on a.keyinstn = f.keyinstn
and f.keyownershipstructure in (1,5,9)
and a.instndoesnotprice = 1
and d.keyindustry = 35
and a.gaapdomainclass <> 12
and ((d.maskgaapdomainclassinternal & power (2,a.gaapdomainclass) > 0) or (d.maskifrsdomaininternal & power (2,a.keyifrsdomainclass) > 0))
inner join snl_new..finleop g on a.keyinstn = g.keyinstn
inner join snl_new..finl h on g.keyfinleop = h.keyfinleop
inner join internaluseonly_new..questionnairetracking p on h.keyfinl = p.keyfinl
and p.keyquestionnaire in (select distinct keyquestionnaire from internaluseonly_new..questionnaires where questionnairefinancial = 1 and updoperation < 2)
and p.keyquestionnairefinalized in (0,4,7)
and p.updoperation < 2
inner join internaluseonly_new..statustracking j2 on g.keyfinleop = j2.oid
and j2.keytable = 560
and j2.firstnormalapproval = 1
and j2.statustrackingdate >= '2009-12-04'
and j2.statustrackingdate < '2010-03-20'
and j2.updoperation < 2
where d.keyindustry = 34
and a.gaapdomainclass not in (12,13)
and (a.gaapcurrent in (0,1) or a.ifrscurrent in (0,1))
and a.updoperation < 2
and d.updoperation < 2
and g.updoperation < 2
and h.updoperation < 2
and f.keyinstn is null
order by e.keyperson
Here is the logical read details. Its taking nearly 6 minutes to fetch just 62 rows.
I have attached execution plan details for the same.
Can anyone let me know what can be done to reduce logical read and time?
Table 'FiscalPeriod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Industry'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FinlEOP'. Scan count 535, logical reads 1789, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Instn'. Scan count 10, logical reads 2222, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StatusTracking'. Scan count 10, logical reads 46402, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 132511338, physical reads 2108, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SourceTaggingDataItem'. Scan count 8, logical reads 191188, physical reads 7, read-ahead reads 37, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Finl'. Scan count 46319, logical reads 173089, physical reads 641, read-ahead reads 992, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SourceTagging'. Scan count 11, logical reads 131, physical reads 10, read-ahead reads 100, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AnalystsInstn'. Scan count 65, logical reads 761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Questionnaires'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FinlEOPFilter'. Scan count 0, logical reads 159734, physical reads 200, read-ahead reads 799, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'QuestionnaireTracking'. Scan count 5, logical reads 3036, physical reads 10, read-ahead reads 2762, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
January 12, 2011 at 1:39 am
First of update the statistics of all tables, but specifically sourcetaggingdataitem.
Its doing a loop join to that table which is causing the problem,
it may be worth forcing a hash or a merge ( depending on your indexing) join to see if that helps.
January 12, 2011 at 1:55 am
It also might help to preaggregate the tables commonly used in both UNION statements (e.g. using a temp table, maybe even a CTE will help) as well as considering UNION ALL instead of UNION.
January 12, 2011 at 2:26 am
LutzM (1/12/2011)
It also might help to preaggregate the tables commonly used in both UNION statements (e.g. using a temp table, maybe even a CTE will help) as well as considering UNION ALL instead of UNION.
Hi Dave,
I have tried updating statistics on all table. But not getting any help with that.
If you can explain in detail regarding using Hash join rathen than Loop join that would be great
Thanks for quick reply
January 12, 2011 at 2:32 am
BOL describes it fully
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply