What is the best practice for implementing a data table with many inserts, deletes and reads?
I am creating a functionality where our wcf services log all the changes that are stored through them and the changes need to be sent to other systems.
After each call to the service with changes, we save the changes to the table (changes are serialized). We regularly have biztalk to pull changes from the table and delete the one that was pulled.
This means that under high load the number of inserts and deletions is high, and we are struggling with the timing out because it is blocked by inserts.
I've tried playing with different isolation levels but haven't found anything that works so far.
We use ado.net and sql server 2005 for this.
What is the best practice to implement a data table with many inserts, deletes and reads when using SQL Server 2005 and ado.net.
Edited: Our problem with our solution today is that all ongoing inserts stop all reads from the table. Probably because if there is an indexed index scan that I don't see at the moment there is a good way to delete.
a source to share
On the DB side:
- Try using a lock hint called READPAST, which skips rows locked by other transactions. See MSDN for details .
- The Sql server will sometimes increase row-level to page-level or table-level locks if it thinks it is more economical. You can enforce row-level locks with the ROWLOCK hint.
- Ditch unnecessary indexes because they slow down inserts, updates, and deletes, and can also cause index data locking problems.
On the C # code side:
- Use the best practice to "acquire resources as long as possible and release them as soon as possible." Open your ADO.NET connection, run sql command and close your connection before doing any tedious operations on the results. You don't need to worry about connection pooling, this is done automatically if you use the same connection string all the time.
a source to share
This is what your DBA should do. Are you a database administrator? If so, you will need to check some benchmarks to see what the bottleneck is. This article should help as an introduction to performance tuning: http://www.devx.com/getHelpOn/Article/8202/
a source to share
If the semantics of your application permit, consider making inserts into a separate "helper table" and periodically applying them to the "master table" in a single large transaction; deletions can be handled in a similar way (where you are now doing the delete, instead inserting into a separate auxiliary table a record identifying the main table record to delete, and periodically doing a bunch of deletes from the main table in one big transaction).
Of course, if you do this, the master table will not display the "most recent state", but "the state is a few minutes [or some time units] ago", so I say "if the semantics allows it." It is sometimes a good thing that many SELECTs rely on "state as of last update" (that is, what's in the master table) and a few SELECTs that really should reflect the latest updates instantly, which can be satisfied (maybe a little slower) with more complex queries using both primary and secondary tables (via UNION, union, or whatever, depending on the semantic details you need to implement).
a source to share
You mentioned that you've tried different isolation levels, but have you tried snapshot isolation?
Snapshot isolation basically makes a copy of the row before insert / update, and any query that tries to fetch that row gets the old copy until the insert is complete. This means that you sometimes get slightly older data, but the selection will not block during insert.
The downside to this is that your inserts / updates will take longer, due to the need to take a copy. I'm not sure how many fines he incurs, though.
a source to share