should make the difference. you want to insert more than 1 row at time, but
keep the unit of work not too large. you want the unit of work to fit in
memory, so no page writes occur, except for the log writes. i usually find
10k about the right size, but if your rows have lots of data and indexes,
you might try as low as 1000 rows at a time. i'd expect much better than 40
minutes with this approach, as it should easily beat bcp in slow mode.
note: with bcp the batchsize only controls how many rows are inserted before
a commit, and has little impact on performance (except on a rollback when it
fails). the tablock save little time, as lock escalation will happen very
soon anyway. mostly usefull for stopping readlocks getting in the way.
> Bruce - that's pretty much what I'm doing now. I have a raw text file
that
> I process and insert into a work table that has just been truncated. This
> work table has no indexes. I was doing an insert statement to insert
all
> rows from work table into live table, but perf of that insert statement is
> about one hour. In my testing, if I BCP data out of work table, then Bulk
> Insert into Live table, I get about 40 minutes.
> Isn't that essentially what you are saying to do??? Will the process
state
> column and rowcount statement that you have in your script really make a
> difference??
> Thanks for your help.
> > because you have index's, bulk load is really just a batch insert. to
make
> > it go fast do the following.:
> > create a work table with no indexes (i always use a work database on the
> > same server) with one additional column named processstate. also be sure
> > "select into / bulk" is set in the work database.
> > truncate work table
> > bcp data into work table (this will be fast, as the fast load routine
will
> > be used)
> > call sp to move data from work table to live table
> > create procedure loaddata
> > as
> > begin
> > set rowcount 10000
> > update workdb.dbo.worktable
> > set processstate =1
> > where isnull(processstate,0) < 1
> > set rowcount 0
> > begin
> > insert livedata.dbo.livetable (...)
> > select ... from workdb.dbo.worktable
> > where processsate = 1
> > update workdb.dbo.worktable
> > set proccessstate = 2
> > where processstate = 1
> > end
> > end
> > this should get you down to a couple of minutes
> > > I currently have a nightly process that does a bulk insert of
> > approximately
> > > 100,000 rows into a 15 million row table. The destination table has 1
> > > clustered and 2 non-clustered indexes that have fill_factors of 90 and
> > they
> > > are dropped/recreated weekly.
> > > I have tried using the BATCHSIZE, TABLOCK, ORDER, and
> > DATAFILETYPE='native'
> > > hints but the insert still takes over 40 minutes. We have a good
> server
> > > with quad processors which I don't think is the issue.
> > > Creating the indexes takes much longer, so it is not an option to drop
> > them
> > > before the bulk insert. Does this time (40+ minutes) sound reasonable
> to
> > > people???
> > > I'm at my wits end to speed this thing up anymore. Any help/thoughts
> > would
> > > be appreciated. Thanks in advance.
by