sp_prepare与语句句柄

长久以来一直都在处理别人的产品跟数据库，使用事件探查器来跟踪代码执行情况是我最常用的解析别人产品的方式，不过昨天在搞怡软产品的时候却发现这招不好使，事件探查器中根本看不到一句数据库SQL语句，所有的操作全部都用语句句柄来做了，看到最多的就是 exec sp_execute 11,1，即便在事件探查器中添加参数SP:StmtStaring,SP:StmtCompleted，也没有结果，原因是在处理过程中检测到了Password，探查器给替换了文本。真是万恶，这样就起到了隐藏代码的目的。

   有些地方可以查到使用了sp_prepare来预编译SQL语句，之后在执行语句时只需要调用句柄就可以，而非sp_executesql那样每次调用都必须传递语句和变量。这样处理在多次调用同一语句的情况下，执行速度和代码简洁度都要好一些，不过除此之外暂时没有感觉到有其他好处。

以下是李维的书上的测试：从别的地方看到的，留下来，有空的时候研究一下。
是测试UnPrepared的TADOQuery组件、Prepared的TADOQuery组件以
及TADOStoredProc组件执行新增数据的范例应用程序的执行画面。由于这个范例
应用程序使用了相同的方式新增大量的数据，因此我们可以通过它来测试
UnPrepared的TADOQuery组件、Prepared的TADOQuery组件以及TADOStoredProc
组件相对的执行效率。
执行这个范例应用程序时，也激活了MSSQLProfiler来观察这三个不同的组
件在同样新增数据时执行的行为是什么？下面的数据是UnPrepared的TADOQuery
组件执行新增数据时MS SQL Profiler 观察到的Action Query ：
sp_executesql N'insert into ADOTestDatas
(ID, Name, Phone, Address, Salary, EDate)
values
(@P1, @P2, @P3, @P4, @P5, @P6)
', N'@P1 varchar(10),@P2 varchar(10),@P3 varchar(13),@P4
varchar(50),@P5 float,@P6 datetime', 'K334646237', 'dbuuaiudlo',
'5388648427081',
'icwwoapxxxabukwhbciietruytkpftlgnpqqsclcsprmnhpkxu' ,
99572.479999999996, '20000625 15:27:19:000'
. . .
当应用程序使用UnPrepared的TADOQuery组件新增数据时，对于每一笔新增
的数据，ADO驱动程序都会执行一次上面的SQL命令。从这个观察的结果我们可
以看到，在使用UnPrepared的TADOQuery组件时，MSSQLServer会将每一笔新增
数据的ActionQuery编译成一个暂时的存储过程，再执行它。因此，如果新增了
1000笔数据，那么Action Query 便被编译了1000次。
下面的数据则是当Prepared的TADOQuery组件执行时MS SQL Profiler 观察到
的结果。可以看到，当使用Prepared的TADO Query组件时，MSSQL Server 只会先
编译Action Query 一次成为一个暂时的存储过程：
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 varchar(10),@P2 varchar(10),@P3
varchar(13),@P4 varchar(50),@P5 float,@P6 datetime', N'insert into
ADOTestDatas
(ID, Name, Phone, Address, Salary, EDate)
values
(@P1, @P2, @P3, @P4, @P5, @P6)
', 1
select @P1
而当Prepared的TADOQuery组件新增每一笔数据时，MSSQL Server 只是执行
刚才编译的暂时存储过程，并且只把参数传递给这个暂时的存储过程：
sp_execute 1, 'Z252651567', 'yexoxpwyck', '6683134408672',
'ozerprmgkijeypizrftswouqbmekuxlasrgubzmciwqtuqmjvx' ,
13449.690000000001, '20000625 15:27:36:000'
至于范例应用程序使用TADOStoredProc组件执行新增数据时，它的Action
Query和使用Prepared的TADOQuery组件几乎是一样的。只是TADOStoredProc组件
连第一次编译暂时存储过程的步骤也不用，因为TADOStoredProc组件已经连接了
一个已经存在的存储过程，因此只需要传递参数给这个已经存在的存储过程执行
新增数据的工作即可。
从上面的讨论之中你可以了解，对于应用程序中经常执行的SQL命令而言，使
用Prepared的TADOQuery会比使用UnPrepared的TADOQuery来得快速，因为
Prepared的TADOQuery可以省去每次都需要数据源编译SQL命令的步骤。而Prepared
的TADOQuery和TADOStoredProc的执行效率应该差不多，TADOStoredProc会比
Prepared的TADOQuery来得快一点。
下面的表格便是这三种组件在执行新增数据时的结果：
新增数据(1000笔)新增数据(5000笔)
UnPrepared4.46628
Prepare3.58526.117
Stored Procedure 3.82528.561
上面的执行结果也证明了UnPrepared的TADOquery的执行效率比Prepared的
TADOQuery和TADOStoredProc低一些。
下面的表格则是使用相同的组件来随机地选取大量数据的结果：
随机选取数据
UnPrepared 11.514
Prepare 11.496
Stored Procedure10.866
从这个结果中我们可以看到，UnPrepared的TADOQuery仍然无法和Prepared
的TADOQuery以及TADOStroredProc相比。而在处理大量数据的情形下，直接使
用存储过程的执行效率的优势也就更明显了。
许多人喜欢使用TADOStoredProc组件来增加应用程序的执行效率，因为
TADOStoredProc组件会调用数据库的存储过程来执行工作。在处理大量数据的情
形下，TADOStoredProc可以增加应用程序的执行效率，但它的缺点就是应用程序
会和特定的数据库绑在一起。我个人比较喜欢使用PreparedTADODataSet或
TADOQuery来执行应用程序，因为我经常会使用不同的数据库，因此我并不想和
任何特定的数据库绑在一起。你可以根据你的需要来选择适当的ADOExpress组件
来使用。

