Best way to query data from database and then modify it

Question

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.

• JacquesB: What kind of application? Single or multi-user?
• 41686d6564: @JacquesB Single user. Just many connections per second.
• Stephen Byrne: Does the data have to be modified immediately upon receipt of the packet - for example could you queue up work to be done in a message queue, and then execute that "later". Or do you need to send something back for each packet received based on the work you do in the database?
• 41686d6564: @StephenByrne I need to send something back for each packet. That's the issue, I need -for each incoming packet- to be able to access the database, look for one row, get some items from it, decide if something needs to be modified or not, then reply to the sender based on the work done in the database.
• 41686d6564: @EsbenSkovPedersen They're all needed. Basically each query looks for a specific record which can be anywhere in the table.

Answer 1

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

• 41686d6564: Thanks. I didn't know that I can execute multiple commands within the same connection. Just pointing out 2 things: (1) I suppose you meant using(MySqlCommand cmd = con.CreateCommand()). (2) For anyone who will use parameterized queries, you'll have to clear the parameters using Command.Parameters.Clear() before using the new query.

Answer 2

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.

• 41686d6564: Thanks for the recommendations/hints. Doesn't seem to be exactly what I'm looking for, but I'll see about using classic ADO or DAO thingy. Using Recordset, yes that's what I used in previous VB6 applications. I didn't know it can be used in .Net, plus I need solution that can work for access as well as MySQL.
• Doc Brown: @GeniuSBraiN: to create a solution which works optimized on Access and MS SQL, create a general API layer for your DB access and implement it two times: one optimized MS Access implementation, and one optimized MySQL implementation. I am pretty sure you won't find a better solution.

Answer 3

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.

• 41686d6564: I don't want to ONLY modify the data. I need to be able to read data first (to use it in the software not just the db). Can this be done using store procedures?
• Doc Brown: Sorry, but this is very wrong advice for MS Access. It might be a good idea on MySql, however.
• Fütemire: I'm unfamiliar with MS Access, but for MySQL this is similar to what I do. I run parameterized SQL commands to stored procedures and functions that modify\return results. I chose this method mainly for the security benefits against SQL injection. Also aside from the obvious benefit of the code running on the server, it also means that I don't have to write similar functions in different languages when needing to access the same information or methods from a different technology.