鲨鱼网
游标脚本性能问题详解之游标分类特点篇
创始人
2024-07-25 05:11:54
0次

从上篇游标脚本性能问题详解之案例实践篇两个脚本执行情况的对比中可以看出，游标的选择对语句执行的性能具有一定的影响。

在SQL Server联机丛书上列出了不止十种游标类型，但是所有游标都可以被划到两大类别：

1. 通过从***得到结果的临时拷贝映像静态进行

2. 每次fetch都通过动态进行且真正查阅表

STATIC、KEYSET、READ_ONLY和FAST_FORWARD属于***大类，FORWARD_ONLY、DYNAMIC和OPTIMISTIC属于第二大类。

下面我们来进行一定的比较分析，并学习如何使用各种游标。在进行这部分之前，我们要引入另一个set statistics的方法： set statistics profile on

这个option会帮助我们打印出文本格式的执行计划和每一布的执行统计信息。这个部分的执行语句执行计划都是通过这个option打印的。

1. 首先，我们把游标脚本中的SQL语句抽取出来直接运行而不使用游标：

  1. SELECT       T1.*  
  2. FROM         dbo.S_AUDIT_ITEM T1              
  3. LEFT OUTER JOIN dbo.S_USER T2   
  4. ON T1.USER_ID = T2.PAR_ROW_ID      
  5. WHERE   T1.BC_BASE_TBL = 'S_PARTY' AND   T1.RECORD_ID = '1-10350J' 
  6. ORDER BY       T1.OPERATION_DT DESC  

执行情况如下：逻辑读15次，使用的是索引查找(index seek)

  1. Table 'S_USER'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  
  2. Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

执行计划为：

  1. Rows                 Executes             StmtText                                                                                                   
  2. -------------------- -------------------- ---------------------------------------------------------------------------------------------------------  
  3. 4                    1                    SELECT  T1.*  FROM  dbo.S_AUDIT_ITEM T1                                                                                                                     
  4. LEFT OUTER JOIN dbo.S_USER T2                                                                                                                        
  5. ON T1.USER_ID = T2.PAR_ROW_ID                                                                                                                        
  6. WHERE   T1.BC_BASE_TBL = 'S_PARTY' AND   T1.RECORD_ID = '1-10350J'                                                                                 ORDER BY  T1.OPERATION_DT DESC            1    1    0      NULL         NULL                     
  7. 4                    1                      |--Sort(ORDER BY:([T1].[OPERATION_DT] DESC))                                                             
  8. 4                    1                           |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_  
  9. 4                    1                                |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [T1].[ROW_ID]) OPTIMIZED)            
  10. 4                    1                                |  |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]), SEEK  
  11. 4                    4                                |  |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [  
  12. 66908                4                                |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))     

2. 下面通过T-SQL语句打开一个游标。注意，这里创建的游标为dynamic类型，因为新声明的游标默认类型为dynamic。。本文开头使用的存储过程是调用API游标的写法，这里是用T-SQL语句打开游标，两种写法使用的游标类型和执行的语句是完全一样的。

  1. declare @CONFLICT_ID int 
  2. declare curTest cursor 
  3.  
  4. FOR 
  5.     SELECT   
  6.        T1.CONFLICT_ID  
  7.     FROM         dbo.S_AUDIT_ITEM T1              
  8.     LEFT OUTER JOIN dbo.S_USER T2  
  9.     ON T1.USER_ID = T2.PAR_ROW_ID      
  10.     WHERE      T1.BC_BASE_TBL = 'S_PARTY' AND   T1.RECORD_ID ='1-10350J'     
  11.     ORDER BY       T1.OPERATION_DT    
  12.  
  13. OPEN curTest  
  14. FETCH NEXT FROM curTest   
  15. INTO @CONFLICT_ID  
  16. CLOSE curTest  
  17.  
  18. deallocate curTest 

执行情况为：逻辑读明显增多，使用索引扫描(index scan)

  1. Table 'Worktable'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                 
  2. Table 'S_USER'. Scan count 1, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                   
  3. Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 3026834, physical reads 1292, read-ahead reads 5574, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  

执行计划如下：

  1. Rows                 Executes             StmtText                                                                                                                          
  2. -------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------  
  3. 1                    1                    FETCH NEXT FROM curTest                                                                                                           
  4. INTO @CONFLICT_ID                                                                                                                                                           
  5. 1                    1                      |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as   
  6. 1                    1                           |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))                                                                         
  7. 1                1                                |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as   
  8. 1                    1                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))                                        
  9. 1007751              1                                     |    |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS     
  10. 1                    1007751                               |    |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS   
  11. 16401                1                                     |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))  

