Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Propagating Data Fowards Through Time Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 4:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 71, Visits: 239
Hi SSC,

I have a table containing prices for certain stock prices, and dates upon which their shares outstanding changed. I need to be able to take the dates upon which the Shares Outstanding changed, and propagate that value forwards in time until the next observed change, then propagate that one. The end result should be that for every data point (except those prior to the first observable change in Shares Outstanding) each record should have a value for SharesOutstanding on each date. This is how I've written it right now, but the universe is about 5 million data points, and this query performs very slowly. Does anyone know of a more efficient way to perform this sort of operation?

set nocount on
set statistics io off
--Setting up the mock universe
if object_id('tempdb.dbo.#Stocks') is not null drop table #Stocks
create table #Stocks
(
FundID varchar(10),
TradingDate int, --we use MSDates
SharesOutstanding int,
SharesOutstandingChangeDate int

primary key clustered (FundID, TradingDate)
)

declare
@loop int,
@Price float

select @loop = 39000

while @Loop <= 40000
begin

insert into #Stocks (FundID, TradingDate)
select 'AAA', @loop union all
select 'BBB', @loop

select @Loop = @Loop + 1

end

update #Stocks
set
SharesOutstanding = case FundID when 'AAA' then case TradingDate when 39002 then 40000
when 39761 then 80000
when 39987 then 70000
end
when 'BBB' then case TradingDate when 39127 then 125
when 39493 then 305
end
end,
SharesOutstandingChangeDate = case FundID when 'AAA' then case when TradingDate in (39002, 39761, 39987) then TradingDate
else null
end
when 'BBB' then case when TradingDate in (39127, 39493) then TradingDate
else null
end
end

--this is the statement I'm really looking to improve upon.
set statistics io on
;with Propagate as (select
TradingDate = b.TradingDate,
SharesOutstanding = a.SharesOutstanding,
AsOfDate = b.AsOfDate
from #Stocks a
inner join (select
TradingDate = s2.TradingDate,
AsOfDate = max(s1.SharesOutstandingChangeDate)
from #Stocks s1
inner join #Stocks s2
on s1.SharesOutstandingChangeDate <= s2.TradingDate
group by s2.TradingDate) b
on a.TradingDate = b.AsOfDate)
update s
set SharesOutstanding = p.SharesOutstanding
from #Stocks s
inner join Propagate p
on s.TradingDate = p.TradingDate

Thanks!


Executive Junior Cowboy Developer, Esq.
Post #1370098
Posted Monday, October 8, 2012 6:36 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:42 PM
Points: 951, Visits: 2,880
Hi Gabe

Not sure if this is the logic you want to apply, but you may want to add join clauses for the FundID. Should improve it considerably. I have bolded the changes I made

;with Propagate as (
select
TradingDate = b.TradingDate,
SharesOutstanding = a.SharesOutstanding,
AsOfDate = b.AsOfDate,
FundID = a.FundID
from #Stocks a
inner join (
select
FundID = s2.FundID,
TradingDate = s2.TradingDate,
AsOfDate = max(s1.SharesOutstandingChangeDate)
from #Stocks s1
inner join #Stocks s2 on s1.SharesOutstandingChangeDate <= s2.TradingDate
and s1.FundID = s2.FundID
group by s2.FundID, s2.TradingDate
) b on a.TradingDate = b.AsOfDate
and a.FundID = b.FundID
)
--update s
--set SharesOutstanding = p.SharesOutstanding
select *
from #Stocks s
inner join Propagate p
on s.TradingDate = p.TradingDate
and s.FundID = p.FundID

Post #1370121
Posted Monday, October 8, 2012 7:11 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 3,610, Visits: 5,223
Gabe,

This sounds exactly like the problem posted here (just recently): http://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx

You'll find that the SSC team of volunteers got into a bit of a performance contest over the best way to approach it. Perhaps you'll find some tips in there.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1370126
Posted Monday, October 8, 2012 7:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 PM
Points: 71, Visits: 239
Thanks guys, those look like good places to start. I'll check them out!

Executive Junior Cowboy Developer, Esq.
Post #1370127
Posted Tuesday, October 9, 2012 2:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 440, Visits: 3,277
Seems like a very unwieldy approach. Why can't you just store the starting and ending dates for each price point?

David
Post #1370583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse