Suggestions to reduce query loading time

There are LOTS of clustered index scans (i.e. that's the entire table). Plus, your aggregation on table31.column5 seems to be producing a large amount of the IO in the final query.

Plus, it looks like you're joining to table4 multiple times. That might be a prime opportunity to write a multi-table join which includes transitive join. Some versions of the optimizer don't automatically know "If A=B, and B=C, than A=C". I can't see your SQL code, but many SQL devs write the A=B and B=C part of a join clause, but do not include the last subclause.

This might be a good chance to apply that technique. Of course, I'd prefer to see and comment on the real code. 🙂

-Kev

AND CONVERT(DATETIME, CONVERT(VARCHAR(30), AA.sdtExpectedFunding, 101)) 
      < CONVERT(DATETIME, CONVERT(VARCHAR(30), GETDATE(), 101))

Should be changed to this, especially if there is an index on this column:

AND AA.sdtExpectedFunding < CONVERT(DATE, GETDATE())

Basically all of the other places where you're converting to a string and 101 – just return the data to the client or report and let that layer handle prettifying.

The query text is truncated because the cached plan doesn't contain the entire query, so there isn't much else I could see, except this column looks suspicious and inefficient:

 SELECT dbo.udfCLRConcatenateSemiColonDelimited(ORG.vchOrgName)
     FROM dbo.tsjContractBLKLegalEntity CLE
     INNER JOIN dbo.tsmOrganization ORG 
     ON CLE.intOrganizationID = ORG.intOrganizationID
     WHERE CLE.intContractID = C.intContractID

This will essentially invoke that CLR function for every single row (6,400+ invocations!). Perhaps you could give up some storage space and store those concatenations elsewhere (maintained by a trigger that calls the same CLR function, if necessary), especially if this read query happens a lot more often than each of those delimited lists would actually change. Or you could consider passing that set back separately and have the client layer perform the concatenation (C# is going to be more efficient at string concatenation that T-SQL will be at brokering that work out to C# in the CLR as part of the query).