I should preface this by saying I'm new to the whole data scheme in .NET,
having in the past only worked with direct SQL queries. And I would still be
working with said queries if this were not already a complete application
using the "correct" data access methods. :)
Basically, our situation is this. Every second, our equipment logs a piece
of a "strip" of data, 32 records per second. Obviously in a strip that is
about 3 minutes long, this turns into a ton of records. Each second, we store
those 32 records into a DataTable. At the end of each strip, we are calling
our SQLDataAdapter object from the main form, and calling the
..Update(TheDataTable) method. The only problem is that these 5000 or so
records are taking like a full 3 minutes to store in the database, almost as
much time as it took to gather the data! My only guess is that it is
performing a separate insert query for each DataRow rather than automatically
seeing the opportunity for a Bulk Insert, and I see no way to force it into a
bulk mode. And really, doing manual inserts each second is not the way
either, because if the power goes off during a strip, we simply lose the
whole strip, rather than having data records in a corrupted or unknown state,
which is the preference here. So we do prefer to insert at the close of each
strip.
So, the obvious first question, have we approached it wrong, and are we
doing something incorrectly?
If that seems like the correct way, are there any optimization tips that can
improve the speed of insert?
Or any other ideas?
