CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Covert
Covert

Reputation: 491

How to prevent timeout in query?

SELECT C.CompanyName,
       B.BranchName, 
       E.EmployerName,
       FE.EmployeeUniqueID,
       pcr.EmployerUniqueID, 
       Case when FE.Status_id= 1 then 1 else 0 end IsUnPaid, 
       Case when re.EmployeeUniqueID IS NULL OR re.EmployeeUniqueID= '' then 0 else 1 end AS 'EmployeeRegistration',
       FE.IncomeFixedComponent,
       FE.IncomeVariableComponent, 
       Convert(varchar(11), Fe.PayStartDate, 106) as PayStartDate,
       Convert(varchar(11), Fe.PayEndDate, 106) as PayEndDate,
       S.StatusDescription, 
       FE.IsRejected, 
       FE.ID 'EdrID',   
       Convert(varchar(20), tr.TransactionDateTime, 113) as TransactionDateTime, 
       tr.BatchNo, 
       tr.IsDIFCreated, 
       Convert(varchar(20),tr.DIFFileCreationDateTime,113) as DiffDateTime
    From File_EdrEntries FE 
    JOIN PAFFiles pe ON pe.ID = FE.PAFFile_ID
    inner Join RegisteredEmployees RE
    ON RE.EmployeeUniqueID= FE.EmployeeUniqueID
    inner join File_PCREntries pcr on pe.ID=pcr.PAFFile_ID 
    JOIN Employers E ON E.EmployerID = pcr.EmployerUniqueID
    JOIN Branches B ON B.BranchID = E.Branch_ID 
    JOIN companies C ON C.COMPANYID = B.COMPANY_ID  
    JOIN Statuses S ON S.StatusID = FE.Status_ID
    JOIN Transactions tr on tr.EDRRecord_ID= fe.ID
    where  E.Branch_id=3
    AND FE.IsRejected=0 AND FE.Status_id= 3 and tr.BatchNo is not null
    AND Re.Employer_ID= re.Employer_ID;

THis query is supposed to return 10 million or more records and it usually causes timeout because of large no of records. So how can I improve its performance becauses I have done in where condition what I could.

Upvotes: 3

Views: 5820

Answers (2)

Josh
Josh

Reputation: 424

Find out which combination of tables filters the most data. for example if the following query filters out the majority of data you could consider creating a temp table with the data needed, index it and then use that in your bigger query. 

SELECT fe.*,re.*    
From File_EdrEntries FE 
    inner Join RegisteredEmployees RE
    ON RE.EmployeeUniqueID= FE.EmployeeUniqueID

Breaking out the query into smaller chunks is likely the best way to go. Also make sure you have proper indexes in place

Upvotes: 0

Vikrant
Vikrant

Reputation: 5046

First of all, you need to

  1. optimize query more
  2. Add required Indexes to tables involved in query

Then,

You can use this, to increase Query Timeout:

SET LOCK_TIMEOUT 1800;  
SELECT @@LOCK_TIMEOUT AS [Lock Timeout];

Also, refer This Post

Upvotes: 2

Related Questions