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??
I looked at the actual query plan you kindly uploaded. The stand-out features were the clustered index scan of the TransactionDetail table and the table scan of the temporary table.
Full scans on the inner side of a loop join are rarely good news - especially when the outer input has many rows, and so does the table being scanned. If you hadn't been able to create the new indexes, I would have suggested forcing a HASH or MERGE join for those parts of the plan. Not as optimal as the index by any means, but at least TransactionDetail would only have been scanned once.
I wrote the index definition by inspection: clearly the lookup was on the ID (which I mistakenly assumed was UNIQUE). Adding the included columns wasn't required, by they were listed as output columns in the plan so I thought I'd add them for neatness, and to avoid a key lookup.
I could have saved myself 2 minutes' work by looking at the 'missing index' information in the plan - but the way forward was pretty clear anyway.
mentioned, it's most just a question of experience :-)