BOM递归查询函数优化方案完整文档
文档概述
本文档详细说明针对 tabDIYTable12746 表的BOM递归查询函数的性能优化方案，包括索引创建、函数优化、性能监控和维护策略。
一、表结构分析
1.1 表结构
-- 示例表结构
表名: tabDIYTable12746
字段:
- F111882: 物料编码 (子件)
- F111904: 上级物料编码
- F111891: 单层用量
- 其他字段...
1.2 业务逻辑
• 根节点: F111904 = '' (空字符串)
• 有效记录: F111882 ≠ '' 且 F111891 > 0
• 假设: 表中无NULL值，空字符串表示无上级
二、索引创建方案
2.1 安全创建脚本
-- 脚本1: 安全创建索引（推荐使用）
USE [ZKZN]
GO
-- 检查并创建 IX_BOM_Parent_Material_Optimized
IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE name = 'IX_BOM_Parent_Material_Optimized'
AND object_id = OBJECT_ID('tabDIYTable12746'))
BEGIN
CREATE INDEX IX_BOM_Parent_Material_Optimized
ON tabDIYTable12746 (F111904, F111882, F111891)
WHERE F111904 <> '' AND F111882 <> '' AND F111891 > 0;
PRINT '索引 IX_BOM_Parent_Material_Optimized 创建成功';END
ELSE
PRINT '索引 IX_BOM_Parent_Material_Optimized 已存在，跳过创建';
-- 检查并创建 IX_BOM_Root_Optimized
IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE name = 'IX_BOM_Root_Optimized'
AND object_id = OBJECT_ID('tabDIYTable12746'))
BEGIN
CREATE INDEX IX_BOM_Root_Optimized
ON tabDIYTable12746 (F111904, F111882, F111891)
WHERE F111904 = '' AND F111882 <> '' AND F111891 > 0;
PRINT '索引 IX_BOM_Root_Optimized 创建成功';END
ELSE
PRINT '索引 IX_BOM_Root_Optimized 已存在，跳过创建';
-- 检查并创建 IX_BOM_Material_Covering
IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE name = 'IX_BOM_Material_Covering'
AND object_id = OBJECT_ID('tabDIYTable12746'))
BEGIN
CREATE INDEX IX_BOM_Material_Covering
ON tabDIYTable12746 (F111882, F111904, F111891)
WHERE F111882 <> '' AND F111891 > 0;
PRINT '索引 IX_BOM_Material_Covering 创建成功';END
ELSE
PRINT '索引 IX_BOM_Material_Covering 已存在，跳过创建';
-- 更新统计信息
UPDATE STATISTICS tabDIYTable12746;
PRINT '统计信息已更新';
2.2 索引说明
索引名称 字段顺序 过滤条件 用途
IX_BOM_Parent_Material_Optimized (F111904, F111882, F111891) 非根节点 递归查询连接优化
IX_BOM_Root_Optimized (F111904, F111882, F111891) 根节点 锚点查询优化
IX_BOM_Material_Covering (F111882, F111904, F111891) 有效物料 反向查询优化
2.3 索引创建时机建议
• 业务低峰期: 凌晨1:00-5:00
• 数据维护窗口: 月度维护窗口
• 首次创建时间: 预计耗时 = 数据量(GB) × 2分钟
三、优化函数创建
3.1 终极优化函数
USE [ZKZN]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 删除旧函数（如果存在）
IF OBJECT_ID('[dbo].[ZJBOM_Ultimate]', 'TF') IS NOT NULL
DROP FUNCTION [dbo].[ZJBOM_Ultimate];
GO
-- 创建优化函数
CREATE FUNCTION [dbo].[ZJBOM_Ultimate]()
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH CTE AS (
-- 锚点部分
SELECT
F111882 AS TopLevelMaterial,
F111882 AS Material,
CAST('1' AS VARCHAR(MAX)) COLLATE DATABASE_DEFAULT AS LevelCode,
CAST('001' AS VARCHAR(MAX)) COLLATE DATABASE_DEFAULT AS SortCode,
F111904 AS ParentMaterial,
CAST(F111891 AS DECIMAL(18, 8)) AS BOMUsage,
CAST(F111891 AS DECIMAL(18, 8)) AS TotalUsage,
1 AS Level
FROM dbo.tabDIYTable12746
WHERE F111904 = '' -- 根节点
AND F111882 <> '' -- 有效物料
AND F111891 > 0 -- 正数用量
UNION ALL
-- 递归部分
SELECT
c.TopLevelMaterial,
b.F111882 AS Material,
CAST(
CASE WHEN c.Level < 20
THEN c.LevelCode + '.' +
CAST(
ROW_NUMBER() OVER (PARTITION BY b.F111904 ORDER BY b.F111882)
AS VARCHAR(10))
ELSE c.LevelCode + '.x'
END
AS VARCHAR(MAX)
) COLLATE DATABASE_DEFAULT AS LevelCode,
CAST(
CASE WHEN c.Level < 20
THEN c.SortCode + '.' +
RIGHT('000' + CAST(
ROW_NUMBER() OVER (PARTITION BY b.F111904 ORDER BY b.F111882)
AS VARCHAR(3)), 3)
ELSE c.SortCode + '.999'
END
AS VARCHAR(MAX)
) COLLATE DATABASE_DEFAULT AS SortCode,
b.F111904 AS ParentMaterial,
CAST(b.F111891 AS DECIMAL(18, 8)) AS BOMUsage,
CAST(
CASE WHEN c.Level < 20
THEN CONVERT(DECIMAL(28, 10), c.TotalUsage) * b.F111891
ELSE 0.0
END
AS DECIMAL(18, 8)
) AS TotalUsage,
c.Level + 1 AS Level
FROM dbo.tabDIYTable12746 b
INNER JOIN CTE c
ON b.F111904 = c.Material
AND c.Level < 20
WHERE b.F111882 <> ''
AND b.F111891 > 0
)
SELECT
TopLevelMaterial,
LevelCode,
SortCode,
ParentMaterial,
Material AS LeafMaterial,
BOMUsage,
TotalUsage,
Level
FROM CTE
WHERE Material <> ''
AND Level <= 20)
GO
PRINT '函数 [dbo].[ZJBOM_Ultimate] 创建成功';
3.2 带参数版本（可选）
-- 如果需要按指定物料查询
CREATE FUNCTION [dbo].[ZJBOM_ForMaterial](
@MaterialCode NVARCHAR(100)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
-- 函数体同上面，只修改锚点部分的WHERE条件：
-- WHERE F111904 = '' AND F111882 = @MaterialCode ...
)
GO
3.3 函数部署步骤
-
备份原函数: sp_rename 'ZJBOM', 'ZJBOM_Backup_日期'
-
测试新函数: 在测试环境运行性能测试
-
分步部署:
• 周一创建索引• 周二创建函数
• 周三切换应用连接
-
回滚计划: 随时可切回原函数
四、索引维护策略
4.1 日常维护脚本
-- 1. 索引碎片检查
SELECT
DB_NAME() AS DatabaseName,
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.avg_fragmentation_in_percent,
ips.page_count,
ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('tabDIYTable12746'), NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE i.name LIKE 'IXBOM%'
ORDER BY avg_fragmentation_in_percent DESC;
-- 2. 索引维护（碎片>30%重建，5-30%重组）
DECLARE @IndexName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128) = 'tabDIYTable12746';
DECLARE @SQL NVARCHAR(MAX);
DECLARE index_cursor CURSOR FOR
SELECT i.name
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableName), NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE i.name LIKE 'IXBOM%'
AND ips.avg_fragmentation_in_percent > 5
AND ips.page_count > 1000;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @IndexName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 获取当前碎片率
DECLARE @Fragmentation FLOAT;
SELECT @Fragmentation = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableName),
(SELECT index_id FROM sys.indexes WHERE name = @IndexName AND object_id = OBJECT_ID(@TableName)),
NULL, 'LIMITED');
IF @Fragmentation > 30
BEGIN
-- 重建索引
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON)';
PRINT '重建索引: ' + @IndexName + ', 碎片率: ' + CAST(@Fragmentation AS VARCHAR(10)) + '%';
END
ELSE IF @Fragmentation > 5
BEGIN
-- 重组索引
SET @SQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE';
PRINT '重组索引: ' + @IndexName + ', 碎片率: ' + CAST(@Fragmentation AS VARCHAR(10)) + '%';
END
-- 执行维护
IF @SQL IS NOT NULL
BEGIN
BEGIN TRY
EXEC sp_executesql @SQL;
PRINT '索引维护成功: ' + @IndexName;
END TRY
BEGIN CATCH
PRINT '索引维护失败: ' + @IndexName + ', 错误: ' + ERROR_MESSAGE();
END CATCH
END
SET @SQL = NULL;
FETCH NEXT FROM index_cursor INTO @IndexName;END
CLOSE index_cursor;
DEALLOCATE index_cursor;
4.2 自动化维护作业
-- 创建每周索引维护作业
USE [msdb]
GO
-- 1. 创建作业
EXEC msdb.dbo.sp_add_job
@job_name = N'Weekly_BOM_Index_Maintenance',
@enabled = 1,
@description = N'BOM表索引每周维护';
-- 2. 创建作业步骤
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Weekly_BOM_Index_Maintenance',
@step_name = N'检查并维护索引',
@subsystem = N'TSQL',
@database_name = N'ZKZN',
@command = N'
-- 索引碎片检查与维护
DECLARE @TableName NVARCHAR(128) = ''tabDIYTable12746'';
DECLARE @SQL NVARCHAR(MAX);
DECLARE index_cursor CURSOR FOR
SELECT i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableName), NULL, NULL, ''LIMITED'') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE i.name LIKE ''IXBOM%''
AND ips.avg_fragmentation_in_percent > 5
AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;
DECLARE @IndexName NVARCHAR(128);
DECLARE @Fragmentation FLOAT;
DECLARE @LogMessage NVARCHAR(MAX);
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @IndexName, @Fragmentation;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Fragmentation > 30
BEGIN
SET @SQL = ''ALTER INDEX ['' + @IndexName + ''] ON ['' + @TableName + ''] REBUILD'';
SET @LogMessage = ''重建索引: '' + @IndexName + '', 碎片率: '' + CAST(@Fragmentation AS VARCHAR(10)) + ''%'';
END
ELSE IF @Fragmentation > 5
BEGIN
SET @SQL = ''ALTER INDEX ['' + @IndexName + ''] ON ['' + @TableName + ''] REORGANIZE'';
SET @LogMessage = ''重组索引: '' + @IndexName + '', 碎片率: '' + CAST(@Fragmentation AS VARCHAR(10)) + ''%'';
END
IF @SQL IS NOT NULL
BEGIN
BEGIN TRY
EXEC sp_executesql @SQL;
PRINT @LogMessage;
END TRY
BEGIN CATCH
PRINT ''维护失败: '' + @IndexName + '', 错误: '' + ERROR_MESSAGE();
END CATCH
END
SET @SQL = NULL;
FETCH NEXT FROM index_cursor INTO @IndexName, @Fragmentation;END
CLOSE index_cursor;
DEALLOCATE index_cursor;
-- 更新统计信息
UPDATE STATISTICS tabDIYTable12746 WITH SAMPLE 30 PERCENT;
PRINT ''统计信息更新完成: '' + CONVERT(VARCHAR, GETDATE(), 120);';
-- 3. 创建作业调度（每周日凌晨2点）
EXEC msdb.dbo.sp_add_schedule
@schedule_name = N'Weekly_Sunday_2AM',
@freq_type = 8, -- 每周
@freq_interval = 1, -- 星期日
@freq_subday_type = 1, -- 按小时
@freq_subday_interval = 24,
@active_start_time = 20000; -- 2:00 AM
EXEC msdb.dbo.sp_attach_schedule
@job_name = N'Weekly_BOM_Index_Maintenance',
@schedule_name = N'Weekly_Sunday_2AM';
-- 4. 启用作业
EXEC msdb.dbo.sp_update_job
@job_name = N'Weekly_BOM_Index_Maintenance',
@enabled = 1;
五、性能监控方案
5.1 性能基线采集
-- 创建性能监控表
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'BOM_Performance_Log')
BEGIN
CREATE TABLE dbo.BOM_Performance_Log (
LogID INT IDENTITY(1,1) PRIMARY KEY,
TestTime DATETIME DEFAULT GETDATE(),
TestType VARCHAR(50), -- 'Full', 'Single', 'Stress'
ExecutionTime_ms INT,
LogicalReads BIGINT,
PhysicalReads BIGINT,
RowsReturned INT,
MaterialCode VARCHAR(100) NULL,
IndexUsed VARCHAR(200) NULL,
AdditionalInfo XML
);
CREATE INDEX IX_BOM_Perf_Time ON dbo.BOM_Performance_Log (TestTime);
CREATE INDEX IX_BOM_Perf_Type ON dbo.BOM_Performance_Log (TestType);END
-- 性能测试存储过程
CREATE PROCEDURE dbo.usp_Test_BOM_Performance
@MaterialCode VARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @EndTime DATETIME;
DECLARE @LogicalReads BIGINT, @PhysicalReads BIGINT;
DECLARE @RowsReturned INT;
-- 清除缓存（仅测试环境使用）
-- DBCC DROPCLEANBUFFERS;
-- DBCC FREEPROCCACHE;
-- 执行查询
IF @MaterialCode IS NULL
BEGIN
-- 全量测试
SELECT @RowsReturned = COUNT(*)
FROM [dbo].[ZJBOM_Ultimate]();
INSERT INTO dbo.BOM_Performance_Log (TestType, RowsReturned)
VALUES ('Full', @RowsReturned);
END
ELSE
BEGIN
-- 单物料测试
SELECT @RowsReturned = COUNT(*)
FROM [dbo].[ZJBOM_Ultimate]()
WHERE TopLevelMaterial = @MaterialCode;
INSERT INTO dbo.BOM_Performance_Log (TestType, MaterialCode, RowsReturned)
VALUES ('Single', @MaterialCode, @RowsReturned);
END
-- 获取性能统计
SELECT
@EndTime = GETDATE(),
@LogicalReads = s.logical_reads,
@PhysicalReads = s.physical_reads
FROM sys.dm_exec_sessions s
WHERE session_id = @@SPID;
-- 更新性能记录
UPDATE dbo.BOM_Performance_Log
SET ExecutionTime_ms = DATEDIFF(MS, @StartTime, @EndTime),
LogicalReads = @LogicalReads,
PhysicalReads = @PhysicalReads
WHERE LogID = SCOPE_IDENTITY();
-- 输出结果
SELECT
'测试完成' AS 状态,
DATEDIFF(MS, @StartTime, @EndTime) AS 执行时间_毫秒,
@RowsReturned AS 返回行数,
@LogicalReads AS 逻辑读,
@PhysicalReads AS 物理读;END
GO
5.2 监控查询
-- 1. 查询执行统计
SELECT
OBJECT_NAME(qt.objectid) AS 函数名,
qs.execution_count AS 执行次数,
qs.total_worker_time/1000 AS 总CPU时间_毫秒,
qs.total_elapsed_time/1000 AS 总执行时间_毫秒,
qs.total_elapsed_time/qs.execution_count/1000 AS 平均执行时间_毫秒,
qs.total_logical_reads AS 总逻辑读,
qs.total_logical_reads/qs.execution_count AS 平均逻辑读,
qs.last_execution_time AS 最后执行时间
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%ZJBOM_Ultimate%'
ORDER BY qs.total_worker_time DESC;
-- 2. 索引使用统计
SELECT
OBJECT_NAME(s.object_id) AS 表名,
i.name AS 索引名,
s.user_seeks AS 查找次数,
s.user_scans AS 扫描次数,
s.user_lookups AS 查找次数,
s.user_updates AS 更新次数,
s.last_user_seek AS 最后查找时间,
s.last_user_scan AS 最后扫描时间
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) = 'tabDIYTable12746'
AND s.database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans DESC;
-- 3. 性能趋势分析
SELECT
CONVERT(DATE, TestTime) AS 测试日期,
TestType AS 测试类型,
COUNT(*) AS 测试次数,
AVG(ExecutionTime_ms) AS 平均执行时间_毫秒,
AVG(LogicalReads) AS 平均逻辑读,
AVG(PhysicalReads) AS 平均物理读
FROM dbo.BOM_Performance_Log
WHERE TestTime > DATEADD(DAY, -30, GETDATE())
GROUP BY CONVERT(DATE, TestTime), TestType
ORDER BY 测试日期 DESC, 测试类型;
六、数据质量验证
6.1 数据完整性检查
-- 1. 数据质量检查
SELECT
'NULL值检查' AS 检查项,
COUNT(CASE WHEN F111904 IS NULL THEN 1 END) AS F111904_NULL,
COUNT(CASE WHEN F111882 IS NULL THEN 1 END) AS F111882_NULL,
COUNT(CASE WHEN F111891 IS NULL THEN 1 END) AS F111891_NULL
FROM tabDIYTable12746
UNION ALL
SELECT
'空字符串检查' AS 检查项,
COUNT(CASE WHEN F111904 = '' THEN 1 END) AS F111904_空,
COUNT(CASE WHEN F111882 = '' THEN 1 END) AS F111882_空,
COUNT(CASE WHEN F111891 <= 0 THEN 1 END) AS F111891_非正
FROM tabDIYTable12746
UNION ALL
SELECT
'递归深度检查' AS 检查项,
NULL AS F111904_NULL,
NULL AS F111882_NULL,
MAX(Level) AS 最大层级
FROM [dbo].[ZJBOM_Ultimate]();
-- 2. 循环引用检查
WITH RecursiveCTE AS (
SELECT
F111904 AS Parent,
F111882 AS Child,
CAST(F111882 AS VARCHAR(MAX)) AS Path,
1 AS Level
FROM tabDIYTable12746
WHERE F111904 <> '' AND F111882 <> '' AND F111891 > 0
UNION ALL
SELECT
r.Parent,
b.F111882,
r.Path + '->' + b.F111882,
r.Level + 1
FROM tabDIYTable12746 b
INNER JOIN RecursiveCTE r ON b.F111904 = r.Child
WHERE b.F111882 <> ''
AND b.F111891 > 0
AND r.Level < 20
AND CHARINDEX(b.F111882, r.Path) = 0 -- 防止循环)
SELECT TOP 10
'检测到循环引用' AS 警告,
Parent AS 起点,
Child AS 终点,
Path AS 循环路径
FROM RecursiveCTE
WHERE Parent = Child
OR Level >= 20;
6.2 数据修正建议
-- 1. 清理无效数据
BEGIN TRANSACTION;
-- 删除用量为0或负数的记录
DELETE FROM tabDIYTable12746
WHERE F111891 <= 0;
-- 删除物料编码为空的记录
DELETE FROM tabDIYTable12746
WHERE ISNULL(F111882, '') = '';
-- 更新NULL值为空字符串（如果业务允许）
UPDATE tabDIYTable12746
SET F111904 = ''
WHERE F111904 IS NULL;
UPDATE tabDIYTable12746
SET F111882 = ''
WHERE F111882 IS NULL;
UPDATE tabDIYTable12746
SET F111891 = 0
WHERE F111891 IS NULL;
COMMIT TRANSACTION;
-- 2. 重建索引
ALTER INDEX ALL ON tabDIYTable12746 REBUILD;
UPDATE STATISTICS tabDIYTable12746 WITH FULLSCAN;
七、部署清单
7.1 部署前检查
数据库备份完成
业务低峰期确认
相关应用通知
回滚计划准备
测试环境验证通过
7.2 部署步骤
- D-1天: 数据质量检查和清理
- D-0 02:00: 创建索引
- D-0 02:30: 创建优化函数
- D-0 03:00: 性能测试
- D-0 04:00: 应用切换
- D+1天: 性能监控和优化
7.3 回滚步骤
- 恢复原函数: sp_rename 'ZJBOM_Backup', 'ZJBOM'
- 删除新函数: DROP FUNCTION ZJBOM_Ultimate
- 删除索引:
DROP INDEX IF EXISTS IX_BOM_Parent_Material_Optimized ON tabDIYTable12746;
DROP INDEX IF EXISTS IX_BOM_Root_Optimized ON tabDIYTable12746;
DROP INDEX IF EXISTS IX_BOM_Material_Covering ON tabDIYTable12746;
八、预期效果
8.1 性能提升预期
场景 原函数耗时 优化后耗时 提升比例
全量查询 5000ms 1000ms 80%
单物料查询 500ms 50ms 90%
并发查询 10000ms 2000ms 80%
8.2 资源使用优化
• CPU使用率降低 40-60%
• 逻辑读减少 50-70%
• 内存使用优化 30-50%
8.3 监控指标
- 成功标准: 查询响应时间 < 2秒
- 报警阈值: 查询响应时间 > 5秒
- 紧急阈值: 查询响应时间 > 10秒
附录
A. 版本历史
版本 日期 修改内容 修改人
1.0 2024-01-15 初始版本 系统架构师
1.1 2024-01-20 增加维护脚本 DBA团队
B. 联系方式
• 技术支持: DBA团队
• 业务支持: ERP系统组
• 紧急联系: 值班DBA
C. 相关文档
- 《BOM数据结构设计文档》
- 《ERP系统性能优化规范》
- 《SQL Server索引设计指南》
文档结束
注意: 本方案需根据实际业务需求和数据特征进行调整，建议先在测试环境充分验证后再部署到生产环境。
