System.Data.SQLite

Hi,

 I have testing sqlite with a table of 300 hundreds records only, at the moment.

All the data in the records are renewed at about 50ms interval.

Reading and updating the database, both at approximately 50ms interval.

I am getting about 40% CPU time. Is this normal for sqlite given the scenario above? This high CPU time doesn;t seem to be acceptable to ne at the moment. How to reduce it?

//this is the updating part 

try

 using (DbDataReader reader = MyCmd .ExecuteReader())
    {
        while (reader.Read())
        {
            string str =  reader.GetString(2);

              int x = rand.Next(100);

From my little experience, I would make all the updates in one transaction. Once I used this approach, speed of multiple inserts raised several times.

Exactly.  SQLite's ACID compliance is biting you here.  Every time you issue a singular update or insert outside a transaction, an implicit transaction has to be constructed and destructed around your statement.  Furthermore, SQLite must make sure the HD has completely flushed the data to disk before finishing the transaction.

Bulk up your updates/inserts in a transaction as best you can to increase performance.

An other thing to do to raise performance would be to use a parameterized query.  SQLite is having to recompile your sql statement for every update.

g_sqlCmd.CommandText = String.Format("UPDATE MyTable SET Reading = {0:d} WHERE ID = '{1:d}'", x, y--);

1.  As I said before, SQLite has to create an implicit transaction, perform your update, wait for the harddrive platters to fully commit the data via flushing the disk buffers, and tear down the transaction every 30ms.  This is a lot of work.  As a matter of fact, in 30ms using a single transaction, SQLite could probably write 10,000 updates to the same database.  Building the transaction and flushing the disk buffers consumes the vast majority of the effort.

2.  How about revisiting this 30ms update thing?  Do you really have to update that much?  Can't you spin off a separate thread and build the updates up into a cache and when the cache exceeds a certain threshold, flush the cache to the database via separate thread?  Doing this in a single transaction would massively increase performance.

3.  Use parameterized queries.  This won't solve the problem alone, but it will help.

I need to have the database updated every approx 30ms, as some other process is also reading this database at roughly 30 to 50ms interval in order to have the updated readings as real time as possible.

Since this involves across processes access of DB, and every 30ms, i am getting new readings for all my database records; Building up the updates in cache before flushing the data to the DB  would mean i need to skip a few rounds of updates...

I have also tried parameterized queries..

Putting my DB on RAM disk, will it help? What other methods i can use to get around the massive time spent on building the transaction and flushing buffer?

It sounds like you need to post some more information about your architecture then.  Anytime I deal in realtime communications like this, I tend not to use a file-based communications method.