|
|
Posted by shelleybobelly on 07/18/06 22:13
Right, I agree. If I run it the other way (just update the big lookup
table from the separate tables where there were changes), it runs much
quicker, only deleting/adding a thousand or so records a day. The
owners of this DB are doing things differently, but I guess I'll just
have to 'educate' them on this one. Then I'll create another job to
re-index this table about once a week or so to keep it from
fragmenting.
BTW, this is for a Legal department that has to look up files that can
be up to 20 years old, but they don't have all the information. That's
why the 3 million records in a lookup table. It is a subset of a HUGE
archive table that contains 255 columns, too.
Thanks Erland, keep up your postings.
Erland Sommarskog wrote:
> 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
[Back to original message]
|