(dsdevonsomer@gmail.com) writes:
> Here is my scenario.
> I have 2 tables with more than 4 mil rows (1 - 2.8 Mil, 2 2.1 mil). Of
> these tables, there are about 25 ( select ) queries run to help
> generate reports every week.
>
> Also, every week, there will be some new data in both tables (about
> 25k in each tables). To improve select performance, based on few
> columns that are frequently used in those queries, I added Non-
> Clustered Indexes on these columns ( about 4 in each table ) on both
> tables.
>
> Now, for the first time, the performance was great.. And I am now on
> 2nd week, where I have to import new weekly data. I am debating as to
> disable and rebuild all index or just drop and recreate.
>
> I have tried drop & recreate, it takes about 1.5 hr to finish, which
> then defeats the performance improvement argument.

Did you try importing the new rows with the index present?

I would tend think that if you only import 25000 rows, that the
penalty you will get from having the indexes in place will not be
that severe.

Then again, I would not really expect it to take 1½ hour to drop and
rebuild four non-clustered indexes on a four-million rows table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx