[分享]sp_prepare你用過嗎?

goodidea

最近研究ADO的數(shù)據(jù)構形技術的時候，跟蹤SQL Server發(fā)現(xiàn)有如下的SQL代碼被執(zhí)行

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 的這種用法，用她來執(zhí)行預編譯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的數(shù)據(jù)構形技術相關的VBA代碼：

Private Sub Command25_Click()

    Dim rst As New ADODB.Recordset

    Dim rs As New ADODB.Recordset

    '這種用法是非參數(shù)話的構形，效率比以下這種方式低

    '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



    '這里用參數(shù)話的構形，

    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是只是相應的記錄， 如果是用參數(shù)話的構形，每次只從服務器返回相應的記錄，而不是一次性返回所有

    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組件執(zhí)行新增數(shù)據(jù)的范例應用程序的執(zhí)行畫面。由于這個范例

應用程序使用了相同的方式新增大量的數(shù)據(jù)，因此我們可以通過它來測試

UnPrepared的TADOQuery組件、Prepared的TADOQuery組件以及TADOStoredProc

組件相對的執(zhí)行效率。

執(zhí)行這個范例應用程序時，也激活了MSSQLProfiler來觀察這三個不同的組

件在同樣新增數(shù)據(jù)時執(zhí)行的行為是什么？下面的數(shù)據(jù)是UnPrepared的TADOQuery

組件執(zhí)行新增數(shù)據(jù)時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組件新增數(shù)據(jù)時，對于每一筆新增

的數(shù)據(jù)，ADO驅動程序都會執(zhí)行一次上面的SQL命令。從這個觀察的結果我們可

以看到，在使用UnPrepared的TADOQuery組件時，MSSQLServer會將每一筆新增

數(shù)據(jù)的ActionQuery編譯成一個暫時的存儲過程，再執(zhí)行它。因此，如果新增了

1000筆數(shù)據(jù)，那么Action Query 便被編譯了1000次。

下面的數(shù)據(jù)則是當Prepared的TADOQuery組件執(zhí)行時MS SQL Profiler 觀察到

的結果。可以看到，當使用Prepared的TADO Query組件時，MSSQL Server 只會先

編譯Action Query 一次成為一個暫時的存儲過程：

decla

zhuyiwen

好心得！