解决凭证摘要模糊查询性能慢的问题

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

解决凭证摘要模糊查询性能慢的问题

前言：星空的凭证摘要，做为一个常用的重要文本字段，位于单据体上，常常被用来记录一些重要的信息，或标示当前行分录的重要属性。在实际使用中，用户通常需要根据关键字模糊查询这些信息相关的凭证。然后，做为技术开发人员都知道，在文本字段上模糊查询，数据库的查询性能是相关糟糕的。一方面的原因是，模糊查询使用LIKE关键字，不支持走索引。另一方面的原因是单据体上的分录行相对单据头来说，往往数量要多得多。在大量的分录中查询，又不能走索引，这导致模糊查询的性能不高。这也导致，用户在进行摘要有关的查询时，耗时漫长，满意度不高。为了提单客户使用体验，优化摘要模糊查询性能，我们做了大量分析和实验，在方案的易实现性和实际优化效果来看，本方案是目前最佳方案。

本方案的思路：将同一个凭证上所有分录上的摘要用“；”串联成一个字符串后，更新到单据头的备注【FRAMARKS】字段上（PS：如果凭证单据头上的备注字段已被使用，也可以新建一个新字段来代替），查询时，改为对备注做模糊查询，而不再使用“分录上的摘要”，查询的最终数据结果一致。

测试结果对比：效果比较明显，原本需要1分钟的查询优化后3秒左右出结果。从执行计划上来看，原本模糊查询LIKE的开销高达86%，经优化后，降到了2%。

以下是实现步骤：

1，在BOS设计器中扩展凭证，如果已扩展过，可以直接编辑扩展过的凭证，拖一个多行文本字段来界面上，设置相关属性如下图所示，注意，字段名需要和后面脚本中的字段名一致。由于该字段在单据编辑界面不可见且不允许手工编辑，因此，它人位置和外观可以不考虑。保存后退出即可，系统后自动在数据库中生成一个同名字段，如自动关键表中同名字段。

2，更新数据库中备注字段的长度为MAX，防止内容超长溢出。由于表中原有FREMARKS字段或新增加的字段默认长度有限，当分录较多，串联后的摘要数据可能很长，因此，这里将备注字段的长度更改为NVARCHAR(MAX)，这样，理论上可以保存2G的数据，几乎不可能会超长了。更新脚本如下：

--删除原字段上的默认值约束
DECLARE @DefaultName VARCHAR(50)
SELECT @DefaultName=name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('T_GL_VOUCHER')
AND parent_column_id = (SELECT column_id FROM sys.columns 
WHERE object_id = OBJECT_ID('T_GL_VOUCHER')
    AND name = 'FREMARKS')
EXEC('ALTER TABLE T_GL_VOUCHER DROP CONSTRAINT '+@DefaultName);
--修改字段的最大长度
ALTER TABLE T_GL_VOUCHER ALTER COLUMN FREMARKS NVARCHAR(MAX) NOT NULL
--重新添加默认值约束
ALTER TABLE T_GL_VOUCHER ADD DEFAULT ' ' FOR FREMARKS

3，将所有历史数据的摘要信息刷到备注字段中。这一步耗时较长，内部测试，100W个凭证耗时约用时1分钟。实现SQL脚本如下：

--生成内存临时表TMP
WITH TMP AS(
SELECT TMAIN.FVOUCHERID, STUFF((
    SELECT '；'+FEXPLANATION FROM (
            SELECT VE.FVOUCHERID,VE.FEXPLANATION 
            FROM T_GL_VOUCHERENTRY VE
            WHERE VE.FEXPLANATION IS NOT NULL) TEXP 
        WHERE TEXP.FVOUCHERID=TMAIN.FVOUCHERID FOR XML PATH('')
    ),1,1,'') AS FREMARKS
FROM (SELECT V.FVOUCHERID FROM T_GL_VOUCHER V
WHERE LEN(V.FREMARKS)=0) TMAIN)
--关联TMP更新凭证单据头上的备注字段
UPDATE V SET V.FREMARKS=T.FREMARKS 
FROM T_GL_VOUCHER V
JOIN TMP T ON T.FVOUCHERID=V.FVOUCHERID

刷新后，查询数据看效果：

SELECT TOP 100 FREMARKS, * FROM T_GL_VOUCHER

4，加触发器实现凭证内容变化时自动更新备注字段的内容，这样确保单据头备注的内容和分录中摘要的内容一致。

--删除原有触发器（如果有的话）
DROP TRIGGER TRG_REMARKS_UPDATE_INSERT;
--创建更新和插入触发器
CREATE TRIGGER TRG_REMARKS_UPDATE_INSERT
ON T_GL_VOUCHERENTRY
FOR UPDATE,INSERT
AS 
UPDATE V SET V.FREMARKS=T.FREMARKS FROM T_GL_VOUCHER V
JOIN (SELECT TMAIN.FVOUCHERID, STUFF((
    SELECT '；'+FEXPLANATION FROM (
            SELECT VE.FVOUCHERID,VE.FEXPLANATION 
            FROM T_GL_VOUCHERENTRY VE
            WHERE VE.FEXPLANATION IS NOT NULL) TEXP 
        WHERE TEXP.FVOUCHERID=TMAIN.FVOUCHERID FOR XML PATH('')
    ),1,1,'') AS FREMARKS
FROM (SELECT DISTINCT V.FVOUCHERID FROM INSERTED V) TMAIN
) T ON T.FVOUCHERID=V.FVOUCHERID

测试效果，来到凭证查询界面，将原有查询条件中的按分录行摘要包含某个关键词的条件改成按单据头备注包含该关键字后查询。实际查询结果和原来一样，时间大大缩短。