总账账表查询慢耗时长的常见原因及解决办法

遇到（客户反馈）总账相关账表查询慢，卡顿等问题时，常见的原因有：

1，未过账期间太多

2，查询范围过大

3，后台数据库索引碎片严重

可以从这几个方面分析原因，以下就这些原因详细说明并给出解决办法。

一，未过账期间太多

查询的账簿当前期间是很早的历史期间，比如是4年前的某个期间，而查询范围又是最近的年度和月份，且勾选了“包含未过账凭证”选项。这种情况下，系统需要对所有未过账期间的凭证逐期进行虚拟过账，只有这样才能得到查询范围内的科目期初余额，本年累计等数据。此时，账簿的当前期间距离正常年期越远，虚拟过账耗时越长，查询越慢。

要解决这种场景下的卡慢问题，唯一的方法就是尽快结账到正常年期来。 

二，查询范围过大

比如一次查询4-5年的数据，不指定科目范围或核算维度的编码范围等，这将导致查询中间过程临时表数据量过大，造成磁盘IO性能下降，空间不够，甚至服务器崩溃。

建议的指定查询条件时，根据实际查询需要，在条件中尽量过滤掉不需要数据。如：

1，多账簿查询时，尽量优化账簿隶属关系。如不包含本次查询不关心的账簿；为不同需求，设置不同的隶属关系，只为本次查询时不要包含不需要的账簿数据

2，指定的起止年度和期间，跨度越小越好，尽量不要超时一年。

3，指定科目范围，尽量不要空着，这样会查询所有科目，指定科目范围后，查询中间过程中的数据量将会大大减少，这对整体查询速度提升明显

4，指定核算维度编码范围，和科目一样，当指定核算维度起上范围后，系统过滤后的有效数据量将明显减少。尤其是核算维度组合查询时，由于各维度可能的组合数极多，如果不指定编码范围，中间查询过程的数据量将极其大，这将严重影响查询性能。在核算维度明细账，多账簿核算维度明细账等账表中，如下图条件中的选项，如果不勾选，将导致可能的维度组合数据成指数级增长，这将导致数据量灾难级爆发，很容易出现磁盘空间用尽，服务器宕机的情况。

5，非必要，不要勾选“包含未过账凭证”

当勾选“包含未过账凭证”时，系统将对所有查询结束期间之前的凭证进行虚拟过账，如果存在多期未过账时，情况将更加糟糕，系统需要逐期虚拟过账。而这，将消耗大量系统资源和时间，让整个查询变得耗时长。我们应该尽量避免系统进行虚拟过账。

解决办法：对于查询已结账期间的数据，由于已结账期间所有凭证均已过账，不存未过账凭证，因此，勾选该选项对查询结果有意义。只有在查询当前期间数据时，才需要勾选。为了避免需要勾选“包含未过账凭证”选项，建议已结账期间和当前期间分开查询，尽量不要混在一起查询。

三，后台数据库索引碎片严重

由于私有云环境，长期缺少对数据库中数据及索引进行优化维护的制度计划，随着时间的推移，数据的变迁，导致后台数据库索引碎片严重，效率低下，SQL执行性能不佳。

解决方案：

1)，在数据中心中定期执行“数据库优化”

具体路径为：数据中心列表->选中待优化的账套->升级->数据库优化。

注意：该功能将对全库所有表进行索引优化，耗时较长，通常以小时为单位，建议在夜间或非工作日进行。

2)，手工脚本优化，可以仅优化指定的表，避免全库执行，耗时较少。

具体方法如下（示例脚本仅适用于SQL SERVER）：

可以通过以下脚本查询对应的数据表的索引碎片情况

DBCC SHOWCONTIG(表名)

查询结果示例如下：

DBCC SHOWCONTIG 正在扫描 'T_GL_VOUCHER' 表...
表: 'T_GL_VOUCHER' (884966279)；索引 ID: 1，数据库 ID: 16
已执行 TABLE 级别的扫描。
- 扫描页数................................: 2704
- 扫描区数..............................: 351
- 区切换次数..............................: 413
- 每个区的平均页数........................: 7.7
- 扫描密度 [最佳计数:实际计数].......: 81.64% [338:414]
- 逻辑扫描碎片 ..................: 3.48%
- 区扫描碎片 ..................: 8.55%
- 每页的平均可用字节数.....................: 268.8
- 平均页密度(满).....................: 96.68%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

主要参考以下几项：

1，扫描密度，平均页密度：越接近100%越好，反之越差

