ChrisM@Work (4/9/2013)
An index on #d2 supporting this join([#d2].MARIMSSALESID) = ([DynamicsV5Realtime].[dbo].[SALESTABLE].MARIMSSALESID)would reduce the exaggerated estimated row counts, but that's besides the point. This lot is unlikely to run well unless the whole caboodle is run remotely. I'd recommend you create a reporting db on server [BIDATAWSQL] and package these queries into a stored procedure in it. Not only would it perform far better than it does now, it would be a heck of a lot easier to perform the remaining tuning. You can only go so far with it as it is.If your udf's, particularly udf_R000_EmployeeList, aren't already configured as inline table-valued functions, then consider recoding them.
Hi all and thanks for the input. I will check out ChrisM's suggestion in an attempt to maybe fix the hash match issue. As for your suggestion on creating a report DB, that is actually what we have however the tester was pointing from a test box to a live server. He was doing this in SSRS and the code was failing validation when executing, which I believe was being caused by the large amount of estimated rows.