|
|
Posted by Erland Sommarskog on 07/18/06 21:18
shelleybobelly (shelleybobelly@yahoo.com) writes:
> I have a new job. It needs to drop and re-create (by insert) a table
> every night. The table contains approximately 3,000,000 (and growing)
> records. The insert is fine, runs in 2 minutes. The problem is that
> when I create the indexes on the table, it is taking 15-20 minutes.
> There is one clustered index and 11 non-clustered. This is a lookup
> table that takes many different paremeters, so it really needs the
> indexes for the user interface to run efficiently. However, the
> database owners aren't keen on a job taking 20 minutes to run every
> night.
Without knowing much about the data, it's difficult to tell. But I find it
difficult to believe that data changes that much in a lookup table. Then
again, I would not expect a look-up table to have three million rows.
Anyway, rather than dropping and recreating, maybe it's more effective
to load into staging table, and then update changed rows, insert new
ones, and delete old ones.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|