接下来，我们使用其他类型的游标进行测试，从它们的测试结果会发现：

当使用STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型的游标，可以得到理想的执行计划(索引S_AUDIT_ITEM_M3上使用索引查找)。

但是，如果使用其他第二类游标类型，得到的执行计划就不甚理想了(索引S_AUDIT_ITEM_M4上使用索引扫描)。

从上面的测试，我们知道STATIC、KEYSET、READ_ONLY及FAST_FORWARD游标可以带给我们同样的理想结果。那么，这些游标有什么共同点?

我们可以分析一下两大游标类型执行计划的不同：

1. STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型游标的执行计划：

  1. Executes             StmtText                                                                                                              
  2. -------------------- --------------------------------------------------------------------------------------------------------------------  
  3. 1                    OPEN curTest                                                                                                          
  4. 1                      |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[CONFLICT_ID] as   
  5. 1                           |--Sequence Project(DEFINE:([Expr1008]=i4_row_number))                                                         
  6. 1                                |--Segment                                                                                                
  7. 1                                     |--Sort(ORDER BY:([T1].[OPERATION_DT] ASC))                                                          
  8. 1                                          |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[U  
  9. 1                                               |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]) OPTIMIZED)        
  10. 1                                               |    |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]),   
  11. 4                                               |    |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1]   
  12. 4                                               |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))                                
  13.                                                                                                       
  14. Executes             StmtText                                                                                        StmtId      NodeId    
  15. -------------------- ----------------------------------------------------------------------------------------------- ----------- --------  
  16. 1                    FETCH NEXT FROM curTest INTO @CONFLICT_ID                       2           1           
  17. 1                      |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD)  2           2         

2. dynamic类型游标的执行计划

  1. Executes   StmtText                                                                                                                      
  2. ---------------------------------------------------------------------------------------------------------------------------------------  
  3. 1          FETCH NEXT FROM curTest                                                                                                       
  4.                                                                                                                                          
  5. 1            |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as [T1].[ROW_ID]  
  6. 1                 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))                                                                     
  7. 1                      |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_ID]=[testcurso  
  8. 1                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))                                    
  9. 1                           |    |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS [T1]), ORDERED BACKWARD)   
  10. 1007751                     |    |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [T1]), SEEK:([T  
  11. 1                           |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))  

比较一下两个执行计划的FETCH NEXT部分(SQL Server在游标打开阶段不会读取表)：在***个执行计划中，FETCH是直接从临时对象CWT中得到行，然后从CWT.ROWID中找到相应范围。而在第二个计划中，FETCH是动态的而且是真正对表进行了读取，从表中取得数据。
 

上一篇：mysql5存储过程编写实践

下一篇：使用Nagios监视企业集群

相关内容

热门资讯

如何允许远程连接到MySQL数... [[277004]]【51CTO.com快译】默认情况下，MySQL服务器仅侦听来自localhos...
如何利用交换机和端口设置来管理... 在网络管理中，总是有些人让管理员头疼。下面我们就将介绍一下一个网管员利用交换机以及端口设置等来进行D...
施耐德电气数据中心整体解决方案... 近日，全球能效管理专家施耐德电气正式启动大型体验活动“能效中国行——2012卡车巡展”，作为该活动的...
20个非常棒的扁平设计免费资源 Apple设备的平面图标PSD免费平板UI 平板UI套件24平图标Freen平板UI套件PSD径向平...
2009 IBM动态架构新动力...
通过 XML 进行内容发布
Windows恶意软件20年“... 在Windows的早期年代，病毒游走于系统之间，偶尔删除文件（但被删除的文件几乎都是可恢复的），并弹...
德国电信门户网站可实时显示全球... 德国电信周三推出一个门户网站，直观地实时提供其安装在全球各地的传感器网络检测到的网络攻击状况。该网站...
为啥国人偏爱 Mybatis，... 关于 SQL 和 ORM 的争论，永远都不会终止，我也一直在思考这个问题。昨天又跟群里的小伙伴进行...
《非诚勿扰》红人闫凤娇被曝厕所... 【51CTO.com 综合消息360安全专家提醒说，“闫凤娇”、“非诚勿扰”已经被黑客盯上成为了“木...