I am seeing a performance issue related to schema upgrades.  This is from a C++ program that uses the MAPI library.

We try and keep 30 days worth of data, imported at a fairly high data rate with lots of columns.  We use multiple threads to ingest data (bulk insert), and the number of threads is stored in a configuration table.

For 20 days worth of data, this works out to around 9000 tables (plus merge tables).  We have roughly 6 sets of tables per hour, each of which has 4 tables and a merge table connecting them.

Because this is a schema change, we wrap the entire upgrade in a transaction.

For the purpose of the test, we had no data in the tables, but only had empty tables.

If we change our setting from 4 parallel tables to 2, we end up dropping 9000 tables.  About half of the “drop table” commands take 0 seconds.  The rest take anywhere between 4 and 12 seconds each.

Here is a plot of the time per delete, y access is seconds, x is the statement.  The X access is NOT time based:

Any idea on how we can reduce the time?  The total time to delete 9000 tables was 480 minutes.

Thanks,

Dave