Improve INSERT INTO SELECT Query performance?

Question

I am trying to improve old while loop query. So far tried this but it is still slow. Not sure may be SUM perform slow.

Old query (10 mins+)

ALTER PROCEDURE [dbo].[process_tax]
   @userid VARCHAR(10),
   @remark NVARCHAR(500),
   @tdate DATE,
   @roadno NVARCHAR(10),
   @inst INT
AS
BEGIN 
    IF OBJECT_ID('tempdb..#tempProcess_tax_1') IS NOT NULL  
        DROP TABLE #tempProcess_tax_1

    CREATE TABLE #tempProcess_tax_1 
    (
         RowID INT IDENTITY(1, 1), 
         clid_ INT,
         hlid_ INT,
         holdinNo_ NVARCHAR(500),
         holding_ NVARCHAR(50),
         clientid_ NVARCHAR(500),
         clientName_ NVARCHAR(500)
    )

    INSERT INTO #tempProcess_tax_1 (clid_, hlid_, holdinNo_, holding_, clientid_, clientName_)
        SELECT
            cl.clid AS clid_, cl.id AS hlid_, holdinNo, holding,
            ClientID AS clientid_, ClientName AS clientName_  
        FROM
            tx_holding AS cl 
        WHERE
            cl.status = 1 AND cl.roadno = @roadno
            AND cl.id IN (SELECT hlid FROM tx_asset WHERE asset IS NOT NULL) 
            AND cl.clid IN (SELECT id FROM tbl_client WHERE client_type = 'Non-Govt.') 
            AND cl.id NOT IN (SELECT hlid FROM tx_bill_pay 
                              WHERE YEAR(date_month) = YEAR(@tdate) 
                                 AND hlid IS NOT NULL 
                              GROUP BY hlid)

    DECLARE @NumberRecords_1 INT, @RowCounter_1 INT

    SET @NumberRecords_1 = (SELECT COUNT(*) FROM #tempProcess_tax_1)
    SET @RowCounter_1 = 1

    WHILE @RowCounter_1 <= @NumberRecords_1
    BEGIN
        DECLARE @clid_ INT
        DECLARE @hlid_ INT
        DECLARE @holdinNo_ NVARCHAR(50)
        DECLARE @holding_ NVARCHAR(50)
        DECLARE @clientid_ NVARCHAR(100)
        DECLARE @clientName_ NVARCHAR(250)
        DECLARE @bill AS MONEY
        DECLARE @sr AS MONEY;

   SELECT  
       @clid_ = clid_,
       @hlid_ = hlid_,
       @holdinNo_ = holdinNo_, @holding_ = holding_,
       @clientid_ = clientid_, @clientName_ = clientName_
   FROM 
       #tempProcess_tax_1 
   WHERE 
       RowID = @RowCounter_1

SET @bill = (SELECT 
                 CASE WHEN SUM(netvalue) IS NULL
                         THEN 0 
                         ELSE SUM(netvalue) 
                 END  
             FROM 
                 tx_bill 
             WHERE 
                 hlid = @hlid_ 
                 AND itemID NOT IN (8, 6) 
                 AND YEAR(date_month) = YEAR(@tdate))
    SET @sr = (SELECT
                   CASE WHEN SUM(asset * rate / 100) IS NULL
                           THEN 0 
                           ELSE SUM(asset * rate / 100) 
                   END
               FROM 
                   tx_bill 
               WHERE
                   hlid = @hlid_ 
                   AND itemID = 6 
                   AND YEAR(date_month) = YEAR(@tdate))

    INSERT INTO tx_bill_pay(clid, hlid, swercharge, pay_bill, pdate, bill_id, holdingNo, holding, ClientID, ClientName, billno, date_month, bill, install, inserted_by, inserted_date) 
    VALUES (@clid_, @hlid_, @sr, @bill / 4, DATEADD(day, -1, DATEADD(m, 3, @tdate)), CONCAT(@holdinNo_, YEAR(@tdate), '1'), @holdinNo_, @holding_, @clientid_, @clientName_, CONCAT(@holdinNo_, YEAR@tdate)), @tdate, @bill, 1, @userid, GETDATE())

    INSERT INTO tx_bill_pay(clid, hlid, swercharge, pay_bill, pdate, bill_id, holdingNo, holding, ClientID, ClientName, billno, date_month, bill, install, inserted_by, inserted_date) 
    VALUES (@clid_, @hlid_, 0, 2 * (@bill / 4), DATEADD(day, -1, DATEADD(m, 6, @tdate)), CONCAT(@holdinNo_, YEAR(@tdate), '2'), @holdinNo_, @holding_, @clientid_, @clientName_, CONCAT(@holdinNo_, YEAR(@tdate)), @tdate, @bill, 2, @userid, GETDATE())

    SET @RowCounter_1 = @RowCounter_1 + 1
END

DROP TABLE #tempProcess_tax_1
END

New query (1-2 mins)

ALTER PROCEDURE [dbo].[process_tax]
   @userid varchar(10),
   @remark nvarchar(500),
   @tdate date ,
   @roadno nvarchar(10),
   @inst int
   as
