执行报表反接收操作时检查是否产生关联报表的sql查询。
这条脚本在oracle库都执行正常,执行速度可以控制在0.05s以内。
存在sql查询性能问题的客户情况:
1、操作系统
阿里云 四核、14G内存。数据库服务器分配了10个G的内存。
2、Sql Server设置
3、业务数据:
4、客户出现性能的SQL
SELECT distinct r.FID AS ID,r2.FName AS SourceReport,
r2.FNumber AS SourceReportNumber,
tr.FName_l2 AS TreeName,
o.FName_l2 AS UnitName,
c.FName_l2 AS CurrencyName,
r.FPeriodType AS PeriodType,
r.FYear AS FYear,
r.FPeriod AS Period,
r.FSourceType AS SourceType,
r.FName AS ReportName,
s.FLongNumber AS OrgLongNumber,
r.FCommittedStatus AS CommittedStatus
FROM T_CSL_CslReport r
INNER JOIN T_Org_Tree tr ON r.FOrgTreeID = tr.FID
INNER JOIN T_Org_BaseUnit o ON r.FOrgUnitID = o.FID
INNER JOIN T_BD_Currency c ON r.FCurrencyID = c.FID
INNER JOIN T_RPT_Template t ON r.FTemplateID = t.FID
INNER JOIN T_CSL_TempletItemList til ON t.FID = til.FTemplateID
INNER JOIN T_CSL_TempletItemList til2
ON til.FItemNumber = til2.FItemNumber
AND til.FDataElement = til2.FDataElement
AND til.FValueType = til2.FValueType
INNER JOIN T_RPT_Template t2
ON til2.FTemplateID = t2.FID
INNER JOIN T_CSL_CslReport r2
ON t2.FID = r2.FTemplateID AND r.FCurrencyID = r2.FCurrencyID
AND r.FYear = r2.FYear AND r.FPeriod = r2.FPeriod AND r.