Paul's Suggestion

CREATE NONCLUSTERED INDEX [UQ dbo.TransactionDetail TransactionId (AccountId, Direction, Amount)]

ON [dbo].[TransactionDetail] (TransactionId ASC)

INCLUDE (AccountId, Direction, Amount)

WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, ONLINE = OFF);

but i removed the UNIQUE from...

Thanks alot it works... now it retrieves the 5200 records in only one second...

thanks alot again i appreciate that alot

but please may i ask about the tools you used??

When i removed the Order By clause the time decreased from "1 minute and 48 seconds" to "2 seconds only".....but i really need this Order By Clause

attached the updated plan and a txt file that Contains : the 4 tables creation, the stored proc. and the function i used in the stored proc.

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...