I've answered enough, so I get to ask one. 
I've written a little module that will accept a table name, read the indexes on that table and drop those values into a temporary table, remove the indexes, perform a lot of data manipulation (updates, appends, deletes, etc.), and then put the indexes back on the table. Visually, it's this:
Read Indexes -> Temporarily Store Indexes -> Drop Indexes from Table -> Manipulate Table -> Restore Indexes to Table
The reason you would want to do this, in case you're wondering, is that if you are working with massive amounts of data and you have a lot of indexes on the table you are working with, they can slow down the process exponentially.
As a real world example, appending ~700K rows to a table that already had ~1.5M rows took 2 1/2 hours because that table had several indexes on it. If you manually remove the indexes and perform the same append, the process takes less than a minute. (I'll explain why if anyone wants.)
I need to get it automated because different tables have different values indexed and those indexes can change over time, so a static "Table of Indexes" will not suffice.
I can drop indexes using DDL:
DROP INDEX IndexName ON TableName
I can add indexes using DDL:
CREATE INDEX IndexName ON TableName (IndexField1, IndexField2, etc.)
I can read existing indexes using VBA:
CurrentDb.TableDefs("TableName").Indexes("IndexName").Fields
(This is pseudo-coded as I iterate through to get all table names, all index names, all field names, etc. and write that to a table on the fly.)
The only issue I have is that I can't seem to access the IndexField's Sort Order property. An index can be sorted ascending or descending, but I can't figure out how to read the sort order on an existing index field. There must be a method or property I'm missing, and hopefully, someone knows just what that is.
I'll post a picture of exactly what I'm talking about if that clears it up any.
Thanks.
I've written a little module that will accept a table name, read the indexes on that table and drop those values into a temporary table, remove the indexes, perform a lot of data manipulation (updates, appends, deletes, etc.), and then put the indexes back on the table. Visually, it's this:
Read Indexes -> Temporarily Store Indexes -> Drop Indexes from Table -> Manipulate Table -> Restore Indexes to Table
The reason you would want to do this, in case you're wondering, is that if you are working with massive amounts of data and you have a lot of indexes on the table you are working with, they can slow down the process exponentially.
As a real world example, appending ~700K rows to a table that already had ~1.5M rows took 2 1/2 hours because that table had several indexes on it. If you manually remove the indexes and perform the same append, the process takes less than a minute. (I'll explain why if anyone wants.)
I need to get it automated because different tables have different values indexed and those indexes can change over time, so a static "Table of Indexes" will not suffice.
I can drop indexes using DDL:
DROP INDEX IndexName ON TableName
I can add indexes using DDL:
CREATE INDEX IndexName ON TableName (IndexField1, IndexField2, etc.)
I can read existing indexes using VBA:
CurrentDb.TableDefs("TableName").Indexes("IndexName").Fields
(This is pseudo-coded as I iterate through to get all table names, all index names, all field names, etc. and write that to a table on the fly.)
The only issue I have is that I can't seem to access the IndexField's Sort Order property. An index can be sorted ascending or descending, but I can't figure out how to read the sort order on an existing index field. There must be a method or property I'm missing, and hopefully, someone knows just what that is.
I'll post a picture of exactly what I'm talking about if that clears it up any.
Thanks.
