SQLCLR运行速度太慢

Question

我在一个表中有160万行。有100,000行缺少一些信息。在不重复引用的情况下定位该信息。我已经编写了一个SQL过程。它的执行速度非常慢。在30分钟的时间内，100,000上只处理了5000行。

可以用内联SQL替换下面的代码。

  var paymentSql =String.Format("select PaymodeId,StdLedgerId,BaseAmount,RegNo/*,REfInstno,RefStdLedgerId,RefPaymodeId*/ from vw_Payment_Ledger_Matching_Other {0} {1}" ,(condition.Equals("") ? "" : " where " + condition) ," order by CenterId,Ledgerdate,RecptKey ");

                var payment = new SqlCommand(paymentSql, conn1) { CommandTimeout = 600 };

                using (SqlDataReader payments = payment.ExecuteReader())
                {
                    while (payments.Read())
                    {
                        var paymentPaymodeId = payments["PaymodeId"];
                        var paymentStdLedgerId = payments["StdLedgerId"];
                        var paymentAmount = payments["BaseAmount"];
                        var paymentRegNo = payments["RegNo"];
                        //var paymentRefInstNo = payments["RefInstNo"];
                        //var paymentRefStdLedgerId = payments["RefStdLedgerId"];
                        //var paymentRefPayModeId = payments["RefPayModeId"];

                        //if (Convert.ToInt32(paymentRefInstNo) == 0 && Convert.ToInt32(paymentRefStdLedgerId) == 0 && paymentRefPayModeId.Equals("0"))
                        {
                            var ledgerSql = String.Format("select paymodeId,StdLedgerId,Instno,Concession,LumpSump,ConcessionDtl,LumpSumpDtl from vw_Payment_Ledger_Matching_inst a where a.regno='{0}' and abs(a.BaseAmount) between  abs({1})-5 and abs({1})+5 and Isnull(a.refInstno,0)=0 and a.insttype<>'O'" +
                                                   "and (cast(a.StdLedgerID as varchar(10))+cast(InstNo as varchar(1))) not in ( select cast(b.refStdLedgerID as varchar(10))+cast(b.refInstNo as varchar(1)) from vw_Payment_Ledger_Matching_inst b"
                                                   +" where b.regno='{0}'  and (b.BaseAmount) between  ({1})-5 and ({1})+5 and b.Insttype='O' )"
                                                   +" order by a.CenterId,a.RecptKey,a.LedgerDate ",paymentRegNo,paymentAmount );
                            var Ledger = new SqlCommand(ledgerSql, conn2) { CommandTimeout = 600 };
                            SqlDataReader ledger = Ledger.ExecuteReader();
                            if (ledger.Read())
                            {
                                var ledgerPayModeId = ledger["PayModeID"];                                    
                                var ledgerStdLedgerId = ledger["StdLedgerId"];
                                var ledgerInstNo = ledger["InstNo"];
                                var ledgerConcession = ledger["Concession"];
                                var ledgerLumpsump = ledger["Lumpsump"];
                                var ledgerConcessionDtl = ledger["ConcessionDtl"];
                                var ledgerLumpsumpDtl = ledger["LumpsumpDtl"];

                                var updatesql = "update " + updateTable + " set RefInstno=" + ledgerInstNo
                                                + ", RefStdLedgerId=" + ledgerStdLedgerId + ""
                                                + ", RefPayModeId='" + ledgerPayModeId + "'"
                                                + ", RefConcession=" + ledgerConcession
                                                + ", RefLumpsump=" + ledgerLumpsump
                                                + ", RefConcessionDtl=" + ledgerConcessionDtl
                                                + ", RefLumpsumpDtl=" + ledgerLumpsumpDtl
                                                + " where stdLedgerId=" + paymentStdLedgerId
                                                + " and PayModeId='" + paymentPaymodeId + "'";
                                var ledgerUpdate = new SqlCommand(updatesql, conn3);
                                ledgerUpdate.ExecuteNonQuery();                                    
                            }
                        }
                    }
                }

