Best way to query data from database and then modify it

I’m working on a software using VB.Net which retrieves string packets through a TCP socket. The problem is it receives hundreds of packets per second. For each incoming packet the software should connect to the database (right now it’s Access DB) and do one of the following:

Query one row of the database and use some item’s of it (no problem with this).
Query one row, read some items, and then modify items in the same row.
Query one row, read some items, and then modify items in another row.

Now obviously this need to be done as fast as possible, so I’m looking for a good way (using less resources as well) to handle this.

The only way I know to query & modify data in the same connection is to use disconnected mode (method #3) but I’m thinking that if I use disconnected mode to handle updating the data, that might cause some conflicts.

Here’s what I thought of anyway:

Method #1 (Mix between connected & disconnected mode):

Create a function that uses DataAdapter and returns a variable of type DataRow contains the data of the found row. And then modify the row directly in the DB by using UPDATE.. SET statement and ExecuteNonQuery method.

Method #2 (All connected):

Create a function that uses DataReader and returns required items from the found row. And then modify the row directly in the DB by using UPDATE.. SET statement and ExecuteNonQuery method.

Method #3 (All disconnected):

Query and fill the found row into a DataTable using DataAdapter, and then update & save the row using CommandBuilder and DataAdapter.Update

So, I’m looking the best approach to handle this. Please offer other approaches if you think it’s better than the 3 methods I mentioned.

Note: I’m using an Access database right now for comparability issues, and I’m planning to use MySQL in the future.

If you going to query and update then I would go with Reader. It is a little more efficient and it sounds like you do not need any DataTable features.

On Reader use ordinal (not column name) for a little faster reference.

Connections are pooled so open and close a connection is fast.

MySqlConnection con = new MySqlConnection(.... 
try 
{
     con.Open();
     using(MySqlCommand cmd = con.CreateCommand())
     {
         Int    data1;
         String data2;
         cmd.CommandText = .....;  //or better parameterized query
         using(MySqlReader rdr = cmd.ExecuteReader())
         {
            data2 = rdr.GetInt(0); 
            data2 = rdr.GetString(1);
         }
         // rdr should be closed so you can reuse the cmd  
         // do any cacls you need 
         cmd.CommandText = "update table ....." + data;  //or better parameterized query
         cmd.ExecuteNonQuery();
     }
}
catch(SQLexception Ex) 
{
}
finally
{
    if(con.IsOpen)   // not the actual syntax - look it up
       con.Close();
}

If you need to worry about data changed between the read and the write then you could need a transaction

And you should use parameterized query / update unless it is data under your control that can clean of injections

Also StoredProduces could be a little faster. But if you are accessing a row by a PK direct to the table is going to be pretty fast.

If the query is not by the PK then retrieve the PK and use that in the update

When it comes to DB performance, the best solutions are extremely dependend from the DB system you are using. I can give you some hints how to optimize your case for MS Access, for MS SQL server you will probably need a completely different approach.

1. For single user access, keep the database file locally

If you want to keep your db file on a network drive for nightly server backups, but don’t have the requirement to access the database by a second user at the same time, just copy it over from a network folder before the processing starts, do the processing and copy it back afterwards. That releases your program from any network overhead.

2. Do not use ADO.DB. Use classic ADO or DAO!

This may sound very old school and outdated, but ADO and DAO (which are fully available in VB.NET as COM components on every modern Windows system) can be up to 20 times faster for batch read & update scenarios on Access than ADO.DB, due to the additional abstraction overhead. That is at least what I have measured when creating such kind of applications. Moreover, these older APIs contain the concept of a recordset (which works similar to the concept of a cursor in other SQL databases), but is AFAIK not supported by ADO.DB. Recordset will allow you to navigate to a certain record, read it, modify it and write it back within the same connection and “transaction” (whatever the latter means in MS Access).

Some general hints (besides the usual “measure where your real bottleneck is”):

As a general rule of thumb, which is also valid for different databases: for your kind of scenario, the more lightweight the storage is, the faster your program can be. The fastest approach is often what you can do in-memory, the second fastest are flat files, then come lightweight DB solutions like MS Access or SQLite, and finally (often slower by an order of magnitude) heavy-weight client/server databases with full transaction management.

Of course, this is only a very rough guideline – the final performance you get will depend on the gory details of your implementation, and the available hardware & network.

Consider writing the logic to do comparisons and updates inside stored procedures in your database. You can then invoke the stored procedures using SqlCommand.

If you’re careful about how you structure the queries in your stored procedures it has almost no overhead compared with executing multiple queries from within an application, but saves you the latency of multiple queries.

The only downside is that you’ll need to redo a lot of your database logic when you migrate to a new DBMS.

Filed under: softwareengineering - @ 12:59

Thẻ: .net, c++, database, sql, vb.net

Thiết kế website giá rẻ

Danh mục

Best way to query data from database and then modify it