Hi,
I'm building a multithreaded app that's using the producer consumer queue threading logic...I'm pushing the feed data into a queue which a worker thread processes (inserts and updates database tables). The problem is towards the end of the trading session the volume becomes VERY heavy and the queue builds at a faster rate than the worker thread can process causing latency with the inserts by 2 or 3 mins!

Does anyone have any solutions or suggestions on how to flush the entire queue into the table vs. individual inserts or some other way to resolve this?

By the way, I'm using MySQL 5.5 with .net connector (API) and C# 4.0

Thanks,
D.Allen
Edited by d_allen on Feb 25, 2011 at 04:06 PM

This is a bit generic,
but when writing to a DB the only way I know of to make things faster is to use transactions and commit every 1000 or 10,000 or so rows, this is how its done for ETL jobs.

If you are using mysql you may also look into using their multi-insert insert into syntax
>> INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
(copied from http://dev.mysql.com/doc/refman/5.5/en/insert.html )

Also consider if possible dropping any primary keys and indexes constraints before the insert and building them afterward