Dropping/Creating indexes

[HongKongCV]

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.

[GaryMazzone]

Have you looked at this:

sp_MShelpindex N'tableNameHere', null, 1;

[HongKongCV]

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.

[brucevde]

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.