我在做一个档案系统的优化, 其中统计功能,3个存储过程完成统计功能, 将T_AM_File 表按照Company_ID 分区以后统计的执行速度反而比分区之前更慢了 执行一次存储过程130几秒,实在有点悲剧
这3个存储过程 总共查询了两次 分区过的T_AM_File表, 如果我单独执行那两个查询的SQL 速度又不是很慢
一次十几秒就查完了
我还是没想明白个中道理, 把存储过程贴出来, 欢迎指教create or replace procedure Statistic
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtStatisticDateDelete in date,
dtCreateDateFrom in date,
dtCreateDateTo in date,
nStatisticMode in int
) as
begin
Delete from T_AM_Statistic where Statistic_Date <=dtStatisticDateDelete and Company_ID = nCompanyID and Dept_ID = nDeptID; if(nStatisticMode<=4)then
StatisticFile(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtCreateDateFrom,dtCreateDateTo,nStatisticMode);
else
StatisticVolume(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtCreateDateFrom,dtCreateDateTo);
end if;
end Statistic;
create or replace procedure StatisticFile
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtFileCreateDateFrom in date,
dtFileCreateDateTo in date,
nStatisticFileMode in int
) as
nCount int;
nMax int;
strStatisticName varchar(1000);
begin
if(nStatisticFileMode=0) then
Select Count(*) into nCount from T_AM_File Where Company_ID = nCompanyID and Dept_ID = nDeptID and (File_CreateDate > =dtFileCreateDateFrom and File_CreateDate < =dtFileCreateDateTo) ;
Select max(Statistic_Order) into nMax from T_AM_Statistic Where Statistic_ID = strStatisticID;
if(nMax is null) then
nMax:=1;
else
nMax:=nMax+1;
end if;
Insert into T_AM_Statistic (Statistic_ID,Statistic_Order,Company_ID,Dept_ID,Statistic_Name,Statistic_Number,Statistic_Date,Statistic_Re,Statistic_CreateDateFrom,Statistic_CreateDateTo)
values (strStatisticID,nMax,nCompanyID,nDeptID,'档案文件总数',nCount,dtStatisticDate,' ',dtFileCreateDateFrom,dtFileCreateDateTo);
end if;
if(nStatisticFileMode=1) then
StatisticFile_IsDelete(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,0,' 没有销毁的档案文件数');
end if;
if(nStatisticFileMode=2) then
StatisticFile_IsDelete(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,1,' 已经销毁的档案文件数');
end if;
if(nStatisticFileMode=3) then
StatisticFile_IsLendread(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,0,' 没借阅记录的档案文件数');
end if;
if(nStatisticFileMode=4) then
StatisticFile_IsLendread(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,1,' 有借阅记录的档案文件数');
end if;
end StatisticFile;
create or replace procedure StatisticFile_IsDelete
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtFileCreateDateFrom in date,
dtFileCreateDateTo in date,
nFileIsDelete in int,
strStatisticName in varchar
) as
nCount int;
nMax int;
begin
-------------------------------------------------------------
Select Count(*) into nCount from T_AM_File Where Company_ID = nCompanyID and Dept_ID = nDeptID and (File_CreateDate > =dtFileCreateDateFrom and File_CreateDate < =dtFileCreateDateTo) and File_IsDelete = nFileIsDelete;
Select max(Statistic_Order) into nMax from T_AM_Statistic Where Statistic_ID = strStatisticID;
if(nMax is null) then
nMax:=1;
else
nMax:=nMax+1;
end if; Insert into T_AM_Statistic (Statistic_ID,Statistic_Order,Company_ID,Dept_ID,Statistic_Name,Statistic_Number,Statistic_Date,Statistic_Re,Statistic_CreateDateFrom,Statistic_CreateDateTo)
values (strStatisticID,nMax,nCompanyID,nDeptID,strStatisticName,nCount,dtStatisticDate,' ',dtFileCreateDateFrom,dtFileCreateDateTo);
-------------------------------------------------------------- StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 资料文件数',0,nFileIsDelete,-1);
StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 盒内文件数',1,nFileIsDelete,-1);
StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 未归档的签收文档数',2,nFileIsDelete,-1);
end StatisticFile_IsDelete;
这3个存储过程 总共查询了两次 分区过的T_AM_File表, 如果我单独执行那两个查询的SQL 速度又不是很慢
一次十几秒就查完了
我还是没想明白个中道理, 把存储过程贴出来, 欢迎指教create or replace procedure Statistic
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtStatisticDateDelete in date,
dtCreateDateFrom in date,
dtCreateDateTo in date,
nStatisticMode in int
) as
begin
Delete from T_AM_Statistic where Statistic_Date <=dtStatisticDateDelete and Company_ID = nCompanyID and Dept_ID = nDeptID; if(nStatisticMode<=4)then
StatisticFile(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtCreateDateFrom,dtCreateDateTo,nStatisticMode);
else
StatisticVolume(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtCreateDateFrom,dtCreateDateTo);
end if;
end Statistic;
create or replace procedure StatisticFile
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtFileCreateDateFrom in date,
dtFileCreateDateTo in date,
nStatisticFileMode in int
) as
nCount int;
nMax int;
strStatisticName varchar(1000);
begin
if(nStatisticFileMode=0) then
Select Count(*) into nCount from T_AM_File Where Company_ID = nCompanyID and Dept_ID = nDeptID and (File_CreateDate > =dtFileCreateDateFrom and File_CreateDate < =dtFileCreateDateTo) ;
Select max(Statistic_Order) into nMax from T_AM_Statistic Where Statistic_ID = strStatisticID;
if(nMax is null) then
nMax:=1;
else
nMax:=nMax+1;
end if;
Insert into T_AM_Statistic (Statistic_ID,Statistic_Order,Company_ID,Dept_ID,Statistic_Name,Statistic_Number,Statistic_Date,Statistic_Re,Statistic_CreateDateFrom,Statistic_CreateDateTo)
values (strStatisticID,nMax,nCompanyID,nDeptID,'档案文件总数',nCount,dtStatisticDate,' ',dtFileCreateDateFrom,dtFileCreateDateTo);
end if;
if(nStatisticFileMode=1) then
StatisticFile_IsDelete(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,0,' 没有销毁的档案文件数');
end if;
if(nStatisticFileMode=2) then
StatisticFile_IsDelete(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,1,' 已经销毁的档案文件数');
end if;
if(nStatisticFileMode=3) then
StatisticFile_IsLendread(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,0,' 没借阅记录的档案文件数');
end if;
if(nStatisticFileMode=4) then
StatisticFile_IsLendread(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,1,' 有借阅记录的档案文件数');
end if;
end StatisticFile;
create or replace procedure StatisticFile_IsDelete
(
strStatisticID in varchar,
nCompanyID in int,
nDeptID in int,
dtStatisticDate in date,
dtFileCreateDateFrom in date,
dtFileCreateDateTo in date,
nFileIsDelete in int,
strStatisticName in varchar
) as
nCount int;
nMax int;
begin
-------------------------------------------------------------
Select Count(*) into nCount from T_AM_File Where Company_ID = nCompanyID and Dept_ID = nDeptID and (File_CreateDate > =dtFileCreateDateFrom and File_CreateDate < =dtFileCreateDateTo) and File_IsDelete = nFileIsDelete;
Select max(Statistic_Order) into nMax from T_AM_Statistic Where Statistic_ID = strStatisticID;
if(nMax is null) then
nMax:=1;
else
nMax:=nMax+1;
end if; Insert into T_AM_Statistic (Statistic_ID,Statistic_Order,Company_ID,Dept_ID,Statistic_Name,Statistic_Number,Statistic_Date,Statistic_Re,Statistic_CreateDateFrom,Statistic_CreateDateTo)
values (strStatisticID,nMax,nCompanyID,nDeptID,strStatisticName,nCount,dtStatisticDate,' ',dtFileCreateDateFrom,dtFileCreateDateTo);
-------------------------------------------------------------- StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 资料文件数',0,nFileIsDelete,-1);
StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 盒内文件数',1,nFileIsDelete,-1);
StatisticFile_ArchivesType(strStatisticID,nCompanyID,nDeptID,dtStatisticDate,dtFileCreateDateFrom,dtFileCreateDateTo,' 未归档的签收文档数',2,nFileIsDelete,-1);
end StatisticFile_IsDelete;
exec dbms_stats.gather_table_stats(user,'T_AM_File',cascade => true);--分区按什么建的,Company_ID、Dept_ID、File_CreateDate索引情况是怎样的?
--最好贴出有执行计划
你的 T_AM_File 表按照Company_ID 分区 是否能够划分到合理的数量?
还有是 分区在使用的时候,一定要加上分区的条件进行查询,否则的确会更慢。
存储过程里有一堆逻辑,delete、insert 之类,这些时间不算上?要优化,还是一个一个存储过程的来吧,先把存储过程拆分出来,看一下具体时间浪费在哪了。
建议写个日志,记录开始、结束时间。PS. 上面一堆代码,也没几个注释,好在逻辑不是很复杂,但还是让人看的很晕。