Prince Jain

Prince Jain

  • 2k
  • 137
  • 4.2k

Query optimization

May 25 2022 8:38 AM

Hi  All,

I wrote a query to fetch the datafrom database but it's taken 1 min 38 sec to fetch the records 

Please find the script below 

DECLARE  @BankAccount NVARCHAR(200) = 'ALL',    
    @Month NVARCHAR(100) = 'ALL',
    @FromDate DATETIME = '01-Jan-2000',
    @ToDate DATETIME = '25-Apr-2022',
    @Employer NVARCHAR(12) = 'ALL',
    @TransactionType Varchar(50) = 'ALL',
    @PayGroup NVARCHAR(max) = 'ALL',
    @AccountType Varchar(50) = 'ALL',
    @Status varchar(20) = 'ALL',
    @agent varchar(100) = 'ALL',
    @Bank NVARCHAR(200) = 'ALL',
    @pagenumber INT = 1,
    @pagesize INT = 20,
    @SortCol NVARCHAR(200) = '_Employer',
    @SortDir NVARCHAR(200) = 'asc'
DECLARE @All VARCHAR(10) = 'ALL'
SELECT  
    TransactionDate AS _TransactionDate,
    IntendedDate AS _IntendedDate,
    CAST(Employer AS VARCHAR(MAX)) AS _Employer,
    CAST(Paygroup AS VARCHAR(MAX)) AS _Paygroup,
    REPLACE(CAST(TransactionType AS VARCHAR(MAX)),'–','-') AS _TransactionType,
    CAST(Amount AS VARCHAR(MAX)) AS Amount,
    CAST([DR/CR] AS VARCHAR(MAX)) AS Debit_Credit,
    CAST(BankRecTransStatusCode AS VARCHAR(MAX)) AS MatchStatus,
    Amount AS _Amount
    FROM table1 As pt
    INNER JOIN tablle2  As bar 
         ON pt.BRSAccountID = bar.BRSAccountID
    INNER JOIN table3 AS Ac 
         ON bar.AccountTypeId = Ac.AccountTypeID
         WHERE 
    (TransactionDate >= @FromDate AND TransactionDate <= @ToDate) AND
    ((@Employer = @All OR @Employer IS NULL) OR (pt.Employer =@Employer)) AND
    ((@TransactionType = @All OR @TransactionType IS NULL) OR (pt.TransactionType =@TransactionType)) AND
    ((@Paygroup = @All OR @Paygroup IS NULL) OR (pt.Paygroup =@Paygroup)) AND
    ((@BankAccount = @All OR @BankAccount IS NULL) OR (pt.AccountNumber =@BankAccount)) AND
     ((@Bank = @All OR @Bank IS NULL) OR ('AU'+LEFT(bar.BSB, 2)=@Bank)) AND
    ((@AccountType = @All OR @AccountType IS NULL) OR (Ac.AccountTypeDesc =@AccountType)) AND
    ((@Status = @All OR @Status IS NULL) OR (bar.Status =@Status)) AND
    ((@agent = @All OR @agent IS NULL) OR ( @agent IS NOT NULL and EXISTS (SELECT ag.AssignedAgent as AssignedAgent FROM table4 ag WHERE ag.BRSAccountID = pt.BRSAccountID)))
         ORDER BY  
           Case 
           when @SortDir = 'desc' and  @SortCol = '_TransactionDate' then TransactionDate end desc   ,
           Case when @SortDir = 'desc' and   @SortCol = '_IntendedDate' then IntendedDate end desc ,
           Case when @SortDir = 'desc' and   @SortCol = '_Employer' then Employer  end desc,
           Case when @SortDir = 'desc' and  @SortCol = '_Paygroup' then Paygroup end desc,
           Case when @SortDir = 'desc' and   @SortCol = '_TransactionType' then TransactionType end desc,
           Case when @SortDir = 'desc' and  @SortCol = 'Debit_Credit' then [DR/CR] end desc,
           Case when @SortDir = 'desc' and  @SortCol = 'MatchStatus' then  BankRecTransStatusCode end desc,
           Case when @SortDir = 'desc' and  @SortCol = '_Amount' then Amount end desc,
 
          
           Case when @SortDir <> 'desc' and  @SortCol = '_TransactionDate' then TransactionDate end asc   ,
           Case when @SortDir <> 'desc' and   @SortCol = '_IntendedDate' then IntendedDate end asc ,
           Case when @SortDir <> 'desc' and   @SortCol = '_Employer' then Employer  end asc,
           Case when @SortDir <> 'desc' and  @SortCol = '_Paygroup' then Paygroup end asc,
           Case when @SortDir <> 'desc' and   @SortCol = '_TransactionType' then TransactionType end asc,
           Case when @SortDir <> 'desc' and  @SortCol = 'Debit_Credit' then [DR/CR] end asc,
           Case when @SortDir <> 'desc' and  @SortCol = 'MatchStatus' then  BankRecTransStatusCode end asc,
           Case when @SortDir <> 'desc' and  @SortCol = '_Amount' then Amount end asc
      OFFSET (@pagenumber-1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY 

 

Could you please suggest how I optimize it ?


Answers (2)