SQLCLR running too slow

Question

I have 1.6 million rows in a table. there are 100,000 rows which has some information missing. To locate that information without duplicate references. I have written a SQL CLR procedure. It is executing at very slower speed. Only 5000 Rows are processed out on 100,000 in 30 minutes time.

Can below code can be replaced with Inline 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

The problem here is not SQLCLR. The problem is that SQLCLR is being used when there is absolutely no reason for doing so. Unless I am missing something, this operation is just a simple cursor, doing the SELECT FROM vw_Payment_Ledger_Matching_inst and the UPDATE {updateTable} per every row returned from the SELECT FROM vw_Payment_Ledger_Matching_Other query. And even if the 3 SqlConnections are using Context Connect = true;, it is still executing a non-parameterized query (as pointed out by @usr in a comment on the Question) and creating a new SqlDataReader (i.e. ledger) that is not being closed for each of those 100,000 rows. But again, there is no reason to use SQLCLR here.

Let's look at what this operation is trying to do. It is saying:

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

What you have in C# is semantically / operationally equivalent to the following 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);

The above should be much more efficient than the C# version, but it can still be improved upon to remove the CURSOR. The following set-based approach should be logically equivalent, but all done in a single query:

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);