Insert performance problem

Hi,
In our application we do bulk insert 20000 rows into staging database
(bulk/logged recovery model) and then insert into production database from
this staging database. It seems like SQL Server is taking 20 sec to do the
insert into prod database. While if we delete records from prod database
table and run insert again it takes 600ms. Cpu time is approx 250ms in both
cases, writes in 1st case are 3500 and 250 in second and reads are
negligible. What can be the possible reason for this behavior that insert
into a tables as select * from 2nd table always runs slow first time as
compare to if we run insert again it works fine(we ran dbcc dropcleanbuffers
and checkpoint to make sure memory is clean for 2nd run)?
Thanks
--HarvinderSounds like you have a lot of page splits going on. Do you have a clustered
index on the table you are inserting into? How many nonclustered indexes do
you have? What about RI?
--
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> Hi,
> In our application we do bulk insert 20000 rows into staging database
> (bulk/logged recovery model) and then insert into production database from
> this staging database. It seems like SQL Server is taking 20 sec to do the
> insert into prod database. While if we delete records from prod database
> table and run insert again it takes 600ms. Cpu time is approx 250ms in
> both
> cases, writes in 1st case are 3500 and 250 in second and reads are
> negligible. What can be the possible reason for this behavior that insert
> into a tables as select * from 2nd table always runs slow first time as
> compare to if we run insert again it works fine(we ran dbcc
> dropcleanbuffers
> and checkpoint to make sure memory is clean for 2nd run)?
> Thanks
> --Harvinder
>|||We remove the clustered index and still the same issue with heap also...
also following is true:
Auto update stats is off ( we do it manually every night)
No trigger
No foreign key to/from this table
No blocking at time of insertion
Autogrow option is off for data and log files
Strange that the same rows insert slowly first time and then if delete from
table and insert again runs very fast...
"Andrew J. Kelly" wrote:
> Sounds like you have a lot of page splits going on. Do you have a clustered
> index on the table you are inserting into? How many nonclustered indexes do
> you have? What about RI?
> --
> Andrew J. Kelly SQL MVP
>
> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
> > Hi,
> >
> > In our application we do bulk insert 20000 rows into staging database
> > (bulk/logged recovery model) and then insert into production database from
> > this staging database. It seems like SQL Server is taking 20 sec to do the
> > insert into prod database. While if we delete records from prod database
> > table and run insert again it takes 600ms. Cpu time is approx 250ms in
> > both
> > cases, writes in 1st case are 3500 and 250 in second and reads are
> > negligible. What can be the possible reason for this behavior that insert
> > into a tables as select * from 2nd table always runs slow first time as
> > compare to if we run insert again it works fine(we ran dbcc
> > dropcleanbuffers
> > and checkpoint to make sure memory is clean for 2nd run)?
> >
> > Thanks
> > --Harvinder
> >
>
>|||> We remove the clustered index and still the same issue with heap also...
> also following is true:
Just make sure the data being inserted is roughly in the order of the
clustered index otherwise you will get lots of page splits.
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
Well that probably means the data you are reading from in in cache the
second time around.
Andrew J. Kelly SQL MVP
"harvinder" <harvinder@.discussions.microsoft.com> wrote in message
news:5433DEB7-3184-465D-8799-6203DFF09CC4@.microsoft.com...
> We remove the clustered index and still the same issue with heap also...
> also following is true:
> Auto update stats is off ( we do it manually every night)
> No trigger
> No foreign key to/from this table
> No blocking at time of insertion
> Autogrow option is off for data and log files
> Strange that the same rows insert slowly first time and then if delete
> from
> table and insert again runs very fast...
>
> "Andrew J. Kelly" wrote:
>> Sounds like you have a lot of page splits going on. Do you have a
>> clustered
>> index on the table you are inserting into? How many nonclustered indexes
>> do
>> you have? What about RI?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "harvinder" <harvinder@.discussions.microsoft.com> wrote in message
>> news:B5CB822C-6DFD-429D-AEF1-ED0674076E5C@.microsoft.com...
>> > Hi,
>> >
>> > In our application we do bulk insert 20000 rows into staging database
>> > (bulk/logged recovery model) and then insert into production database
>> > from
>> > this staging database. It seems like SQL Server is taking 20 sec to do
>> > the
>> > insert into prod database. While if we delete records from prod
>> > database
>> > table and run insert again it takes 600ms. Cpu time is approx 250ms in
>> > both
>> > cases, writes in 1st case are 3500 and 250 in second and reads are
>> > negligible. What can be the possible reason for this behavior that
>> > insert
>> > into a tables as select * from 2nd table always runs slow first time as
>> > compare to if we run insert again it works fine(we ran dbcc
>> > dropcleanbuffers
>> > and checkpoint to make sure memory is clean for 2nd run)?
>> >
>> > Thanks
>> > --Harvinder
>> >
>>