test:

declare @P1  int
exec sp_prepare @P1 output, N'@orderid bigint',
 N'SELECT top 100 * from liebo.orderflow.[order] with(nolock)
 where orderid=@orderid
  ', 1
select @P1
exec sp_execute @P1, '110826001488'
exec sp_execute @P1, '110826002278'
exec sp_execute @P1, '110826004999'
exec sp_execute @P1, '110826006893'

 

 

 

 

 

 

 

 

 

 

 

 

===================================

 

[分享]sp_prepare你用过吗?
最近研究ADO的数据构形技术的时候，跟踪SQL Server发现有如下的SQL代码被执行

SET NO_BROWSETABLE OFF — 这个未公开的设置,不知道是什么作用
SET FMTONLY ON select bill_id from tbl_wh_bill_detail SET FMTONLY OFF
SET NO_BROWSETABLE ON

declare @P1 int
set @P1=22
– 我要说的就是这个 sp_prepare ，用来预编译SQL语句
exec sp_prepare @P1 output, N’@P1 uniqueidentifier’, N’SELECT * FROM tbl_wh_bill_detail where bill_id = @P1′, 1
select @P1

SET NO_BROWSETABLE OFF
SELECT * FROM tbl_wh_bill_head where warehouse = ‘2002′
SET NO_BROWSETABLE ON

– 这是我第一次见到 sp_execute 的这种用法，用她来执行预编译SQL语句，比反复调用sp_executesql效率高多了
exec sp_execute 22, ‘9E44402B-12CF-4AB4-B1A7-00AB21608CDB’
exec sp_execute 22, ‘44811ABA-5B1E-4CE2-8E67-0744FCEDB072′
exec sp_execute 22, ‘BFBAF066-4A28-42E0-9A90-08912DF18E13′
exec sp_execute 22, ‘16572595-B555-4D8E-96DF-0A48350820A6′
exec sp_execute 22, ‘FADD71DA-9381-4207-8266-0D0F0FFECCC0′

/*
–如果用sp_executesql，每一次调用都必须重新编译, 形如：
exec sp_executesql N’SELECT * FROM tbl_wh_bill_detail where bill_id = @P1′
,N’@P1 uniqueidentifier’
,’9E44402B-12CF-4AB4-B1A7-00AB21608CDB’

*/

以下是ADO的数据构形技术相关的VBA代码：

Private Sub Command25_Click()
Dim rst As New ADODB.Recordset
Dim rs As New ADODB.Recordset

‘这种用法是非参数话的构形，效率比以下这种方式低
‘rst.Open "SHAPE {SELECT * FROM tbl_wh_bill_head where warehouse = ‘2002′} " _
‘ & " APPEND ({SELECT * FROM tbl_wh_bill_detail} AS BillDetail " _
‘ & " RELATE bill_id TO PARAMETER 0)" _
‘ , CurrentProject.Connection

‘这里用参数话的构形，
rst.Open "SHAPE {SELECT * FROM tbl_wh_bill_head where warehouse = ‘2002′} " _
& " APPEND ({SELECT * FROM tbl_wh_bill_detail where bill_id = ?} AS BillDetail " _
& " RELATE bill_id TO PARAMETER 0)" _
, CurrentProject.Connection

Set rs = rst("billdetail").Value

Do Until rst.EOF
Debug.Print rs("bill_id"), rst("bill_code")

Do Until rs.EOF
Debug.Print rs("bill_id"), rs("item_name")
rs.MoveNext
Loop

rst.MoveNext ‘rst 移动记录指针的时候， rs是只是相应的记录， 如果是用参数话的构形，每次只从服务器返回相应的记录，而不是一次性返回所有
Loop

rs.Close
rst.Close
Set rst = Nothing
Set rs = Nothing

End Sub

为了说明问题，再转贴一篇

来自：t1122, 时间：2002-1-21 22:13:00, ID：875327