Answer 1

这里的问题不是SQLCLR。问题是SQLCLR在完全没有理由的情况下被使用。除非我遗漏了什么，否则这个操作只是一个简单的游标，对从SELECT FROM vw_Payment_Ledger_Matching_Other查询返回的每一行执行SELECT FROM vw_Payment_Ledger_Matching_inst和UPDATE {updateTable}。即使这3个SqlConnection正在使用Context Connect = true;，它仍然在执行一个非参数化查询(正如@usr在关于这个问题的注释中指出的那样)，并为这100,000行中的每一行创建一个没有关闭的新SqlDataReader (即ledger)。但是，这里没有理由使用SQLCLR。

让我们来看看这个操作要做什么。它是在说：

For each record in Query A
{
  Get a row from Query B
  Update a table via Query C, using the row from Query B
}

您在C#中拥有的在语义/操作上等同于以下T-SQL：

CREATE PROCEDURE DoStuffBetter
(
   @Condition NVARCHAR(500),
   @UpdateTable NVARCHAR(500)
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'

DECLARE @PaymentPayModeId NVARCHAR(50),
        @PaymentStdLedgerId INT,
        @PaymentAmount MONEY,
        @PaymentRegNo NVARCHAR(50);


DECLARE payment CURSOR FOR
     SELECT PayModeId, StdLedgerId, BaseAmount, RegNo
                              /*, RefInstNo, RefStdLedgerId, RefPayModeId*/
     FROM   vw_Payment_Ledger_Matching_Other
' + CASE WHEN @Condition <> '' THEN N'     WHERE ' + @Condition ELSE '' END + N'
     ORDER BY CenterId, LedgerDate, RecptKey;

OPEN payment;

FETCH NEXT
FROM  payment
INTO  @PaymentPayModeId, @PaymentStdLedgerId, @PaymentAmount, @PaymentRegNo;

WHILE (@@FETCH_STATUS = 0)
BEGIN
     DECLARE @LedgerPayModeId NVARCHAR(50),
             @LedgerStdLedgerId INT,
             @LedgerInstNo INT,
             @LedgerConcession MONEY,
             @LedgerLumpSump MONEY,
             @LedgerConcessionDtl MONEY,
             @LedgerLumpsumpDtl MONEY;

     SELECT TOP 1
            @LedgerPayModeId = PayModeId,
            @LedgerStdLedgerId = StdLedgerId,
            @LedgerInstNo = InstNo,
            @LedgerConcession = Concession,
            @LedgerLumpSump = LumpSump,
            @LedgerConcessionDtl = ConcessionDtl,
            @LedgerLumpsumpDtl = LumpSumpDtl
     FROM   vw_Payment_Ledger_Matching_inst a
     WHERE  a.RegNo = @PaymentRegNo
     AND    ABS(a.BaseAmount) BETWEEN ABS(@PaymentAmount) - 5
                                  AND ABS(@PaymentAmount) + 5
     AND    ISNULL(a.RefInstNo, 0) = 0
     AND    a.InstType <> ''O''
     AND    (CAST(a.StdLedgerID AS VARCHAR(10)) + CAST(a.InstNo AS VARCHAR(1)))
            NOT IN (
                    SELECT CAST(b.RefStdLedgerID AS VARCHAR(10)) +
                           CAST(b.RefInstNo AS VARCHAR(1))
                    FROM   vw_Payment_Ledger_Matching_inst b
                    WHERE  b.RegNo = @PaymentRegNo
                    AND    (b.BaseAmount) BETWEEN (@PaymentAmount) - 5
                                              AND (@PaymentAmount) + 5
                    AND    b.InstType = ''O''
                   )
     ORDER BY a.CenterId, a.RecptKey, a.LedgerDate;

     IF (@@ROWCOUNT > 0)
     BEGIN
          UPDATE ' + @UpdateTable + N'
          SET    RefInstNo = @LedgerInstNo,
                 RefStdLedgerId = @LedgerStdLedgerId,
                 RefPayModeId = @LedgerPayModeId,
                 RefConcession = @LedgerConcession,
                 RefLumpsump = @LedgerLumpSump,
                 RefConcessionDtl = @LedgerConcessionDtl,
                 RefLumpsumpDtl = @LedgerLumpsumpDtl
          WHERE  StdLedgerId = @PaymentStdLedgerId
          AND    PayModeId = @PaymentPayModeId;
     END;

     FETCH NEXT
     FROM  payment
     INTO  @PaymentPayModeId, @PaymentStdLedgerId, @PaymentAmount, @PaymentRegNo;
END;

CLOSE payment;
DEALLOCATE payment;
';

EXEC (@SQL);

上面的方法应该比C#版本的效率高得多，但是仍然可以改进它来删除光标。以下基于集合的方法在逻辑上应该是等价的，但都在一个查询中完成：

CREATE PROCEDURE DoStuffBest
(
   @Condition NVARCHAR(500),
   @UpdateTable NVARCHAR(500)
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'

;WITH Payment AS
(
     SELECT PayModeId, StdLedgerId, BaseAmount, RegNo
                      /*, RefInstNo, RefStdLedgerId, RefPayModeId*/
     FROM   vw_Payment_Ledger_Matching_Other
' + CASE WHEN @Condition <> '' THEN N'     WHERE ' + @Condition ELSE '' END + N'
     ORDER BY CenterId, LedgerDate, RecptKey
), Ledger AS
(
     SELECT 
            a.PayModeId,
            a.StdLedgerId,
            a.InstNo,
            a.Concession,
            a.LumpSump,
            a.ConcessionDtl,
            a.LumpSumpDtl,
            Payment.PayModeId AS [PaymentPayModeId], -- passthrough for UPDATE
            Payment.StdLedgerId AS [PaymentStdLedgerId], -- passthrough for UPDATE
            ROW_NUMBER() OVER (PARTITION BY a.RegNo
                 ORDER BY a.CenterId, a.RecptKey, a.LedgerDate) AS [RowNumInGroup]
     FROM   vw_Payment_Ledger_Matching_inst a
     INNER JOIN Payment
             ON Payment.RegNo = a.RegNo
     WHERE  ABS(a.BaseAmount) BETWEEN ABS(Payment.BaseAmount) - 5
                                  AND ABS(Payment.BaseAmount) + 5
     AND    ISNULL(a.RefInstNo, 0) = 0
     AND    a.InstType <> ''O''
     AND    (CAST(a.StdLedgerID AS VARCHAR(10)) + CAST(a.InstNo AS VARCHAR(1)))
            NOT IN (
                    SELECT CAST(b.RefStdLedgerID AS VARCHAR(10)) +
                              CAST(b.RefInstNo AS VARCHAR(1))
                    FROM   vw_Payment_Ledger_Matching_inst b
                    WHERE  b.RegNo = Payment.RegNo
                    AND    (b.BaseAmount) BETWEEN (Payment.BaseAmount) - 5
                                              AND (Payment.BaseAmount) + 5
                    AND    b.InstType = ''O''
                   )
     ORDER BY a.CenterId, a.RecptKey, a.LedgerDate
)
UPDATE upd
SET    upd.RefInstNo = Ledger.InstNo,
       upd.RefStdLedgerId = Ledger.StdLedgerId,
       upd.RefPayModeId = Ledger.PayModeId,
       upd.RefConcession = Ledger.Concession,
       upd.RefLumpsump = Ledger.LumpSump,
       upd.RefConcessionDtl = Ledger.ConcessionDtl,
       upd.RefLumpsumpDtl = Ledger.LumpsumpDtl
FROM   ' + @UpdateTable + N' upd
INNER JOIN Ledger
        ON Ledger.PaymentStdLedgerId = upd.StdLedgerId
       AND Ledger.PaymentPayModeId = upd.PayModeId
WHERE  Ledger.[RowNumInGroup] = 1; --ensure same behavior as TOP 1 within the CURSOR
';

EXEC (@SQL);