I want to increase performance on a fact table load.
The table has only a pk defined on an identity column.

Since it would help performance to drop all indexes
and then load a fact table. Is there anyway I can drop
the clustered index that is automatically created
on the identity column and then recreate it.

Would this help performance is the bottom line.

Inserting into an empty heap table is generally the fastest You'll need to
write a "post load" routine to add the indexes back in -

the approach I find most robust and high performance is as follows:

drop NC indexes
drop clustered index
load the data using a bulk api (bcp, bulk-insert, DTS bulk, etc)
create the clustered index
defrag clustered index <-- non intuitive, but optimal in many cases
create the PK (if PK is not clustered)
create other constraints
create the NC indexes
create statistics
dbcc updateusage

Before automating all this you'll obviously need to record the existing
indexes, constraints and statistics

----------------------------------------------------
The views expressed here are my own
and not of my employer.
----------------------------------------------------
"Phil" <atec396@hotmail.com> wrote in message
news:03ab01c3554f$3b3767d0$a401280a@phx.gbl...
> I want to increase performance on a fact table load.
> The table has only a pk defined on an identity column.
>
> Since it would help performance to drop all indexes
> and then load a fact table. Is there anyway I can drop
> the clustered index that is automatically created
> on the identity column and then recreate it.
>
> Would this help performance is the bottom line.

you mean insert in order by clustered index order, right? (PK not
necessarily clustered...)


--
----------------------------------------------------
The views expressed here are my own
and not of my employer.
----------------------------------------------------
"Ray Higdon" <rayhigdon@higdonconsulting.com> wrote in message
news:eo6EBEXVDHA.1280@tk2msftngp13.phx.gbl...
> If you have a CL index on the primary key, it will improve performance
> to import new data with an "order by primary key" statement.
>
> HTH
>
> Ray Higdon MCSE, MCDBA, CCNA
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

correction, first step should be drop constraints, and create constraints
and PK should be "WITH CHECK"
--
----------------------------------------------------
The views expressed here are my own
and not of my employer.
----------------------------------------------------
"Kevin" <ReplyTo@Newsgroups.only> wrote in message
news:#xA#VreVDHA.2032@TK2MSFTNGP11.phx.gbl...
> Inserting into an empty heap table is generally the fastest You'll need
to
> write a "post load" routine to add the indexes back in -
>
> the approach I find most robust and high performance is as follows:
>
> drop NC indexes
> drop clustered index
> load the data using a bulk api (bcp, bulk-insert, DTS bulk, etc)
> create the clustered index
> defrag clustered index <-- non intuitive, but optimal in many cases
> create the PK (if PK is not clustered)
> create other constraints
> create the NC indexes
> create statistics
> dbcc updateusage
>
> Before automating all this you'll obviously need to record the existing
> indexes, constraints and statistics
>
> ----------------------------------------------------
> The views expressed here are my own
> and not of my employer.
> ----------------------------------------------------
> "Phil" <atec396@hotmail.com> wrote in message
> news:03ab01c3554f$3b3767d0$a401280a@phx.gbl...
> > I want to increase performance on a fact table load.
> > The table has only a pk defined on an identity column.
> >
> > Since it would help performance to drop all indexes
> > and then load a fact table. Is there anyway I can drop
> > the clustered index that is automatically created
> > on the identity column and then recreate it.
> >
> > Would this help performance is the bottom line.
>
>

Yes an EMPTY heap is fastest, and I did mean by order of the clustered
index if you retain the clustered index, it is definately good idea to
drop any NCI's and possibly CLI's before data load and then recreate
them after the load, this will ensure the lowest amount of fragmentation
and is better option than leaving them and then reindexing.


Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!