How to update thousands of rows in an MS-Access query, whose values are received using "Count" and whose unique condition is contained within an array

Question

I have a book table with the columns ISBN and Inventory. After removing a row or thousands of rows from this table to avoid user error, I automatically calculate the Inventory of that book based on its ISBN.

I use the following code to do this, but it takes a long time when the user deletes hundreds of rows and if there are thousands of unique ISBNs.

string[] UniqueISBN = ISBN_Value.Distinct().ToArray();
OleDbCommand OleDbCommand_Update = new OleDbCommand();
OleDbCommand_Update.Connection = OleDbConnect;
OleDbConnect.Open();
for (int i = 0; i < UniqueISBN.Length; i++)
{
     OleDbCommand_Update.CommandText = string.Join(null, "Select Count(*) From BookTable Where ISBN = '", UniqueISBN[i], "'");
     OleDbCommand_Update.CommandText = string.Join(null, "Update BookTable Set Inventory = ", (int)OleDbCommand_Update.ExecuteScalar(), " Where ISBN = '", UniqueISBN[i], "'");
     OleDbCommand_Update.ExecuteNonQuery();
}
OleDbConnect.Close();

To answer this question, the following information may be required:

The array's length can range from 1 to 250 thousand, and each ISBN[i] has 13 digits and is stored in the database as a string type field.

In the following GIF image , the inventory of ISBN 0000000000000 is 3 and the inventory of ISBN 2222222222222 is 4.

As you can see, when the two ISBNs 00000000000000 are removed, the inventory of the ISBN 00000000000000 changes automatically.

Unfortunately, when the number of unique ISBNs increases (for example, to 4,000 unique ISBNs), this solution becomes very slow.

I believe there is a quicker alternative, similar to the following code.

OleDbCommand_Update.CommandText = "Update BookTable Set Inventory = (Select Distinct Count(*) From BookTable Group By ISBN) Where ISBN IN (Select Distinct ISBN From BookTable Group By ISBN)";

I use the following tools:

.NET Framework 4.5.2, WPF, MS-Access 2007

Thanks.

Answer 1

I would think the count() going to run as fast, or better then a sub query. We assuming there is a index on column ISBN in both tables?

And how big is your array that drives this loop? What you might try is to have one update statement and use a ISBN IN (34,343,343) (this would work only if the array list is say less then 30 values - but you could try having just ONE sql update with a sub query for the count value).

so, if the array is less then 30 values, then you could say try somthing like:

Update BookTable Set Inventory = 
    (select count(*) FROM BOOKTable WHERE BOOKTable.ISBN = Inventory.ISBN)
 where BookTable.ISBN in (34,343443,343,343)

So, you would convert the array to a "," list, and use that in place of the above hard coded list.

So, one BIG cost?

Each time you execute a full seperate sql command (.Execute of oleDB command object), you have a big cost overhead.

However, if array list is small - say 5-10 values, then you not see a whole lot of performance improvment. So, critical in this suggestion is how big is the array?

With the above suggested approach, we could chunk, or run the update in batches of say 30. I can't say this would speed things up, but worth a try.

I would consider using a debug.print (or console.writeline) in that loop, and see how long each of the count() + update query takes.

But, as noted, check for index on ISBN in both tables.