Disable and Rebuild or Drop and recreate Index

Hello all,
I am new to managing indexes on large tables and need some help.
Hopefully, I am not repeating question here. I searched as much as I
can, but not finding relatively best answer..

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.

Can someone please share their expert knowledge/experience about the
best way to use indexes in this scenario?

Many thanks,
JB
.

Relevant Pages

  • Re: Lost all joins
    ... Plus all the fieldname in the field pane of the query design have ... >> I made a copy of a database which is made up of a front and back end. ... But when i checked the queries all joins between ... >> are gone and i have to recreate all those links for the queries to run. ...
    (microsoft.public.access.queries)
  • Re: Text Box in Form vs. User-Defined Criteria
    ... try to recreate the form by renaming it and then making a copy of it with the ... the date in that text box and pull the date from there in my query as ... took only 20 seconds to run with the date entered in the pop-up. ... queries on the same form have this problem but I don't have this ...
    (microsoft.public.access.queries)
  • Key Violations
    ... I am having some difficulty with a series of update queries I am trying ... sites (and then values which relate to each scenario at each site). ... I am trying to create an input form for the site data which will then ...
    (microsoft.public.access.forms)
  • Re: assigning DB-user to server role
    ... Perhaps if you used profiler while you recreate your scenario in EM (create ... the roles, grant permissions to the role, add users to role, try to execute ...
    (microsoft.public.sqlserver.security)
  • Re: Disable and Rebuild or Drop and recreate Index
    ... columns that are frequently used in those queries, ... disable and rebuild all index or just drop and recreate. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)