合并报表优化记录
这是早期的合并报表优化记录，内容不多，保留下来备查。
一.查找源报表是否存在
SELECT 1 WHERE EXISTS (SELECT * FROM T_CSL_CslReport WHERE FSourceRptID = '4583062d-010a-1000-e002-63bbc0a8ef02B712EA2C')
未优化前:
执行成本:0.396
Reads:2139
Duration:10
给T_CSL_CslReport的FsourceRptID增加索引后:
执行成本:0.00641
Reads:43
Duration:0
增加索引后提高了50倍
Create Index IX_Csl_Rpt_SrcRpt On T_CSL_CslReport(FSourceRptID);
二.项目取数
SELECT sum(T_CSL_ItemDataEntry003.F670) "ZJ17001"
FROM T_CSL_ItemDataEntry ItemDataBd
INNER JOIN T_CSL_ItemData ItemDataHd ON ItemDataBd.FItemDataID = ItemDataHd.FID
LEFT OUTER JOIN T_Csl_RptReceived Received ON Received.FReportID = ItemDataHd.FReportID
LEFT OUTER JOIN T_CSL_ItemDataEntry003 ON ItemDataBd.FID = T_CSL_ItemDataEntry003.FID
LEFT OUTER JOIN T_ORG_Tree orgbound ON Received.FOrgTreeID = orgbound.FID
LEFT OUTER JOIN T_ORG_BaseUnit company ON ItemDataHd.FCompanyID = company.FID
LEFT OUTER JOIN T_BD_Currency cur ON ItemDataHd.FCurrencyID = cur.FID
LEFT OUTER JOIN T_BD_Currency tgtcur ON ItemDataHd.FTargetCurrencyID = tgtcur.FID
WHERE ((1 = 1 AND (orgbound.FNumber = '005' OR (orgbound.FNumber IS NULL)))
AND (((((company.FNumber IN ('0300100800')
AND tgtcur.FNumber = 'BB01')
AND ItemDataHd.FPeriodType = 1)
AND ItemDataBd.FYear = 2006)
AND ItemDataBd.FPeriod = 94)
AND ((ItemDataHd.FDataSource IN (1, 7)
AND ItemDataBd.FDataElement = 4)
AND ItemDataBd.FValueType = 1)))
未优化前:
成本:0.390
Reads:920
Duration:50
给T_Csl_ItemDataEntry的增加索引(FvalueType,Fyear,Fperiod,FDataElement)后:
成本:0.0650
Reads:494
Duration:40
效率提高一倍,提升空间不是太大
Create Index IX_Csl_ItmDE_1 On
T_CSL_ItemDataEntry(FValueType,FYear,FPeriod,FDataElement);
三.
SELECT TOP 100 "CSLREPORT".FID "ID", "ORGUNIT".FName_L2 "ORGUNIT.NAME", "CSLREPORT".FName "NAME", "CURRENCY".FName_L2 "CURRENCY.NAME",
"SOURCECURRENCY".FName_L2 "SOURCECURRENCY.NAME", "CSLREPORT".FSourceType "SOURCETYPE", "CSLREPORT".FPeriodType "PERIODTYPE",
"CSLREPORT".FYear "YEAR", "CSLREPORT".FPeriod "PERIOD", "CSLREPORT".FAuditedStatus "AUDITEDSTATUS",
"CSLREPORT".FCommittedStatus "COMMITTEDSTATUS", "CSLREPORT".FCheckedStatus "CHECKEDSTATUS", "CURRENCY".FID "CURRENCY.ID",
"CSLREPORT".FReportDate "REPORTDATE", "TEMPLATE".FID "TEMPLATE.ID", "ORGUNIT".FID "ORGUNIT.ID",
"TEMPLATE".FTemplateType "TEMPLATE.TEMPLATETYPE", "TREE".FID "TREE.ID", "CSLREPORT".FConvertStatus "CONVERTSTATUS",
"CSLREPORT".FAdjustStatus "ADJUSTSTATUS", "STRUCTURE".FLongNumber "STRUCTURE.LONGNUMBER", "PARENTSTRUCTURE".FID "PARENTSTRUCTURE.ID"
FROM T_CSL_CslReport "CSLREPORT"
INNER JOIN T_ORG_BaseUnit "ORGUNIT" ON "CSLREPORT".FOrgUnitID = "ORGUNIT".FID
INNER JOIN T_BD_Currency "CURRENCY" ON "CSLREPORT".FCurrencyID = "CURRENCY".FID
INNER JOIN T_BD_Currency "SOURCECURRENCY" ON "CSLREPORT".FSourceCurrencyID = "SOURCECURRENCY".FID
INNER JOIN T_RPT_Template "TEMPLATE" ON "CSLREPORT".FTemplateID = "TEMPLATE".FID
INNER JOIN T_ORG_Structure "STRUCTURE" ON "ORGUNIT".FID = "STRUCTURE".FUnitId
INNER JOIN T_ORG_Tree "TREE" ON "STRUCTURE".FTreeId = "TREE".FID
INNER JOIN T_ORG_Structure "PARENTSTRUCTURE" ON "STRUCTURE".FParentID = "PARENTSTRUCTURE".FID
WHERE ((("CSLREPORT".FSourceType NOT IN (4, 6) AND ("CSLREPORT".FCommittedStatus <> 1))
AND "CSLREPORT".FAdjustStatus = 0) AND (((("CSLREPORT".FPeriodType = 3 AND "CSLREPORT".FYear = 2006)
AND "CSLREPORT".FPeriod = 3) AND "TREE".FID = 'a2bf23e7-0108-1000-e000-d469c0a8ef024F2827FD')
AND ("PARENTSTRUCTURE".FID = '0aa070b0-0109-1000-e002-1fe4c0a8ef0232B85C74'
OR "STRUCTURE".FLongNumber = '007001!00700103!00700103015')))
成本:1.21
Reads:3773
Duration:950
给T_Csl_CslReport添加索引(FadjustStatus,Fyear,Fperiod,FSourceType)后:
成本:0.340
Reads:2294
Duration:60