以下是李维的书上的测试：
是测试UnPrepared的TADOQuery组件、Prepared的TADOQuery组件以
及TADOStoredProc组件执行新增数据的范例应用程序的执行画面。由于这个范例
应用程序使用了相同的方式新增大量的数据，因此我们可以通过它来测试
UnPrepared的TADOQuery组件、Prepared的TADOQuery组件以及TADOStoredProc
组件相对的执行效率。
执行这个范例应用程序时，也激活了MSSQLProfiler来观察这三个不同的组
件在同样新增数据时执行的行为是什么？下面的数据是UnPrepared的TADOQuery
组件执行新增数据时MS SQL Profiler 观察到的Action Query ：
sp_executesql N’insert into ADOTestDatas
(ID, Name, Phone, Address, Salary, EDate)
values
(@P1, @P2, @P3, @P4, @P5, @P6)
‘, N’@P1 varchar(10),@P2 varchar(10),@P3 varchar(13),@P4
varchar(50),@P5 float,@P6 datetime’, ‘K334646237′, ‘dbuuaiudlo’,
‘5388648427081′,
‘icwwoapxxxabukwhbciietruytkpftlgnpqqsclcsprmnhpkxu’ ,
99572.479999999996, ‘20000625 15:27:19:000′
. . .
当应用程序使用UnPrepared的TADOQuery组件新增数据时，对于每一笔新增
的数据，ADO驱动程序都会执行一次上面的SQL命令。从这个观察的结果我们可
以看到，在使用UnPrepared的TADOQuery组件时，MSSQLServer会将每一笔新增
数据的ActionQuery编译成一个暂时的存储过程，再执行它。因此，如果新增了
1000笔数据，那么Action Query 便被编译了1000次。
下面的数据则是当Prepared的TADOQuery组件执行时MS SQL Profiler 观察到
的结果。可以看到，当使用Prepared的TADO Query组件时，MSSQL Server 只会先
编译Action Query 一次成为一个暂时的存储过程：
declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N’@P1 varchar(10),@P2 varchar(10),@P3
varchar(13),@P4 varchar(50),@P5 float,@P6 datetime’, N’insert into
ADOTestDatas
(ID, Name, Phone, Address, Salary, EDate)
values
(@P1, @P2, @P3, @P4, @P5, @P6)
‘, 1
select @P1
而当Prepared的TADOQuery组件新增每一笔数据时，MSSQL Server 只是执行
刚才编译的暂时存储过程，并且只把参数传递给这个暂时的存储过程：
sp_execute 1, ‘Z252651567′, ‘yexoxpwyck’, ‘6683134408672′,
‘ozerprmgkijeypizrftswouqbmekuxlasrgubzmciwqtuqmjvx’ ,
13449.690000000001, ‘20000625 15:27:36:000′
至于范例应用程序使用TADOStoredProc组件执行新增数据时，它的Action
Query和使用Prepared的TADOQuery组件几乎是一样的。只是TADOStoredProc组件
连第一次编译暂时存储过程的步骤也不用，因为TADOStoredProc组件已经连接了
一个已经存在的存储过程，因此只需要传递参数给这个已经存在的存储过程执行
新增数据的工作即可。
从上面的讨论之中你可以了解，对于应用程序中经常执行的SQL命令而言，使
用Prepared的TADOQuery会比使用UnPrepared的TADOQuery来得快速，因为
Prepared的TADOQuery可以省去每次都需要数据源编译SQL命令的步骤。而Prepared
的TADOQuery和TADOStoredProc的执行效率应该差不多，TADOStoredProc会比
Prepared的TADOQuery来得快一点。
下面的表格便是这三种组件在执行新增数据时的结果：
新增数据(1000笔)新增数据(5000笔)
UnPrepared4.46628
Prepare3.58526.117
Stored Procedure 3.82528.561
上面的执行结果也证明了UnPrepared的TADOquery的执行效率比Prepared的
TADOQuery和TADOStoredProc低一些。
下面的表格则是使用相同的组件来随机地选取大量数据的结果：
随机选取数据
UnPrepared 11.514
Prepare 11.496
Stored Procedure10.866
从这个结果中我们可以看到，UnPrepared的TADOQuery仍然无法和Prepared
的TADOQuery以及TADOStroredProc相比。而在处理大量数据的情形下，直接使
用存储过程的执行效率的优势也就更明显了。
许多人喜欢使用TADOStoredProc组件来增加应用程序的执行效率，因为
TADOStoredProc组件会调用数据库的存储过程来执行工作。在处理大量数据的情
形下，TADOStoredProc可以增加应用程序的执行效率，但它的缺点就是应用程序
会和特定的数据库绑在一起。我个人比较喜欢使用PreparedTADODataSet或
TADOQuery来执行应用程序，因为我经常会使用不同的数据库，因此我并不想和
任何特定的数据库绑在一起。你可以根据你的需要来选择适当的ADOExpress组件
来使用。