[分享]sp_prepare你用过吗?

goodidea

最近研究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, [url=mailto:N'@P1]N'@P1[/url] 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)

', [url=mailto:N'@P1]N'@P1[/url] 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 一次成为一个暂时的存储过程：

decla

zhuyiwen

好心得！