SQL 2008 更改数据捕获查询性能问题（SP 与直接查询）

Question

在向 Microsoft 支持开具票证之前，我想尝试一下社区！

我有一个正在开发的应用程序，我们正在 SQL 2008 R2（当前开发人员版本）中使用更改数据捕获。对于一些特别复杂的查询，我们希望将查询包装到存储过程中，公开公共参数，以避免客户端的复杂性（通常的参数）......

无论如何，我们确定的是以下语句，如无论边界条件如何，独立查询都会在大约 3-5 秒内运行，而完全相同的语句作为存储过程，会跳转到 1.5 分钟才能产生相同的结果。此外，SP版本在运行时似乎会在执行过程中多次切换用户身份...此外，在SP执行期间，CPU使用率会出现峰值。

有什么想法吗？

查询：

DECLARE @fromlsn BINARY(10),
    @tolsn   BINARY(10),
    @NodeID  varchar(6)
SET     @NodeID = '123456',
        @fromlsn = 0x000017E6000001AC0041,
        @tolsn = sys.fn_cdc_get_max_lsn()

DECLARE @min_lsn_TransactionDate BINARY(10),
        @min_TransactionDate smalldatetime

SELECT  @min_TransactionDate = MIN(TransactionDate)
FROM    cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with merge') 
WHERE   _NODEID_=@NodeId and __$operation<>1

SELECT  @min_lsn_TransactionDate = MIN(__$start_lsn)
FROM    cdc.dbo_tblOrders_CT with (nolock)
WHERE   _NODEID_=@NodeId
    AND TransactionDate=@min_TransactionDate

SELECT   Table1.TransactionDate
    ,Table1.OrderNumber
    ,Table1.SequenceNum 
    ,Table1.ItemNumber
    ,Table1.Quantity
    ,Table1.Price
    ,Table1.ExtPrice
FROM          cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with mask') Table1
WHERE   Table1._NodeID_=@NodeId
    AND (   Table1.__$operation=2
         OR (   Table1.__$operation=4  
            AND  ( sys.fn_cdc_is_bit_set(9,Table1.__$update_mask)=1 
                   OR sys.fn_cdc_is_bit_set(10,Table1.__$update_mask)=1 
                 )
             )
        )

以及关联的存储过程：

CREATE PROCEDURE testtesttest 
        @fromlsn BINARY(10),
        @tolsn   BINARY(10),
        @NodeID  varchar(10)
as 
DECLARE @min_lsn_TransactionDate BINARY(10),
        @min_TransactionDate smalldatetime

SELECT  @min_TransactionDate = MIN(TransactionDate)
FROM    cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with merge') 
WHERE   _NODEID_=@NodeId and __$operation<>1

SELECT  @min_lsn_TransactionDate = MIN(__$start_lsn)
FROM    cdc.dbo_tblOrders_CT with (nolock)
WHERE   _NODEID_=@NodeId
    AND TransactionDate=@min_TransactionDate

SELECT   Table1.TransactionDate
    ,Table1.OrderNumber
    ,Table1.SequenceNum 
    ,Table1.ItemNumber
    ,Table1.Quantity
    ,Table1.Price
    ,Table1.ExtPrice
FROM          cdc.fn_cdc_get_net_changes_dbo_tblOrders(sys.fn_cdc_increment_lsn(@fromlsn),@tolsn,'all with mask') Table1
WHERE   Table1._NodeID_=@NodeId
    AND (   Table1.__$operation=2
         OR (   Table1.__$operation=4  
            AND  ( sys.fn_cdc_is_bit_set(9,Table1.__$update_mask)=1 
                   OR sys.fn_cdc_is_bit_set(10,Table1.__$update_mask)=1 
                 )
             )
        )

执行 SP 的脚本：

DECLARE @fromlsn BINARY(10),
    @tolsn   BINARY(10),
    @NodeID  varchar(6)
SET     @NodeID = '123456',
        @fromlsn = 0x000017E6000001AC0041,
        @tolsn = sys.fn_cdc_get_max_lsn()

exec testtesttest @fromlsn,@tolsn,@NodeID

如上面的文本所示，作为查询，大约需要 3-5 秒（在 Management Studio 中）。作为存储过程，1.5 分钟。通过 .Net 框架提供程序 (System.Data.SqlClient) 进行查询，1.5 分钟。作为通过 OleDb SQLNCLI10 提供程序的查询，需要 3-5 秒。作为通过 Framework 或 OleDb 的 SP，1.5 分钟。

有什么想法吗？

Answer 1

我的钱将花在缓存中的错误查询计划上。尝试刷新过程缓存（不在实时系统上！）或使用 SP 中的选项（重新编译）看看是否有帮助