小议成本计算卡住的解决方法

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-23浏览：1

小议成本计算卡住的解决方法

【问题描述】

成本计算到某个节点，卡住半小时甚至更久

【解决方案】

1 数据库优化与重建索引

数据库优化与重建索引操作步骤	系统管理_数据库优化：索引	SQL SERVER数据库的常规优化

性能优化点	性能优化建议	SQLServer数据库通用优化

转：数据库优化：

1，是否存在过多未清理的临时表；

2，查看表存储排名前20的表，查看最大的20个表为哪些；

3，数据库的恢复模式是否是简单，日志文件是否过大，有没有定期备份，截断日志，收缩数据库。

-------------------------------------------------------------------------------------------------------------------

2 检查业务数据有无嵌套

如有，则打最新补丁，先进行出库成本核算，将其他出库单生成凭证，再计算

QQ截图20221008093625.webp

-------------------------------------------------------------------------------------------------------------------

3 转：SQL Server下SQL语句在代码中执行效率远低于在SSMS下的一个可能原因

若为SQL SERVER 2008版本，且数据库属性设置如下

解决方式：

将数据属性中的【ANSI 警告已启用】设置为true后问题得到解决。

或者通过脚本实现：

USE [master]

ALTER DATABASE 数据库名 SET ANSI_WARNINGS ON WITH NO_WAIT

-------------------------------------------------------------------------------------------------------------------

4 数据库优化脚本以及定时自动优化

-------------------------------------------------------------------------------------------------------------------

5 提单处理

-------------------------------------------------------------------------------------------------------------------

6 转：SqlServer数据库优化

6.1 启动【sql server Management Studio】，在【对象资源管理器】窗口里选择【SQL Server 代理】-【作业】-【新增作业】。如果SQL Server 代理没有启用请先启用。

6.2 选择到【步骤】，新建作业步骤，写上步骤名称，选择类型为T_SQL,选择对应需要优化的数据库，在命令框中输入执行存储过程exec RebuildIndex_UpdateSTATISTICS

6.3 选择【高级】，成功时要执行的操作改为【退出报告成功的作业】

6.4 创建存储过程，复制以下脚本到SQL Server里面执行，USE [CB1608]中的“CB1608”改为需要优化的数据库

USE [CB1608]

/****** Object: StoredProcedure [dbo].[RebuildIndex_UpdateSTATISTICS] Script Date: *****/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

create PROCEDURE [dbo].[RebuildIndex_UpdateSTATISTICS]

as begin

declare @SqlStr nvarchar(max)

set @SqlStr=''

select @SqlStr= @SqlStr+ + 'alter index all on '+name+ ' rebuild;' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr)

declare @SqlStr1 nvarchar(max)

set @SqlStr1=''

select @SqlStr1= @SqlStr1+ + 'UPDATE STATISTICS '+name+ ';' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr1)

declare @SqlStr2 nvarchar(max)

set @SqlStr2=''

select @SqlStr2= @SqlStr2+ + 'ALTER TABLE '+name+ ' rebuild WITH (DATA_COMPRESSION =ROW);' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr2)

end

6.5 设置计划，自动定时执行的时间，切换到【计划】选项，新增作业计划，选择重复执行，可以根据实际情况来设置执行频率和执行时点。

6.6 点击确认按钮完成作业计划创建操作。如果想先立即执行一次，可以右键该作业->作业开始步骤。执行时要选择空闲时间，因为优化过程中会导致性能慢，也有可能造成阻塞。执行时间根据数据库大小

附录：或者使用下面脚本直接创建也可以

--USE [蓝海实业集团1230] --需要优化的数据库名称，需要修改为对应的数据库,或者直接打开对应数据库执行

--GO

/****** Object: StoredProcedure [dbo].[RebuildIndex_UpdateSTATISTICS] Script Date: *****/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

if exists(select 1 from sys.all_objects where name ='RebuildIndex_UpdateSTATISTICS')

begin

drop PROCEDURE RebuildIndex_UpdateSTATISTICS

end

create PROCEDURE [dbo].[RebuildIndex_UpdateSTATISTICS]

as begin

declare @SqlStr nvarchar(max)

set @SqlStr=''

select @SqlStr= @SqlStr+ + 'alter index all on '+name+ ' rebuild;' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr)

declare @SqlStr1 nvarchar(max)

set @SqlStr1=''

select @SqlStr1= @SqlStr1+ + 'UPDATE STATISTICS '+name+ ';' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr1)

declare @SqlStr2 nvarchar(max)

set @SqlStr2=''

select @SqlStr2= @SqlStr2+ + 'ALTER TABLE '+name+ ' rebuild WITH (DATA_COMPRESSION =ROW);' from sysobjects where xtype='U' and (name like 'T_%') and (name not like 'TMP%')

exec (@SqlStr2)

end

