Thomas,

My specific question is," if I have a process which creates a table and
inserts 90GB of data what is the best way to configure the database or
server to load it fastest?"

No indexes are used (that wasn't in the question).

I also need to do the same thing on an Oracle server but that has a lot
more granularity (block size, buffer size, rollback segments, etc.)
But I know this is not the Oracle list.

So...your answer was,"Set the fill facotr to the maximum." What is the
maximum. I guess I can look that up myself.

-Kevin
-----Original Message-----
From: thomas.[Email Address Removed] [mailto:thomas.[Email Address Removed] Tuesday, January 22, 2002 11:43 AM
To: [Email Address Removed] Sexton, Kevin
Subject: Re: fill factor



Kevin,

Fill factor is, IMO, related to updates and future inserts. If you have
a lot of updates to those null columns, and you can do update in place,
then you will probably want to leave room on the data pages for the
records to grow. Similarly, if there is a clustered index and you will
be doing inserts throughout the table, then you will probably want to
set the fill factor to leave room on the data pages.

One possibility for speeding it up is to not create indexes until after
the data is loaded. If you follow this strategy, create the clustered
index first and then the other indexes. If possible, create the other
indexes in parallel. You can use global temporary tables to track the
status of the creation of the indexes - much as you would if you had
separate processes and used shared memory to synchronize them - but if
you do, you will be getting complex enough that others may have
difficulty following the logic.

My suggestion would be that if you have the option, do not have any
triggers or indexes on the created table, bulk insert into it to avoid
the overhead of a transaction log, create the indexes and RI constraints
and triggers after the data is loaded. Bulk insert is faster than bcp
which is faster than insert. Data set operations are faster than cursor
operations except in some less common cases where you need to do special
calculations on the data.

If you can give more details, it would be easier to make concrete
suggestions.

. . . Tom

Tom Zeblisky
Reuters

Kevin Wrote:

Fellow DBA's,
I have a process that runs and creates a 90GB table. (Assume I don't use
cursors)

Should I set the fillfactor for the database to it's maximum extent?
Is the fill factor called into play for the batch transactions or is it
applied for every row inserted?
Does it change the answer if I have a lot of nulls in my table?
What if I was using a cursor? Does that change the answer?

Are there any other settings I could change to increase the speed of the
process?

Thanks!

KEVIN.[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.

---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page