Help w/ RealTime stock prices
Michael Ogrinz
I am interested in different approaches to tackle a particular problem
involving
real-time feeds from a security-pricing service.
The goal:
---------
A PowerBuilder program will query a table for 15-minute delayed stock
prices at the
user's request.
A currently suggested approach:
-------------------------------
A Sun box will obtain real-time ticker information for, say, 5000
issues. This information
will be stored in shared memory. Every 15 minutes, a second process will
take these records
and *insert* them into a table (real_time_price). Current thinking is
that
1> If the process receiving the prices had to write to the table as
well, it might get bogged down (hence process #2)
2> For speed, an insert (w/ a DateTime stamp) would be preferable to
doing an update (against all the issues that have changed - potentially
5000. The PowerBuilder query will request the most recent price.
Of course, updating every 15 minutes against (worst case) 5000 issues
all day would leave us with a
table of 160,000 records at the end of the day: Average record size is
about 32 bytes
Still, there is the problem of those users who want to query (or *are*
querying) the price information
while process #2 wants to update the table. So it has also been
suggested that a 3rd process perform
some 'switching'. Process #3 (in 3-card-monty style) would rename the
"old" price table (real_time_price)
to a different name (say, rtp_temp) - Even if the user was mid-query
(because the user's query is by this
point going against the underlying sybase id?) and then rename the most
recently updated price table (rtp_load) to the one the user's app
expects (real_time_price). Then rtp_temp is renamed to rtp_load. Process
#3 will truncate this table and load it with the most recent information
that process #2 put into its own table.
Whew!
Now, I have to assume there are other systems out there doing this, and
I have to say my programmer's
instincts tell me this is an overly complicated approach. The only hard
and fast requirement is that
the most recent prices need to be cached somehere, so that whenever the
user logs on we can give
them updated information (and no locks, either ;-) I would greatly
appreciate the feedback/suggestions
of my peers on this one.
Thanks,
Mike
Korrapolu Reddy
Michael Ogrinz wrote:
>
> I am interested in different approaches to tackle a particular problem
> involving
> real-time feeds from a security-pricing service.
>
> The goal:
> ---------
> A PowerBuilder program will query a table for 15-minute delayed stock
> prices at the
> user's request.
>
>
> Thanks,
>
> Mike
I implemented exactly the same problem with a lot more complexity in the
sence that it is realtime not 15 minute delay and does lot of other
stuff. It is in the production and doing very good job. It is not
possible to discuss the complete solution but I give the
main procdeure.
1.Have two threads.
2.Connect one with an asyncronous connection and the other one with
synchronous connection.
3.Insert or bulkcopy (using bulkcopy API) the feed into a table1 using
synchronous connection.
4.When you get a period of lull in the feed, using the same synchronous
connection copy into table2 and then invoke a stored procedure with
whatever logic using asynchronous connection. Since you don't have to
wait for its completion, go back immediately to the synchronous
connection and process feed into table1. Whenever there is some wait in
the feed go and check whether the stored procedure is done or not.
5.If done repeat step 4.
Hope this helps.
Korrapolu Reddy
Bridge Information Systems. Inc
314-567-8431
abo...@pacbell.net
Mike,
We have a system that does something very similar to this. Except we do not
doing any renaming of the table. We insert all records with a timestamp and
have a trigger to delete old records in case we receive updates for a
specific price date. We don't worry about someone running a query because
usually our tables only get queried at the end of the day.
Here is our trigger:
create trigger
docorr_p241 on p241_data
for insert as
begin
/*
This section handles resends, corrections or re-broadcast from the TIC.
*/
if (select count(*) from p241_data a, inserted b
where a.SYMBOL = b.SYMBOL
and a.price_date = b.price_date) > 1
begin
delete p241_data
from p241_data a, inserted b
where a.SYMBOL = b.SYMBOL
and a.price_date = b.price_date
and a.timestamp != b.timestamp
end
end
Michael Ogrinz wrote:
> I am interested in different approaches to tackle a particular problem
> involving
> real-time feeds from a security-pricing service.
> <cut>
> A currently suggested approach:
> -------------------------------
> A Sun box will obtain real-time ticker information for, say, 5000
> issues. This information
> will be stored in shared memory. Every 15 minutes, a second process will
> take these records
> and *insert* them into a table (real_time_price). Current thinking is
> that
>
> 1> If the process receiving the prices had to write to the table as
> well, it might get bogged down (hence process #2)
> 2> For speed, an insert (w/ a DateTime stamp) would be preferable to
> doing an update (against all the issues that have changed - potentially
> 5000. The PowerBuilder query will request the most recent price.
>
> <cut>
> Still, there is the problem of those users who want to query (or *are*
> querying) the price information
> while process #2 wants to update the table. So it has also been
> suggested that a 3rd process perform
> some 'switching'. Process #3 (in 3-card-monty style) would rename the
> "old" price table (real_time_price)
> to a different name (say, rtp_temp) - Even if the user was mid-query
> (because the user's query is by this
> point going against the underlying sybase id?) and then rename the most
> recently updated price table (rtp_load) to the one the user's app
> expects (real_time_price). Then rtp_temp is renamed to rtp_load. Process
> #3 will truncate this table and load it with the most recent information
> that process #2 put into its own table.
>
..
.