2，逻辑扫描碎片，区扫描碎片：越接近100%越差，说明碎片率高，30%以上建议进行索引重建

如果发现碎片率较高，就需要进行索引重建，重建脚本如下：

ALTER INDEX ALL ON 表名 REBUILD;

总账常用数据表的索引碎片查询和重建脚本汇总如下：

--查询索引碎片情况
DBCC SHOWCONTIG(T_GL_VOUCHER)
DBCC SHOWCONTIG(T_GL_VOUCHERENTRY)
DBCC SHOWCONTIG(T_GL_BALANCE)
DBCC SHOWCONTIG( T_GL_BALANCEADJUST)
DBCC SHOWCONTIG(T_GL_BALANCEQTY)
DBCC SHOWCONTIG( T_GL_BALANCEQTYADJUST)
DBCC SHOWCONTIG( T_BD_FLEXITEMDETAILV)
DBCC SHOWCONTIG(T_BD_ACCOUNT)

--索引优化
ALTER INDEX ALL ON T_GL_VOUCHER REBUILD;
ALTER INDEX ALL ON T_GL_VOUCHERENTRY REBUILD;
ALTER INDEX ALL ON T_GL_BALANCE REBUILD;
ALTER INDEX ALL ON T_GL_BALANCEQTY REBUILD;
ALTER INDEX ALL ON T_GL_BALANCEADJUST REBUILD;
ALTER INDEX ALL ON T_GL_BALANCEQTYADJUST REBUILD;
ALTER INDEX ALL ON T_BD_FLEXITEMDETAILV REBUILD;
ALTER INDEX ALL ON T_BD_ACCOUNT REBUILD;

3，利用数据库的自动任务，定时执行，将索引优化的脚本关联到执行计划方案中，让系统定期执行。

大致方法为（示例脚本仅适用于SQL SERVER）：

1)，在“SQL SERVER代理”下的“作业”上右键，新建一个“作业”

2)，指定名称后，点“步骤”，开始“新建”作业步骤。指定步骤名称，对应的数据库，及要执行的脚本。

优化可能用到的脚本如下（示例脚本仅适用于SQL SERVER）：

--重建索引 更新统计信息 表收缩
DECLARE @DBCCString NVARCHAR(1000)
DECLARE @DBCCString1 NVARCHAR(1000)
DECLARE @DBCCString2 NVARCHAR(1000)
DECLARE @TableName VARCHAR(150)
declare @n int
declare @cu int
select @n= (select count(1) from sysobjects WHERE xType='U' and (name like 'T_%') and name not like 'tmp%')
select @cu=1
DECLARE Cur_Index CURSOR FOR
SELECT Name AS TblName FROM sysobjects WHERE xType='U' and (name like 'T_%') and name not like 'tmp%' ORDER BY TblName
FOR READ ONLY
OPEN Cur_Index
FETCH NEXT FROM Cur_Index INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
  SET @DBCCString ='alter index all on '+@TableName+ ' rebuild;'
  EXEC (@DBCCString)   
  SET @DBCCString1 = 'UPDATE STATISTICS '+@TableName+ ';'
  EXEC (@DBCCString1)
  SET @DBCCString2 = 'ALTER TABLE '+@TableName+ ' rebuild WITH (DATA_COMPRESSION =ROW);'
  EXEC (@DBCCString2)
  PRINT '第' + CONVERT(varchar (12), @cu)+ ' /共 ' +CONVERT(varchar (12), @n) + ' 表： ' + @TableName +' 重建索引、更新统计信息、表收缩完成！'
  FETCH NEXT FROM Cur_Index INTO @TableName
  select @cu=@cu+1
END
CLOSE Cur_Index
DEALLOCATE Cur_Index
PRINT '操作完成！'

3)，然后在“计划”中“新建”作业计划，指定执行间隔和频率。不要让相关作业在业务活动期间执行，建议在夜间或休息日执行。

注意，后续在“作业活动监视器”中关注相关作业的执行情况。

四，如以上常用方法还是无法改善账表的查询性能，可以调出性能监控，记录下查询过程中后台SQL执行耗时，并导出监控文件，发送给官方技术人员分析。

调出性能监控工具的方法是：在需要监控性能的功能的界面上，同时按下Shift，Ctrl，Alt和M四个组合键。

在开始查询前，先“开启”监控。查询结束后，再点“停止”，然后“查看报告”。

性能监控报告将用红色标出耗时较长的SQL操作。将该报告导出成文件后，可以方便的传递给相关技术人员。

希望对你有帮助。