Hmmm... Why do you say it is using too much tempdb? With all those subqueries I'm not surprised! As Ken stated it would be nearly impossible to do the analysis of this without knowing the data.
Having said that. If you are doing this in a stored procedure I would certainly move some of it to temp tables just for readability if nothing else.
I would start that process off by creating a temp table that holds the following.
select dtser, TableB_H.cusip, TableB_H.shares
from TableB_H
where dtser > 20010925
I would then create another with the following probaby using the temp table from above.
select dtser, TableB_H.cusip, TableB_H.shares
from TableB_H
join TableC_R on TableB_H.dtser = TableC_R.[date] and TableB_H.cusip = TableC_R.cusip
where dtser > 20010925)
union
select a.dtser, cusip, shares
from TableA_D
join (select dtser, TableB_H.cusip, TableB_H.shares
from TableB_H
left join TableC_R on TableB_H.dtser = TableC_R.[date] and TableB_H.cusip = TableC_R.cusip
where dtser > 20010925 and TableC_R.cusip is null) as a
on TableA_D.dtser = a.dtser and TableA_D.axys_cusip = a.cusip
You may also want to add an index or two to help with performance. Sure this will use TempDB but as I see it no matter how you build it you will do that. So break it down and see if/where you can improve performance. I would also convert those left joins to NOT EXISTS correlated subqueries.
Do any of these subqueries pull back only one record? If so you might try using variables instead. I would also highly suggest using comments inside this to let you/others know why you are doing what you are doing. It will make it so much easier to debug this when it fails in 6 months!
Good luck! That is one nasty query!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.