合并报表优化记录.
来源:互联网 发布:python 微信上传图片 编辑:程序博客网 时间:2024/04/30 00:18
这是早期的合并报表优化记录,内容不多,保留下来备查。
一.查找源报表是否存在
SELECT 1 WHERE EXISTS (SELECT * FROM T_CSL_CslReport WHERE FSourceRptID = '4583062d
未优化前:
执行成本: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-d
AND ("PARENTSTRUCTURE".FID = '0aa070b0-0109-1000-e002-1fe
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
- 合并报表优化记录.
- 合并报表优化记录
- 合并报表优化记录
- 合并报表
- hive优化记录----合并小文件压缩输出
- 报表优化
- 报表单元格的合并
- 简述合并报表逻辑
- 报表运行时的报表合并
- 水晶报表合并模块大全
- 水晶报表合并模块大全
- 水晶报表合并模块大全
- 合并报表取数说明
- rdlc报表纵向合并单元格
- 报表单元格的合并(续)
- ireport+springMVC合并报表实例
- 报表项目代码记录
- RTF报表开发记录
- Visual FoxPro 的一个排序作业
- 设置动态修改,删除,插入的odac
- 理解Session State模式+ASP.NET SESSION丢失FAQ[翻译]
- 在C++中使用C#编写的类
- 北京火车订票电话一览
- 合并报表优化记录.
- 3.2 改变日志操作模式
- 办公软件现状与发展趋势
- 论文的书写
- asp.net显示进度提示功能.
- ESRI.ArcGIS.esriSystem名称空间问题
- data list分页功能的实现
- fedora 8 安装配置
- JDBC select update