/****** Object: Job [优化数据库（重建索引更新统计信息收缩表）] Script Date: 2020/7/30 15:22:57 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 2020/7/30 15:22:57 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

declare @dtbs varchar(255);

Select @dtbs= Name From Master..SysDataBases Where DbId=(Select Dbid From Master..SysProcesses Where Spid = @@spid);

declare @jobName varchar(255);

set @jobName = '优化数据库（重建索引更新统计信息收缩表）_' + @dtbs;

if EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @jobName )

BEGIN

EXEC

msdb.dbo.sp_delete_job @job_name = @jobName

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobName,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [重建索引更新统计信息收缩表] Script Date: 2020/7/30 15:22:57 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'重建索引更新统计信息收缩表',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'exec RebuildIndex_UpdateSTATISTICS',

@database_name= @dtbs,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'优化数据库',

@enabled=1,

@freq_type=4, -- 8：每周；4每日

@freq_interval=1, --执行间隔

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20000101,

@active_end_date=99991231,

@active_start_time=20000,

@active_end_time=235959,

@schedule_uid=N'ad85d50c-2b86-4b98-9e2f-2bd9c07d6d1c'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

-------------------------------------------------------------------------------------------------------------------

7 转：Oracle数据库优化

--用作业定时执行以下4个定时做作业，都在 SYS 账户下定制运行。

--1 每天跑一遍，业务空闲时段运行。

create or replace procedure pro_analyze_dict as

begin

dbms_stats.gather_dictionary_stats();

dbms_stats.gather_fixed_objects_stats();

end pro_analyze_dict;

--2 每天跑一遍，业务空闲时段运行。

create or replace procedure pro_analyze_stats as

begin

-- 2017-11-03 10:46

-- username in ('','') order by decode(username,'',1,'', 填入业务账户名称，下同。

for i in (select username from dba_users where username in ('K3BC') order by decode(username,'',1,'',2,3)) loop

for j in (select owner, '"'||table_name||'"' table_name from dba_tables a where owner in (i.username) and temporary='N' and table_name not like 'TMP%'

and not exists (select 1 from dba_mview_logs where owner in (i.username) and log_table=a.table_name)

order by owner, last_analyzed nulls first) loop

dbms_stats.gather_table_stats(j.owner,j.table_name,method_opt=>'for all columns size 254',degree=>2);

end loop;

for j in (select owner, '"'||table_name||'"' table_name from dba_tables where owner in (i.username) and temporary='Y' and last_analyzed is not null

order by owner, table_name) loop

dbms_stats.delete_table_stats(j.owner,j.table_name);

end loop;

-- 2017-10-09 10:36 gather virtual column stats

for j in (select owner, '"'||table_name||'"' table_name, column_name from dba_tab_cols where owner in (i.username) and virtual_column='YES'

and table_name not like 'TMP%' and data_type<>'XMLTYPE' and hidden_column='NO') loop

dbms_stats.gather_table_stats(j.owner,j.table_name,method_opt=>'for columns '||j.column_name||' size 254 ');

end loop;

end pro_analyze_stats;

--3 每小时跑一遍。

create or replace procedure pro_delete_tm_stats as

begin

for i in (select owner, table_name, last_analyzed, num_rows from dba_tables where owner in ('K3BC') and temporary='Y'

and last_analyzed is not null and last_analyzed < (sysdate - 1/288) order by owner, table_name) loop

dbms_stats.delete_table_stats(i.owner,i.table_name);

end loop;

end;

--4 每天跑一遍，若 TMP% 开头的表太多，可以间隔12小时左右再跑一遍，安排在业务空闲时段运行。

create or replace procedure pro_purge_tmptable as

ora_942 exception;

pragma exception_init(ora_942, -942);

ora_54 exception;

pragma exception_init(ora_54, -54);

v_msg varchar2(300);

begin

for i in (select username from dba_users where username in ('K3BC') order by decode(username,'',1,'',2,3)) loop

for j in (select a.owner, a.table_name from dba_tables a, dba_objects b

where a.owner in (i.username) and a.table_name like 'TMP%' and length(a.table_name) = 30

and b.owner in (i.username) and b.object_type='TABLE' and b.object_name like 'TMP%' and b.created < (sysdate - 1)

and (a.owner=b.owner and a.table_name=b.object_name) ) loop

begin

--dbms_output.put_line(j.table_name);

execute immediate 'drop table '||j.owner||'.'||j.table_name||' purge';

exception

when ora_942 then

null;

when others then

v_msg := substr(dbms_utility.format_error_backtrace,1,100);

v_msg := v_msg||','||substr(dbms_utility.format_error_stack,1,100);

v_msg := v_msg||',Table_name:'||j.table_name;

raise_application_error(-20010,v_msg);

end;

end loop;

execute immediate 'delete from '||i.username||'.t_bas_temporarytablename a where not exists (select 1 from dba_tables where owner='''||i.username||''' and table_name=a.ftablename)';

commit;

end loop;

end pro_purge_tmptable;

或者直接手动在数据库中执行以下语句，查下出更新脚本，然后复制出脚本，再执行脚本更新统信息和索引更新优化。

SELECT 'Analyze Table '||table_name||' compute statistics;' FROM user_tables WHERE table_name like 'T_%' and table_name not like 'TM%';