There are a few thing I noticed:
a) In your WHERE clause you use CAST to convert receiptdate into a char value and compare it with @FromDate and @ToDate.
It's already in the code. Why don't you use it?
I'd expect to see an index seek if there's no CAST involved.
b) There's an RID Lookup against tfsReceiptDetails. The missing columns (Amount, RemainingAmount, tSubContractId) might need to go into the INCLUDE section of the IX_tfsReceiptDetails_tReceiptID index)
c) You're joining a view. What's the query behind it?
d) Except for tFSPromissoryNotes, the other two tables in the function are already used in the outer query.
To summarize it: there's plenty room for improvement. I'd probably start with separating the data that need to be aggregated into a separate query (indexed temp table) and join the rest of the tables afterwards. The function would not be part of this step at all.
The next step would be rewriting the function either into a inline-Table-valued function or even just using CROSS APPLY and a select to the missing table.
After that, I'd check if there are any missing indexes.
And I wouldn't cast any data in the WHERE clause...
LutzA pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]