How to set FILLFACTOR

I need some help on how to improve performance when loading tables.
We have a datawarehouse and sometimes it is needed that the entire
datawarehouse is refreshed. (like when test is refreshed from production)
The datawarehouse tables are refreshed by a set of sql statements. Take for
example there is a COMPANY table, the data is denormalized with the address,
and user information stored along with company information. In order to do
that, initiallly company information is populated and then address & user
information is updated to it. In this entire process, all indexes except
the clustered index is dropped at the beginning of the process and recreated
at the end of the data load. In order to improve the process, if I set the
FILLFACTOR to 10 or 20 on the clustered index, will it really help ? I
understand from the documentation that FILLFACTOR is used only when the
index is created, so if I loading data to an existing table, FILLFACTOR will
be of no use, correct ?
Is there anything else that I can do to imporve the performance of such
table loads. All loads are with first inserting core data and then updating
the related data.
The database is already set to SIMPLE mode.
-Nagsbest way:
delete all indexes, fill the table, recreate the indexes.
second option:
disable constraint validation, load the table, defrag your indexes after
loading the table
disabling the constraint like foreign key check etc... improove the
performance, but the index creation time degrade the performance. (but make
sure you load valid data in your table)
"Nags" <nags@.RemoveThishotmail.com> wrote in message
news:ufTYvko7EHA.2124@.TK2MSFTNGP14.phx.gbl...
>I need some help on how to improve performance when loading tables.
> We have a datawarehouse and sometimes it is needed that the entire
> datawarehouse is refreshed. (like when test is refreshed from production)
> The datawarehouse tables are refreshed by a set of sql statements. Take
> for
> example there is a COMPANY table, the data is denormalized with the
> address,
> and user information stored along with company information. In order to do
> that, initiallly company information is populated and then address & user
> information is updated to it. In this entire process, all indexes except
> the clustered index is dropped at the beginning of the process and
> recreated
> at the end of the data load. In order to improve the process, if I set
> the
> FILLFACTOR to 10 or 20 on the clustered index, will it really help ? I
> understand from the documentation that FILLFACTOR is used only when the
> index is created, so if I loading data to an existing table, FILLFACTOR
> will
> be of no use, correct ?
> Is there anything else that I can do to imporve the performance of such
> table loads. All loads are with first inserting core data and then
> updating
> the related data.
> The database is already set to SIMPLE mode.
> -Nags
>|||All the datawarehouse tables do not have any constraints. Only Indexes.
This is what we do :
Drop all indexes (except clustered indexes)
Load data
Recreate all indexes.
The total process itself take about 20 hours. Looking for options to
improve performance.
-Nags
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OnjM5Up7EHA.2540@.TK2MSFTNGP09.phx.gbl...
> best way:
> delete all indexes, fill the table, recreate the indexes.
> second option:
> disable constraint validation, load the table, defrag your indexes after
> loading the table
> disabling the constraint like foreign key check etc... improove the
> performance, but the index creation time degrade the performance. (but
make
> sure you load valid data in your table)
> "Nags" <nags@.RemoveThishotmail.com> wrote in message
> news:ufTYvko7EHA.2124@.TK2MSFTNGP14.phx.gbl...
production)[vbcol=seagreen]
do[vbcol=seagreen]
user[vbcol=seagreen]
except[vbcol=seagreen]
>|||Hi Nags
What utility do you use to load the data into the tables?
In order of load performance the follwing are the best utilities for SQL
2000
- BULK INSERT command
- DTS data pump with the correct settings
- BCP
Though you would expect BCP to perform very good you can gain better
performance by using the BULK INSERT command - it seems that this command
uses the netlib to bulk insert whereas BCP uses OLEDB
Yours sincerely
Thomas Kejser|||I use procedures to load the data and I use SQL Commands, like INSERT &
UPDATE statements.
-Nags
"Thomas Kejser" <thomas@.kejser.org> wrote in message
news:OMA0vDN8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi Nags
> What utility do you use to load the data into the tables?
> In order of load performance the follwing are the best utilities for SQL
> 2000
> - BULK INSERT command
> - DTS data pump with the correct settings
> - BCP
> Though you would expect BCP to perform very good you can gain better
> performance by using the BULK INSERT command - it seems that this command
> uses the netlib to bulk insert whereas BCP uses OLEDB
> Yours sincerely
> Thomas Kejser
>
>|||I fogot to mention. The source data is being brought thru replicated
tables.
-Nags
"Nags" <nags@.RemoveThishotmail.com> wrote in message
news:Ol09ZHa8EHA.2572@.tk2msftngp13.phx.gbl...
> I use procedures to load the data and I use SQL Commands, like INSERT &
> UPDATE statements.
> -Nags
> "Thomas Kejser" <thomas@.kejser.org> wrote in message
> news:OMA0vDN8EHA.824@.TK2MSFTNGP11.phx.gbl...
command[vbcol=seagreen]
>|||how many rows are inserted?
standard inserts ALWAYS use the log and this slow down your system. its not
recommended when you have to load a large amount of data.
the bulk inserts allow you to load your data by batch, for example by 50
000rows
another way to watch: your HD config! are you in Raid? (which one; how many
drives)
and how your HD buffer is configured?
have you a dedicated HD raid for your tempdb database?
your bottleneck can come another hardware limitation (not enough memory,
slow proc...)
"Nags" <nags@.RemoveThishotmail.com> wrote in message
news:Ol09ZHa8EHA.2572@.tk2msftngp13.phx.gbl...
>I use procedures to load the data and I use SQL Commands, like INSERT &
> UPDATE statements.
> -Nags
> "Thomas Kejser" <thomas@.kejser.org> wrote in message
> news:OMA0vDN8EHA.824@.TK2MSFTNGP11.phx.gbl...
>|||All the data comes from another set of tables. So, we do not use BULK
INSERT at all.
-Nags
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:#B9J2Cb8EHA.2876@.TK2MSFTNGP12.phx.gbl...
> how many rows are inserted?
> standard inserts ALWAYS use the log and this slow down your system. its
not
> recommended when you have to load a large amount of data.
> the bulk inserts allow you to load your data by batch, for example by 50
> 000rows
> another way to watch: your HD config! are you in Raid? (which one; how
many
> drives)
> and how your HD buffer is configured?
> have you a dedicated HD raid for your tempdb database?
> your bottleneck can come another hardware limitation (not enough memory,
> slow proc...)
> "Nags" <nags@.RemoveThishotmail.com> wrote in message
> news:Ol09ZHa8EHA.2572@.tk2msftngp13.phx.gbl...
SQL[vbcol=seagreen]
>|||Hi Nags
If you copy the data from another table using a SELECT INTO statement is a
VERY fast way to move data. try this:
SELECT col1, col2, ..., col1
INTO TargetTable
FROM SourceTable
Be aware that SELECT INTO create the TargetTable, so you must delete it
first if it already exists
Hope that helps
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"Nags" <nags@.RemoveThishotmail.com> wrote in message
news:eLve5Nb8EHA.2180@.TK2MSFTNGP12.phx.gbl...
> All the data comes from another set of tables. So, we do not use BULK
> INSERT at all.
> -Nags
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:#B9J2Cb8EHA.2876@.TK2MSFTNGP12.phx.gbl...
> not
> many
> SQL
>