MySQL permits an insert to insert multiple records:
INSERT INTO querty(field1, field2, field3) VALUES
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' '),
(null, ' ', ' ');
If you're smart you'll use a separate table for each ticker - provides for more efficient storage utilization and faster inserts. but don't use the ticker name as the table name - remember that both the name and the ISIN number is subject to change - something you usually need to keep track of.
Using separate tables for each security will also allow you to split the load between several computers.
I guess you need at least the following data in each table
CREATE TABLE
(
BidId Integer,
OfferId Integer,
TradeType Integer,
Price decimal,
Volume decimal
)
Well Donald - it's getting interesting, you're right, it's a lot of data, and if you are doing it right it's an astounding amount of data - good luck :)
If you're not keeping track of bids and offers, their sizes and conditions, you are loosing information that is often valuable to traders. Personally I'd like the whole order book for analysis since this may be used to either implement or counter automatic trading systems, or detect other interesting market conditions.
Update
The
System.IO.Log[
^] namespace defines an interface for logging to a record-oriented sequential I/O system. Using the classes from this namespace, you can implement your own transaction processing system using the Common Log File System (CLFS) provided by ws2003r2 and Windows Vista.
Another simple alternative is to use
H2Sharp[
^] with
h2[
^] - as I think you will find the
performaance[
^] more to your liking. Use memory tables for high perfomance - and use MSMQ to enque information to another process that will persist the information in the rdbms of your choice (MySQL).
I've used a similar approach, implemented in java, and it works beautifully. Running H2 as an embedded engine allows you to quite easily deal with 50 000 statements per second - so I guess performance will not be an issue. In my solution external .Net processes was able to use the Postgesql ODBC driver to communicate with the embedded server.
BTW: Serialization in .Net can be a real performance killer -
but here is a very good solution[
^]
Use binary formatter with MSMQ and use FastSerializer, or roll your own, to pass the information as byte[] to MSMQ.
If you are not comfortable with H2, there is always
Oracle TimesTen[
^] - but I've got no idea about the performance or price - but if it isn't way faster than the Oracle RDBMS it's kind of pointless :)
Update 2
Microsoft Message Queuing – Log Trade information using Microsoft SQL Server[
^] - you may find this interesting, given reasonable hardware it should solve your problems :)
Regards
Espen Harlinn