记一次数据库查询优化记录
数据量
先看下关联的表数据量
SQL 全选
select count(1) from osap_data
select count(1) from osap_dataLog
select count(1) from osap_dataT
select count(1) from osap_dataUser
select count(1) from osap_dataUserDetail查询结果：
现状
执行SQL语句
SQL 全选
dbo.proc_osap_MonitorITemUserReciveCfg_List_ImportTag @ClientID = 'xmhf', -- varchar(50)
@SystemID = '0008', -- varchar(50)
@UserIDs = '', -- varchar(max)
@ImportTag='20211025235539xmhf0008ZRWM010',
@MonitorItemIDs = '' -- varchar(max)运行了10分钟，还没有运行完成，具体时间未可知，可以得出的结论是，这个查询时间肯定是不可接受得
优化日志
这么长时间得查询等待，追踪实际执行计划是不行了，( 太慢了 )
分析存储过程，问题语句1
SQL 全选
SELECT * FROM dbo.osap_dataLog AS b WHERE b.Tag='20211025235539xmhf0008ZRWM010'执行耗时
就一个查询而已，觉得这个可以优化
查询得知，在存储过程中，这张表关联得查询有三处
字段有 Tag，DATAID
所以检查osap_dataLog 索引
没有任何索引信息，那么这里我们创建索引，字段为 Tag和DataID
1）osap_dataLog 新建索引
这里由于osap_dataLog数据量太大（53015612），是无法在表设计器中直接保存的，会报超时错误，所以我们要在设计器中生成更改脚本，然后运行，具体参考：SQL表数据多的时候创建索引失败，提示：- 无法创建索引“idx_**”。执行超时已过期。完成操作之前已超时或服务器未响应。
索引的数据库脚本
SQL 全选
/* 为了防止任何可能出现的数据丢失问题，您应该先仔细检查此脚本，然后再在数据库设计器的上下文之外运行此脚本。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE NONCLUSTERED INDEX IX_osap_dataLog ON dbo.osap_dataLog
(
Tag,
DATAID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.osap_dataLog SET (LOCK_ESCALATION = TABLE)
GO
COMMIT脚本执行花费了 10分钟，(漫长)
然后再次执行语句看看效果
SQL 全选
SELECT * FROM dbo.osap_dataLog AS b WHERE b.Tag='20211025235539xmhf0008ZRWM010'瞬间完成，是一个好的开始
执行一下存储过程试试
SQL 全选
dbo.proc_osap_MonitorITemUserReciveCfg_List_ImportTag @ClientID = 'xmhf', -- varchar(50)
@SystemID = '0008', -- varchar(50)
@UserIDs = '', -- varchar(max)
@ImportTag='20211025235539xmhf0008ZRWM010',
@MonitorItemIDs = '' -- varchar(max)优化前，这个存储过程执行了十多分钟，都还没查出数据
现在，只需要 16秒，16秒
版权声明：本文为YES开发框架网发布内容,转载请附上原文出处连接
post 管理员
