title: Benefit of using Set Based Query over RBAR Query
body: <p>At first, I am apologizing for my bad English.</p>
  
  <p>The problem is--</p>
  
  <p>In my Database, I have a table consisting of 307763 rows. Everyday I need to process each row.To Process the rows I write a SQL query to SELECT, INSERT and UPDATE in the database.Execution time for the query is  <strong>30 minutes</strong> to <strong>1 hour</strong>.</p>
  
  <p>Then I made a Visual Studio console application to run the process.</p>
  
  <p>Here is the problem arise. In App.Config there is a time limit for Database Connection Time Out for 30 seconds. I also tried to by making the value to 0.</p>
  
  <p>Is there any secure way to keep the connection open for 1 hour or any other way to solve the problem? </p>
  
  <p>Here is the query:</p>
  
  <pre><code>DECLARE @totalEmployee AS INT;
  DECLARE @i AS INT=1;
  DECLARE @employee TABLE
  (
      Id INT PRIMARY KEY IDENTITY(1,1),
      SystemId VARCHAR(30),
      PreRecruitmentEmployeeId VARCHAR(30),
      DOJ DATETIME,
      DOS DATETIME,
      ProbationConfirmEntryDate DATETIME
  )
  
  INSERT INTO @employee
  SELECT EI.SystemId, EI.PreRecruitmentEmployeeId, EI.DOJ, EI.DOS, EI.ProbationConfirmEntryDate FROM EmployeeInformation EI WHERE EI.EmployeeStatus='Active';
  SELECT @totalEmployee=COUNT(*) FROM @employee;
  WHILE @i&lt;= @totalEmployee
    BEGIN
      UPDATE EmployeeDocument SET 
             DueProcessDateTime=CASE WHEN ED.DueProcessDateTime IS NULL THEN GETDATE() ELSE ED.DueProcessDateTime END, 
             IsMailSend=CASE WHEN ED.DueProcessDateTime IS NULL THEN 1 ELSE ED.IsMailSend END,
             DueDate=
              CASE 
                  WHEN CD.DependateDate='AsAndWhen' THEN NULL
                  WHEN CD.DependateDate='AppointmentDate' THEN 
                      CASE WHEN E.PreRecruitmentEmployeeId IS NULL AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ) ELSE
                          CASE WHEN PRE.ApprovedDateTime IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, PRE.ApprovedDateTime) 
                          END
                      END
                  WHEN CD.DependateDate='AgreedJoinDate' THEN
                      CASE WHEN E.PreRecruitmentEmployeeId IS NULL AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ) ELSE
                          CASE WHEN PRE.AgreedDOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, PRE.AgreedDOJ) 
                          END
                      END
                  WHEN CD.DependateDate='ResignationApplyDate' THEN (SELECT TOP(1) CASE WHEN ResignationDate&lt;&gt;'' THEN DATEADD(DAY, CD.LeadOrLagDays,ResignationDate)
                                                                      ELSE NULL END FROM TRN.Resignation WHERE EmployeeId=E.SystemId  ORDER BY ResignationDate DESC)
                  WHEN CD.DependateDate='ApprovedResignationEffectiveDate' THEN (SELECT TOP(1) CASE WHEN ApprovedEffectiveDate&lt;&gt;'' THEN DATEADD(DAY, CD.LeadOrLagDays,ApprovedEffectiveDate)
                                                                      ELSE NULL END FROM TRN.Resignation WHERE EmployeeId=E.SystemId  ORDER BY ApprovedEffectiveDate DESC)
                  WHEN CD.DependateDate='JoiningDate' AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ)
                  WHEN CD.DependateDate='LetterOfIndentDate' THEN 
                      CASE WHEN E.PreRecruitmentEmployeeId IS NULL AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ) ELSE
                          CASE WHEN PRE.SelectionDateTime IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, PRE.SelectionDateTime) 
                          END
                      END
                  WHEN CD.DependateDate='ProfileSubmit' THEN 
                      CASE WHEN E.PreRecruitmentEmployeeId IS NULL AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ) ELSE
                          CASE WHEN PRE.SelectionDateTime IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, PRE.SelectionDateTime) 
                          END
                      END
                  WHEN CD.DependateDate='ProbitionPeriodConfirmationDate' AND E.ProbationConfirmEntryDate IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.ProbationConfirmEntryDate)
                  WHEN CD.DependateDate='PromotionDate' THEN NULL
                  WHEN CD.DependateDate='SeparationDate' AND E.DOS IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOS)
                  WHEN CD.DependateDate='SelectionDate' THEN
                  CASE WHEN E.PreRecruitmentEmployeeId IS NULL AND E.DOJ IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, E.DOJ) ELSE
                          CASE WHEN PRE.SelectionDateTime IS NOT NULL THEN DATEADD(DAY, CD.LeadOrLagDays, PRE.SelectionDateTime) 
                          END
                      END
              END
          FROM EmployeeDocument ED
          JOIN HKP.ComplianceDocument CD ON CD.Id=ED.ComplianceDocumentId
          LEFT JOIN @employee E ON E.SystemId=ED.EmpSystemID
          LEFT JOIN PreRecruitmentEmployee PRE ON PRE.Id=E.PreRecruitmentEmployeeId
          WHERE E.Id=@i AND ED.FileId IS NULL                     
      SET @i = @i + 1;
    END
  </code></pre>