Here Is My SQL Query :

ALTER PROCEDURE [dbo].[usp_rpt_AccountStatement]

@dateFrom smalldatetime = null,

@dateTo smalldatetime = null,

@AccountNum nvarchar(20) = null,

@ComponentNumber nvarchar(20) = NULL

AS

BEGIN

SET NOCOUNT OFF;

CREATE TABLE #Accounts (AccountNumber nvarchar(20))

DECLARE @ISLEAF BIT

SELECT @IsLeaf = IsLeafAccount FROM Account WHERE AccountNum = @AccountNum

IF @IsLeaf = 0

BEGIN INSERT INTO #Accounts(AccountNumber) SELECT AccountNum FROM dbo.fnGetAccountChildren(@AccountNum) END

ELSE

BEGIN INSERT INTO #Accounts(AccountNumber) VALUES (@AccountNum) END

SELECT TransactionDetail.Direction * TransactionDetail.Amount AS Debit,

ABS((TransactionDetail.Direction - 1) * TransactionDetail.Amount) AS Credit, [Transaction].TransactionDate, [Transaction].Description_En, [Transaction].Description_Ar,

Component.ComponentId,Component.ComponentName_En,Component.ComponentName_Ar

FROM [Transaction] INNER JOIN TransactionDetail ON [Transaction].TransactionId = TransactionDetail.TransactionId

INNER JOIN Account ON TransactionDetail.AccountId = Account.AccountId

INNER JOIN [Component] ON ([Transaction].[ComponentId] = [Component].[ComponentNumber])

WHERE

((@datefrom IS NULL) OR (@dateTo IS NOT NULL) OR ([Transaction].TransactionDate >= @datefrom) )

AND ((@dateto IS NULL) OR(@dateFrom IS NOT NULL) OR ([Transaction].TransactionDate <= @dateto) )

AND ((@dateFrom IS NULL) OR (@dateTo IS NULL) OR ([Transaction].TransactionDate BETWEEN @datefrom AND @dateto))

AND Account.AccountNum IN (SELECT AccountNumber COLLATE database_default FROM #Accounts)

AND ((@ComponentNumber IS NULL) OR ([Transaction].[ComponentId] = @componentNumber))

ORDER BY [Transaction].[TransactionDate]

END

i ran it now it Retrieved 5200 rows in 1.48 minute

The Transaction Table has 17720 records, The TransactionDetail table has 36346 records, The Account table has 718 records and finally the Component table has 110 records.