Best way to index a mostly static database

[trd79]

Hi,

I am extracting data from an operational system in order to maintain a mirror that can be used for reporting.

I have two means of filling this database:

1) A stored procedure which truncates all tables and rebuilds everything from scratch. This takes about an hour and will be run periodically.

2) A stored procedure that updates my database with the previous days activity which will be run daily. This will DELETE and INSERT but not UPDATE records.

All other interactions with the database are SELECTS.

First off, I am not currently using primary keys in this database as the data, once imported is static.
All rows SHOULD be unique, but I cannot guarantee this and do not want to risk my import failing due to any such constraints.

What sort of index should I be using here and what is the best way of maintaining it? For instance, when doing a complete rebuild, can I leave rebuilding any index until after all the data has been imported?

Hope the above makes sense. I have done a fair bit of searching on index types but it seems to get very complex very quickly!

[ck9663]

What's wrong with implementing number 1?

If your main database gets updated, and you only do delete and insert on your mirror, you'll have data inconsistency?

If you truncate the table, all indexes are still there and will be used as you update the tables. You can either drop the index or drop the table and rebuild them later.

Either way, you need to rebuild your index to make sure the optimizer know the statistics of your table.

Good luck!!

--- CK