SQL Server 2005 游标跑的很慢!

最新推荐文章于 2024-05-29 14:58:12 发布
原创 最新推荐文章于 2024-05-29 14:58:12 发布 · 3.3k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql server #parameters #insert #测试 #存储 #微软

本文通过对比分析在SQL Server Management Studio中使用游标的性能表现，发现将游标封装到存储过程中可以显著提升运行效率。文章详细记录了游标在不同场景下的运行时间，并探讨了事务设置对性能的影响。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 本人使用SQL Server Management Studio 在查询界面中写了两个游标, 份别跑了30分钟和6分钟.

 这两个游标都是只读前进地提取.涉及数量9万和5千条,也就是说提取次数.

 无论对要提取的表增加索引,还是修改Cursor的类型,都无济于事!  微软不会那么差劲吧?

 

 后来感觉放进存储器起跑,确实很快.整个跑下来才1分钟48秒.!

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
 <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from


默认情况下没有一下事务设置的

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

    -- Insert statements for procedure here
 SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

 

 今天单独拿出一部分游标来测试。分别放在两个查询框中。有个有事务设置，另外个没有

结果有的跑了34秒，没有的跑了6分11秒 

Sqlserver 游标 慢
水煮鱼的博客
11-17 3090
.net项目中有个模块做统计功能，原先方法速度很慢，所以需要改进，统计结果如下图： 下图接上图后面： 原先的处理方式是，这些数据分别涉及到四五张表，前台从数据库中查询出需要的数据集，然后分别遍历这些数据集拼接html字符串显示在界面上。 优化思考： 由于前台需要多次调用数据库，试想把改功能封装成一个存储过程实现，前台负责传输参数，有存储过程计算拼接之后返回html结果。 其实对于
SQL Server 使用游标嵌套循环
qq_41971373的博客
04-08 2965
关于SQL Server 游标嵌套 关于SQL Server 游标嵌套 作者：灵感联盟 游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。 ...
php sqlserver游标很慢问题,sqlserver游标概念与实例
weixin_33958381的博客
03-13 445
我们先不讲游标的什么概念，步骤及语法，先来看一个例子： 表一 OriginSalary 表二 AddSalary 现在有2张表，一张是OriginSalary表--工资表，有三个字段0_ID 员工号(NVARCHAR)、O_Name员工姓名(NVARCHAR)、O_Salary工资(FLOAT)。 另一张表AddSalary表—我们先不讲游标的什么概念，步骤及语法，先来看一个例子： 表一...
存储过程执行快，程序调用执行慢的问题
热门推荐
徐本锡的专栏
06-18 121万+
源博客地址https://blog.youkuaiyun.com/pgbiao/article/details/22388945 其他参考：参数探测(Parameter Sniffing)影响存储过程执行效率解决方案 这篇文章对参数嗅探问题作了很详细的研究https://www.cnblogs.com/lyhabc/articles/3222179.html 这两天遇到一个问题令人比较郁闷，一个大概120行左右的存储过程在SQL Server的查询分析器里面执行， 速度非常理想，1秒不到，即可筛选抓取到大概...
sql server语句优化1：去掉游标
小李子的博客
05-29 275
sql语句优化 取消嵌套游标 批量执行
什么是Sql Server游标
weixin_47945234的博客
05-26 1760
什么是Sql Sever游标 游标的概述 在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，
PHP与SQL Server数据库游标：灵活遍历数据，提升数据处理效率
![PHP与SQL Server数据库游标：灵活遍历数据，提升数据处理效率]... PHP与SQL Server游标编程 ### 2.1 游标的创建和使用 #### 2.1.1 游标的声明和初始化 在PHP中，使用 `sqlsrv
SQL Server优化
2301_78102191的博客
07-04 5532
页拆分的后果是降低了索引的性能，因而使用该索引的查询会导致数据存储的支离破碎。A、 查看SQL语句的执行计划，可以在查询分析其使用CTRL+L图形化的显示执行计划，一般应该注意百分比最大的几个图形的属性，把鼠标移动到其上面会显示这个图形的属性，需要注意预计成本的数据，也要注意其标题，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出现SCAN说明语句有优化的余地。
SQL 执行得很慢的原因
ChineseSoftware的博客
03-02 5901
这个问题涉及到 MySQL 很多核心知识，如同测验计算机网络知识时，问“输入 URL 回车之后，究竟发生了什么”一样，就看能说出多少了。 一、执行很慢，分两种情况 大多数情况是正常的，只是偶尔出现很慢的情况。 在数据量不变的情况下，这条 SQL 一直以来都执行的很慢。 二、偶尔很慢的情况 一条 SQL 偶尔出现很慢的情况，该 SQL 的书写本身问题不大，而是其他原因造成的，那会是什么原因呢？ 1️⃣数据库在刷新脏页 当在数据库中插入或者更新一条数据的时候，数据库会在内存中把对应字段的数据更新了。更新之后
SQL Server查询速度慢原因及优化方法
weixin_33743703的博客
12-22 667
SQL Server数据库查询速度慢的原因有很多，常见的有以下几种： 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、s...
php sqlserver游标很慢问题,SQLSERVER游标及循环语句
weixin_39962341的博客
03-13 423
整理一下，关于游标，MSDN有：过 Transact-SQL 服务器游标检索特定行。参数NEXT紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。PRIOR返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行...
游标sql server_SQL Server游标性能问题
culuo4781的博客
07-27 1901
游标sql server 介绍 (Introduction) 在上一篇文章中，我们讨论了如何设置基本游标。 我们解释了游标是基于行的操作，它采用给定的SELECT语句并将数据处理分解为循环执行。 没有数据库管理背景或在学习时不喜欢数据库类的人通常会发现SQL Server游标很方便并且编写起来很有趣。 这是因为它们摆脱了编写大多数T-SQL脚本时常见的基于集合的逻辑的束缚。 出于这个原因，我们经常...
php sqlserver游标很慢问题,SqlServer之游标深入
weixin_28679635的博客
03-13 331
上一节我们简单讲解了SqlServer的游标的简单创建，本节我们将根据msdn上的创建游标代码来详细讲解游标的创建、以及其特性。1 DECLARE cursor_name CURSOR [LOCAL | GLOBAL]2 [FORWARD_ONLY | SCROLL]3 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD]4 [READ...
mssql 游标和循环效率比较_存储过程-使用游标、临时表、表变量、实现对表中数据进行操作...
weixin_39980903的博客
12-04 1068
工作中经常会遇到批量处理一些数据，一般的方法可以使用代码开发相应的功能，在前端操作相应的功能，但是如果操作的数据不是特别的复杂，但是数据量特别的大，在客户端操作就会由于操作时间等待过长而导致客户体验度降低，这时我们就要考虑采用数据库中的存储过程进行操作，存储过程的优点我再这里就不再多介绍。下面分别介绍游标和临时表两种方法对数据库中的表进行循环操作：1、游标：分类：MS SQL SERVER 支持三...
sqlserver存储过程影响客户端执行慢是什么原因
最新发布
04-02
<think>嗯，用户的问题是关于SQL Server存储过程导致客户端执行变慢的原因和解决方法。我需要先理解存储过程在什么情况下会导致客户端变慢。存储过程本身通常被认为是优化性能的手段，但有时候执行起来反而慢，可能有哪些原因呢？ 首先，存储过程本身的执行效率可能有问题。比如，里面的查询没有优化，缺少索引，或者有复杂的计算。另外，存储过程可能生成了大量中间结果，比如临时表或者游标，这些都会占用资源。还有可能是参数嗅探的问题，当存储过程第一次编译时使用的参数不适合后续的执行计划，导致性能下降。 然后，网络传输也是一个因素。如果存储过程返回的结果集太大，客户端需要等待数据传输完成，这会让用户感觉慢。比如，如果存储过程返回了百万行数据，网络带宽不足或者延迟高的话，传输时间就会很长。 客户端处理逻辑的问题也可能导致变慢。比如，客户端可能在获取数据后进行了复杂的处理，或者在循环中频繁调用存储过程，增加了整体时间。此外，应用程序设计不合理，比如没有使用分页而一次性加载大量数据，也会让客户端卡顿。 资源竞争方面，服务器上的CPU、内存、磁盘IO如果不足，存储过程执行时会变慢。比如，内存不足导致频繁的磁盘交换，或者磁盘IO瓶颈让查询变慢。同时，阻塞和死锁也会让存储过程执行时间延长，客户端自然觉得慢。 解决方案的话，应该从存储过程优化开始。检查里面的SQL语句，确保有合适的索引，避免全表扫描。减少临时表和游标的使用，改用更高效的集合操作。处理参数嗅探问题，可以使用局部变量或者OPTIMIZE FOR提示。另外，控制返回的数据量，比如添加分页参数，只返回必要的数据。 网络传输优化方面，可以压缩结果集，或者使用异步查询，让客户端在后台获取数据。客户端处理逻辑需要优化，比如减少不必要的循环调用，使用缓存避免重复执行相同存储过程。分批处理大数据，而不是一次性处理。 资源调优方面，调整SQL Server的内存配置，比如设置最大内存限制，避免资源争抢。优化磁盘配置，使用SSD或者分离数据文件和日志文件。监控和解决阻塞、死锁问题，使用适当的隔离级别和索引减少锁竞争。 另外，定期维护数据库，比如重建索引、更新统计信息，确保执行计划正确。检查存储过程的编译和重编译情况，避免频繁重编译带来的开销。使用性能监控工具，如SQL Server Profiler，找出慢的查询并进行优化。 可能还需要考虑数据库设计和架构调整，比如分区表处理大数据，读写分离分散压力，引入缓存层减少数据库访问次数。如果存储过程中的业务逻辑过于复杂，可以拆分到应用层，或者使用CLR集成来优化计算密集型操作。 用户提供的引用中也提到了一些点，比如使用存储过程减少网络开销，避免返回大的结果集，配置虚拟内存和服务器内存参数，重建索引和收缩数据库等。这些都需要综合起来分析，找到导致性能瓶颈的具体原因，然后针对性地解决。</think>### SQL Server存储过程执行缓慢的原因分析及解决方案 #### 一、根本原因分析 1. **存储过程执行效率问题** - 存在未优化的查询语句（如全表扫描、缺失索引） - 复杂计算或临时表过度使用（如使用游标循环处理数据） - 参数嗅探（Parameter Sniffing）导致执行计划不匹配[^1][^4] 2. **网络传输瓶颈** - 返回结果集过大（如未分页的百万级数据） - 客户端与服务器间高延迟或带宽不足[^1][^3] 3. **客户端处理逻辑缺陷** - 数据后处理耗时（如内存中复杂计算） - 频繁调用存储过程（如循环内嵌套调用）[^3] 4. **资源竞争与配置问题** - 服务器内存/CPU资源不足（如未合理配置`max server memory`） - 磁盘I/O瓶颈（如日志文件与数据文件共用物理磁盘）[^2] #### 二、针对性解决方案 ##### (一) 存储过程优化 1. **查询优化** - 添加缺失索引（通过`EXEC sp_helpindex`分析） - 使用`SET STATISTICS IO ON`检查逻辑读次数 - 示例：将游标改为集合操作 ```sql -- 原始游标方式 DECLARE @id INT DECLARE cur CURSOR FOR SELECT id FROM table OPEN cur FETCH NEXT FROM cur INTO @id WHILE @@FETCH_STATUS = 0 BEGIN -- 单行处理逻辑 FETCH NEXT FROM cur INTO @id END CLOSE cur DEALLOCATE cur -- 优化为集合操作 UPDATE table SET column = calculation FROM table ``` 2. **参数嗅探处理** ```sql CREATE PROCEDURE usp_GetData @Param1 INT WITH RECOMPILE -- 强制重新编译 AS BEGIN SELECT * FROM table WHERE column = @Param1 END ``` 或使用局部变量隔离参数： ```sql CREATE PROCEDURE usp_GetData @Param1 INT AS BEGIN DECLARE @LocalParam INT = @Param1 SELECT * FROM table WHERE column = @LocalParam END ``` 3. **结果集控制** ```sql CREATE PROCEDURE usp_GetPagedData @PageSize INT = 50, @PageNumber INT = 1 AS BEGIN SELECT * FROM table ORDER BY id OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY END ``` ##### (二) 网络传输优化 1. **数据压缩** ```sql -- 启用压缩（SQL Server 2008+） CREATE PROCEDURE usp_GetCompressedData AS BEGIN SELECT * FROM table OPTION (COMPRESS_ROW) END ``` 2. **异步查询模式** （C#示例） ```csharp using (SqlCommand cmd = new SqlCommand("usp_LongProcess", connection)) { cmd.CommandType = CommandType.StoredProcedure; connection.Open(); IAsyncResult result = cmd.BeginExecuteNonQuery(); // 执行其他操作 cmd.EndExecuteNonQuery(result); } ``` ##### (三) 客户端优化 1. **批量处理优化** ```csharp // 错误方式：逐条提交 foreach(var item in list) { ExecuteProc("usp_ProcessItem", item.ID); } // 正确方式：批量提交 DataTable dt = BuildBulkDataTable(list); ExecuteProc("usp_ProcessBatch", dt); ``` 2. **缓存策略** ```sql CREATE PROCEDURE usp_GetCachedData AS BEGIN IF OBJECT_ID('tempdb..##CachedData') IS NOT NULL SELECT * FROM ##CachedData ELSE BEGIN SELECT * INTO ##CachedData FROM source_table SELECT * FROM ##CachedData END END ``` ##### (四) 服务器资源调优 1. **内存配置公式** $$ \text{max server memory} = \text{Physical Memory} \times 0.75 $$ 通过以下命令设置： ```sql EXEC sys.sp_configure N'max server memory (MB)', 12288 RECONFIGURE ``` 2. **磁盘优化组合** | 文件类型 | 推荐磁盘类型 | RAID级别 | |----------------|--------------|----------| | 数据文件 | SSD | RAID 10 | | 事务日志 | SAS 15k RPM | RAID 1 | | TempDB | NVMe | RAID 0 | #### 三、深度优化工具链 1. **执行计划分析** ```sql SET SHOWPLAN_XML ON EXEC usp_ProblemProcedure SET SHOWPLAN_XML OFF ``` 2. **扩展事件监控** ```sql CREATE EVENT SESSION [SP_Monitor] ON SERVER ADD EVENT sqlserver.rpc_completed( WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[database_name],N'YourDB')) ) ADD TARGET package0.event_file(SET filename=N'SP_Monitor.xel') ``` #### 四、典型场景案例 **场景描述**： 某订单系统存储过程`usp_GetOrderReports`执行时间从2秒骤增至30秒。 **分析过程**： 1. 通过`sys.dm_exec_procedure_stats`发现平均逻辑读从5,000升至150,000 2. 检查执行计划发现索引`IX_Orders_Date`丢失 3. 参数嗅检测试显示传入`@StartDate='2020-01-01'`时使用全表扫描 **解决方案**： ```sql -- 重建索引 ALTER INDEX IX_Orders_Date ON Orders REBUILD -- 添加覆盖索引 CREATE NONCLUSTERED INDEX IX_Orders_Covering ON Orders (OrderDate) INCLUDE (CustomerID, TotalAmount, Status) -- 修改存储过程 ALTER PROCEDURE usp_GetOrderReports @StartDate DATETIME, @EndDate DATETIME AS BEGIN DECLARE @LocalStart DATETIME = @StartDate, @LocalEnd DATETIME = @EndDate SELECT OrderID, CustomerID, TotalAmount FROM Orders WITH (INDEX(IX_Orders_Covering)) WHERE OrderDate BETWEEN @LocalStart AND @LocalEnd OPTION (OPTIMIZE FOR (@LocalStart UNKNOWN, @LocalEnd UNKNOWN)) END ``` #### 五、进阶优化方向 1. **内存优化表** ```sql -- 创建内存优化文件组 ALTER DATABASE YourDB ADD FILEGROUP memopt_mod CONTAINS MEMORY_OPTIMIZED_DATA -- 创建内存优化表 CREATE TABLE dbo.SessionData ( SessionID INT NOT NULL PRIMARY KEY NONCLUSTERED, Data VARBINARY(MAX) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) ``` 2. **原生编译存储过程** ```sql CREATE PROCEDURE usp_NativeProc WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' ) -- 高性能处理逻辑 END ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值