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 ?