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 ?