Dropping/Creating indexes

Okay, I have a program that does mass importation of data once a month. By "mass importation", I mean 10-15 million records, easily. I have several indexes on that table, but keeping the indexes there during import slows it down to a quarter of what it takes without the indexes.

Currently I have code in the program to drop the indexes, perform the import, and re-create the indexes... which indexes are dropped/created are hard-coded.

The problem is that we've added several new indexes to the tables and will likely add more in coming months. I CAN just go in and add the new indexes to the list of things to be dropped and created, but I hate having to do that every time we add or delete an index.

So. Is there a way from inside my program to find out what indexes exist on my target tables (there are 3) and generate create/drop scripts that I can run at the proper time against the database? That way the whole process is automated.

I am running Visual Studio 2008 and Sql-Server 2008.

Thanks in advance.

Have you looked at this:

Code:

---

sp_MShelpindex N'tableNameHere', null, 1;

---

That actually gives me the information I need in a compact, easy to parse format. From that I can easily generate drop and create statements for each index.

Of course, it'd be easier if there was an automated way to do THAT, too... (grin)

Thanks... this helps immensely.

Quote:

---

Currently I have code in the program to drop the indexes, perform the import, and re-create the indexes... which indexes are dropped/created are hard-coded.

---

There is no black and white answer. There are several factors in how you should approach this problem/situation for the most efficient process.

How do you import the data? Bulk Copy? Bulk Insert? If you are using a Bulk method, change the recovery model to Bulk-Logged beforehand. Also, experiment with different Batch sizes.

When does the import occur - at night or while users are still accessing the system.

Regardless, the Alter Index statement is more efficient than dropping/recreating indexes. A single statment has the capability to affect all Indexes. Indexes can simply be Disabled and then rebuilt after the import.

Any Clustered indexes? Disk Space requirements to build a Clustered Index are huge. Disabling and Rebuilding with Alter Index uses significantly less disk space than a Create Index statement.