BEGIN 

 insert  into tx_bill_pay(
          clid,
          hlid,
          swercharge,
          pay_bill,
          pdate,
          bill_id,
          holdingNo,
          holding,
          ClientID, 
          ClientName,
          billno, 
          date_month, 
          bill,
          install ,
          inserted_by, 
          inserted_date)

     select 
           cl.clid,
           cl.id,
     swercharge=(select  case when sum(asset*rate/100) is null then 0 else 
               sum(asset*rate/100) end  from tx_bill where hlid=cl.id and 
               itemID =6 and year(date_month)=YEAR(@tdate)),
     pay_bill=(select  case when sum(netvalue) is null then 0 else 
             sum(netvalue) end  from tx_bill where hlid=cl.id and itemID not 
             in(8,6) and year(date_month)=YEAR(@tdate))/4,  
    DATEADD(day,-1,
    DATEADD(m,3,@tdate)),
    CONCAT(cl.holdinNo, year(@tdate),'1'),
    cl.holdinNo,
    cl.holding,
    cl.ClientID, 
    cl.clientName, 
    CONCAT(cl.holdinNo, 
    year(@tdate)), 
    @tdate,
    bill=(select  case when sum(netvalue) is null then 0 else sum(netvalue) 
         end  from tx_bill where hlid=cl.id and itemID not in(8,6) and 
         year(date_month)=YEAR(@tdate))/4,
    1, 
    @userid, getdate()  
    from  
    (select * 
    from tx_holding as cl 
    where cl.status=1 and cl.roadno=@roadno) AS cl
    INNER JOIN (
     select DISTINCT hlid from tx_asset where asset is not null
     ) AS A 
    ON Cl.id = A.hlid 
     INNER JOIN (
      select DISTINCT  id from tbl_client where client_type='Non-Govt.'
     ) AS C 
    ON   cl.clid=C.id
    WHERE    NOT EXISTS
        (   SELECT  1
            FROM    tx_bill_pay as bp
            WHERE    year(date_month)=year(@tdate)
            and bp.hlid=cl.id
        )   


 insert  into tx_bill_pay(clid,hlid 
    ,swercharge,pay_bill,pdate,bill_id,holdingNo,holding,ClientID, 
    ClientName, billno, date_month, bill, install ,inserted_by, 
    inserted_date)
select 
    cl.clid,
    cl.id,
    0,
    pay_bill=2*((select  case when sum(netvalue) is null then 0 else sum(netvalue) end  from tx_bill where hlid=cl.id and itemID not in(8,6) and year(date_month)=YEAR(@tdate))/4),
    DATEADD(day,-1,
    DATEADD(m,3,@tdate)),
    CONCAT(cl.holdinNo, year(@tdate),'2'),
    cl.holdinNo,
    cl.holding,
    cl.ClientID, 
    cl.clientName, 
    CONCAT(cl.holdinNo, year(@tdate)) , 
    @tdate,
    bill=(select  case when sum(netvalue) is null then 0 else sum(netvalue) 
         end  from tx_bill where hlid=cl.id and itemID not in(8,6) and year(date_month)=YEAR(@tdate))/4,
    2, 
    @userid, getdate()  
from  
    (select * 
    from tx_holding as cl 
    where cl.status=1 and cl.roadno=@roadno) AS cl
    INNER JOIN (
     select DISTINCT hlid from tx_asset where asset is not null
     ) AS A 
    ON Cl.id = A.hlid 
     INNER JOIN (
      select DISTINCT  id from tbl_client where client_type='Non-Govt.'
     ) AS C 
    ON   cl.clid=C.id
    WHERE    cl.id not in
        (   SELECT  hlid
            FROM    tx_bill_pay
            WHERE  year(date_month)=year(@tdate)
            and hlid is not null group by hlid
        )

Answer 1

I added non clustered index then my query take 5 sec to run.That's almost solve my problem. @Alex Thanks for your hard work and time. I will check your tips.I Up vote your comment

Answer 2

Great Job removing the loop!

I will point out one possible performance problem, specifically year(date_month)=year(@tdate).

Because a column is wrapped in a function, it is non-SARGABLE. This means that date_month values cannot be evaluated directly and therefore, indexes and statistics for this column cannot be used.

To resolve this I suggest the following changes:

At the top of the SP define two more variables:

DECLARE @YearStart AS DATETIME, @NextYearStart DATETIME
SET @YearStart = DATEADD(yy, DATEDIFF(yy, 0, @tdate ), 0 )
SET @NextYearStart = DATEADD( yy, @YearStart, 1 )

Then replace all instances of year(date_month)=year(@tdate) with

@YearStart <= date_month AND date_month < @NextYearStart

This above expression looks for date_month values greater than or equal to midnight (notice that time component is accounted for) of the first day of the year and less than midnight of the next year.

Next, I would look at the query plan and see if SQL Server gives a "Missing Index" recommendation (it should appear just above the plan diagram, if it does suggest an index). Try adding the recommended missing indexes, then check to see if you get a performance improvement. If you don't get an improvement, remove the index - sometimes suggestions don't help.

Updated

Having 3 sub-queries (populating columns swercharge, pay_bill, bill) using tx_bill table with different WHERE conditions will cause at least 3 evaluations of this table per one main query execution.

It may, depending on the size of the table, be more efficient to calculate all 3 sub-queries once and save results to a temp table (or a table variable) as follows:

SELECT hlid,
    ISNULL( SUM( CASE WHEN itemID NOT IN (8, 6) THEN netvalue END ), 0 ) AS Bill,
    ISNULL( SUM( CASE WHEN itemID = 6 THEN asset * rate / 100 END ), 0 ) AS Sr
INTO #Bills
FROM tx_bill 
WHERE @YearStart <= date_month AND date_month < @NextYearStart
    AND NOT hlid IS NULL
GROUP BY hlid

In your main queries, join to this table as follows:

LEFT JOIN #SumBills AS SumBills ON SumBills.hlid = cl.hlid

and in SELECT change assignments as per below example:

pay_bill= ISNULL( SumBills.Bill, 0 ) / 4,

Note: I believe you have a bug in bill column, as the value is divided by 4, where it is not in the original cursor.

The last point:

As @GarethD has said in his answer to your previous question, properly formatting your code significantly reduces the time it takes to understand and change the code. I would go one step further and say that code format represents your attitude to and understanding of the problem at hand.