Before I open a ticket with Microsoft Support, I thought I'd try the community!
I have an application in development for which we are using Change Data Capture in SQL 2008 R2 (developer edition, currently). For some particularly complex queries, we wanted to wrap the queries into stored procedures, exposing common parameters, to avoid complexity in the client (the usual argument)...
In any case, what we've identified is that the following statement, as a standalone query, will run in about 3-5 seconds, regardless of the boundary conditions, whereas the exact same statement, as a stored procedure, jumps to 1.5 minutes to produce the same results. In addition, the SP version while running seems to switch user identities several times over the course of execution... Also, during execution of the SP, the CPU usage spikes.
Any thoughts?
The Query:
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
)
)
)
And the associated Stored Procedure:
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
)
)
)
Script to execute the 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
As indicated in the text above, as a Query, it takes abuot 3-5 seconds (in Management Studio). As a Stored Proceure, 1.5 minutes. As a query via the .Net framework providor (System.Data.SqlClient), 1.5 minutes. As a query via the OleDb SQLNCLI10 provider, 3-5 seconds. As an SP via either Framework or OleDb, 1.5 minutes.
Any thoughts?