title: How to improve while loop insert performance in sql server?
body: <p>Here is my SQL Query. It's insert almost 6500+ row from temp table. But its takes 15+ mins! . How can i improve this ? Thanks</p>
  
  <pre><code>ALTER proc [dbo].[Process_bill]
  @userid varchar(10),
  @remark nvarchar(500),
  @tdate date ,
  @pdate date
  as
  
  BEGIN 
  
  
  IF OBJECT_ID('tempdb.dbo..#temptbl_bill', 'U') IS NOT NULL
      DROP TABLE #temptbl_bill; 
  
  CREATE TABLE #temptbl_bill (
     RowID int IDENTITY(1, 1), 
     ------------
  
  )
  
  // instert into temp table
  
  DECLARE @NumberRecords int, @RowCounter int
  DECLARE @batch INT
  SET @batch   = 300
  SET @NumberRecords = (SELECT COUNT(*) FROM #temptbl_bill)
  SET @RowCounter = 1
  
  SET NOCOUNT ON  
  
  BEGIN TRANSACTION
  
  WHILE @RowCounter &lt;= @NumberRecords
  BEGIN
  
  declare @clid int
  declare @hlid int
  declare @holdinNo nvarchar(150)
  declare @clientid nvarchar(100)
  declare @clientName nvarchar(50)
  declare @floor int
  declare @radius nvarchar(50)
  declare @bill money 
  declare @others money
  declare @frate int
  declare @due money
  DECLARE @fine money
  DECLARE @rebate money
  
  IF @RowCounter &gt; 0 AND ((@RowCounter % @batch = 0) OR (@RowCounter = @NumberRecords))           
  BEGIN                   
      COMMIT TRANSACTION
      PRINT CONCAT('Transaction #', CEILING(@RowCounter/ CAST(@batch AS FLOAT)), ' committed (', @RowCounter,' rows)');
      BEGIN TRANSACTION
  END;
  
   // multiple select 
  
  
  // insert to destination  table
  
  Print 'RowCount -' +cast(@RowCounter as varchar(20))  + 'batch -' + cast(@batch as varchar(20))
  
  SET @RowCounter = @RowCounter + 1;
  
  END
  
  COMMIT TRANSACTION
  
  PRINT CONCAT('Transaction #', CEILING(@RowCounter/ CAST(@batch AS FLOAT)), ' committed (',
  @RowCounter,' rows)');
  
  SET NOCOUNT OFF 
  
  DROP TABLE #temptbl_bill
  
  END
  
  GO
  </code></pre>