Query is taking a long time to execute [closed]

The query is taking too much time to execute. Ots comparing some condition and then fetching data accordingly.

Please guide me why its happening.

DECLARE @Rowcountcnzg int = 0;
DECLARE @countcnzg bigint = (SELECT CASE
                                         WHEN COUNT(*) >= 100000 THEN COUNT(*) / @BatchSize
                                         WHEN (COUNT(*) > 10000
                                           AND COUNT(*) < 100000) THEN COUNT(*) / 10000
                                         ELSE COUNT(*)
                                    END
                             FROM i_hold_sap_stock ss WITH (NOLOCK)
                                  JOIN tea_item ti ON ss.upload_tea_item = ti.tea_item
                                                  AND ss.upload_revision = ti.revision
                                  LEFT OUTER JOIN mdata_origin_channel_default_port ocdp1 ON ocdp1.origin = ti.origin
                                                                                         AND ocdp1.stock_type = ss.upload_stock_type
                                  LEFT OUTER JOIN mdata_origin_channel_default_port ocdp2 ON ocdp2.origin = ti.origin
                                                                                         AND ocdp2.stock_type = ''
                             WHERE Date_Loaded = @MaxDateLoaded
                               AND conv_zgport_ship = 'NULL');

WHILE (@Rowcountcnzg < @countcnzg + 1)
BEGIN
    UPDATE TOP (@BatchSize)
        ss
    SET conv_zgport_ship = ISNULL(ISNULL(ocdp1.default_origin_location, ocdp2.default_origin_location), 'NULL')
    FROM i_hold_sap_stock ss
         JOIN tea_item ti ON ss.upload_tea_item = ti.tea_item
                         AND ss.upload_revision = ti.revision
         LEFT OUTER JOIN mdata_origin_channel_default_port ocdp1 ON ocdp1.origin = ti.origin
                                                                AND ocdp1.stock_type = ss.upload_stock_type
         LEFT OUTER JOIN mdata_origin_channel_default_port ocdp2 ON ocdp2.origin = ti.origin
                                                                AND ocdp2.stock_type = ''
    WHERE Date_Loaded = @MaxDateLoaded
      AND (conv_zgport_ship = 'NULL'
        OR conv_zgport_ship = NULL)
    OPTION (MAXDOP 4);

    SET @Rowcountcnzg = @Rowcountcnzg + 1;
END;

How i calculate batch Size

DECLARE @Rowcount INT = 0;    
  DECLARE @rowstr nvarchar(100);    
  DECLARE @BatchSize INT = 500000;    
  DECLARE @CountECC INT = ( Select CASE when Count(*) >= 100000 THEN count(*)/@BatchSize     
           When (Count(*) > 10000  and Count(*) < 100000)  THEN count(*)/10000       
           ELSE count(*) END     
         FROM i_hold_sap_stock WITH (NOLOCK) JOIN mdata_mixing_unit mu    
           WITH (NOLOCK) ON (SELECT max(output_code)     
              FROM [dbo].[map_codes] WITH(NOLOCK)    
              WHERE UPPER(input_code) = UPPER(upload_mixing_unit)    
              AND UPPER(mapping_type) = UPPER( 'STOCK_PLANTTOMIX')    
              AND site_id = 0    
              AND UPPER(fa_area) = UPPER('TEACONNECT')) = mu.mixing_unit WHERE Date_Loaded=@MaxDateLoaded)    

   WHILE (@Rowcount < (@CountECC+1))       
   BEGIN    
    UPDATE TOP (@BatchSize) i_hold_sap_stock SET conv_mixing_unit = mu.mixing_unit,site_id = mu.site_id    
    FROM i_hold_sap_stock WITH (NOLOCK)    
    INNER JOIN mdata_mixing_unit mu WITH (NOLOCK)    
    ON (SELECT max(output_code)    
      FROM [dbo].[map_codes] WITH(NOLOCK)    
       WHERE UPPER(input_code) = UPPER(upload_mixing_unit)    
      AND UPPER(mapping_type) = UPPER( 'STOCK_PLANTTOMIX')    
      AND site_id = 0    
      AND UPPER(fa_area) = UPPER('TEACONNECT')    
     ) = mu.mixing_unit    
    WHERE Date_Loaded=@MaxDateLoaded        
    OPTION(MAXDOP 4)    
    SET @Rowcount = @Rowcount+1;     
    SET @rowstr = CAST(@Rowcount as nvarchar)     
    RAISERROR(@rowstr, 0, 1) WITH NOWAIT     
   END     

This is my query don’t why its taking too much time to execute. In one SP there are lot of SP But in this SP i am getting more time what should i modify in this please guide me.

Answer

You can try the below query

You are making batches for records greater than 10000, but say if your records are 9999, then your loop goes for 9999 iterations unnecesarilly.

declare @countRecords bigint=(SELECT count(*)       
                             FROM i_hold_sap_stock ss WITH (NOLOCK)
                                  JOIN tea_item ti ON ss.upload_tea_item = ti.tea_item
                                                  AND ss.upload_revision = ti.revision
                                  LEFT OUTER JOIN mdata_origin_channel_default_port ocdp1 ON ocdp1.origin = ti.origin
                                                                                         AND ocdp1.stock_type = ss.upload_stock_type
                                  LEFT OUTER JOIN mdata_origin_channel_default_port ocdp2 ON ocdp2.origin = ti.origin
                                                                                         AND ocdp2.stock_type = ''
                             WHERE Date_Loaded = @MaxDateLoaded
                               AND conv_zgport_ship = 'NULL');



DECLARE @Rowcountcnzg int = 0;
DECLARE @countcnzg bigint = (SELECT CASE
                                         WHEN @countRecords >= 100000 THEN @countRecords / @BatchSize
                                         WHEN (@countRecords > 10000
                                           AND @countRecords < 100000) THEN @countRecords / 10000
                                         ELSE @countRecords/@countRecords END)

IF(@countRecords<10000)
set @BatchSize=@countRecords

WHILE (@Rowcountcnzg < @countcnzg + 1)
BEGIN

    UPDATE TOP (@BatchSize)
        ss
    SET conv_zgport_ship = ISNULL(ISNULL(ocdp1.default_origin_location, ocdp2.default_origin_location), 'NULL')
    FROM i_hold_sap_stock ss
         JOIN tea_item ti ON ss.upload_tea_item = ti.tea_item
                         AND ss.upload_revision = ti.revision
         LEFT OUTER JOIN mdata_origin_channel_default_port ocdp1 ON ocdp1.origin = ti.origin
                                                                AND ocdp1.stock_type = ss.upload_stock_type
         LEFT OUTER JOIN mdata_origin_channel_default_port ocdp2 ON ocdp2.origin = ti.origin
                                                                AND ocdp2.stock_type = ''
    WHERE Date_Loaded = @MaxDateLoaded
      AND (conv_zgport_ship = 'NULL'
        OR conv_zgport_ship = NULL)
    OPTION (MAXDOP 4);

    SET @Rowcountcnzg = @Rowcountcnzg + 1;
END;