February 8, 2004 at 7:12 pm
I have a query with a bunch of nested queries and a cross join. I would like to know the best methods to use to rewrite this query to make it more efficient. The current query is using a lot of tempdb ... too much. Why is this? What are some ways to get around this? Thanks.
select TableA_D.dtser, sum((mkt_v/mkt_val) * [return]) as por_ret from TableA_D join
( (select dtser, TableB_H.cusip, [return] 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, tr1db 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 ) as a
on TableA_D.dtser = a.dtser and TableA_D.axys_cusip = a.cusip join
(select dtser as dtser_lag, min(dtser_for) as dtser from
(select dtser, dtser_for from
(select distinct dtser from TableB_H) as a cross join
(select distinct dtser as dtser_for from TableB_H) as b
where dtser < dtser_for) as c
group by dtser) as b
on TableA_D.dtser = b.dtser join
(select dtser, sum(mkt_value) as mkt_val from
(select a.dtser, cusip, (a.shares*price_u) as mkt_value from TableA_D join
( (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 ) as a
on TableA_D.dtser = a.dtser and TableA_D.axys_cusip = a.cusip) as a
group by [dtser]) as c
on b.dtser_lag = c.dtser join
(select a.dtser, cusip, (a.shares*price_u) as mkt_v from TableA_D join
( (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 ) as a
on TableA_D.dtser = a.dtser and TableA_D.axys_cusip = a.cusip) as d
on c.dtser = d.dtser and TableA_D.prior_cusip = d.cusip
group by TableA_D.dtser
February 9, 2004 at 8:05 am
Looks like something a codegenerator would barf out... =;o)
I would do it along theses lines....
Find out exactly what the purpose of this query is.
Rip it apart piece by piece and find alternate ways of achieveing the same result.
In order to do that you need initmate knowledge of the underlaying tables/data.
Loads of coffee
Even more loads of patience and persistance
Good luck.
=;o)
/Kenneth
February 9, 2004 at 12:10 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply