Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
only once?
Thanks,
Only once, and ALL of the rows are in the inserted table.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>
|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
> only once?
> Thanks,
>
|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:

> In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or
> only once?
> Thanks,
>

Insert trigger for bulk insert

In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
only once?
Thanks,Only once, and ALL of the rows are in the inserted table.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
> only once?
> Thanks,
>|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:
> In case of a bulk insert, the â'FOR INSERTâ' trigger fires for each recod or
> only once?
> Thanks,
>

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod or only once?

Thanks,

Once for each batch. Here's the specific text from Books Online

"Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch."

Note that, by default, the triggers do not fire for bulk operations, you must specify the FIRE_TRIGGERS option in order to get triggers to fire for bulk operations.

|||

This option may have issues with performance if you enable during the bulk load operaton during the otherside of triggers, refer to the books online about Controlling trigger execution when bulk importing data.

HTH

Insert trigger for bulk insert

In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or only once?

Thanks,

(FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table execute during the bulk-import operation. If triggers are defined for INSERT operations on the target table, they are fired for every completed batch.

If FIRE_TRIGGERS is not specified, no insert triggers execute.

For more information, see Controlling Trigger Execution When Bulk Importing Data. )

What you are looking for is in the BOL(books online). Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

sql

Insert trigger for bulk insert

In case of a bulk insert, the “FOR INSERT” trigger fires for each recod
or
only once?
Thanks,Only once, and ALL of the rows are in the inserted table.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the "FOR INSERT" trigger fires for each recod or
> only once?
> Thanks,
>|||To add to Arnie's response, the trigger is fired one for each batch of
records and only if FIRE_TRIGGERS is specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B90E6B36-CA4C-49C0-B4C2-268CA0A241A7@.microsoft.com...
> In case of a bulk insert, the “FOR INSERT” trigger fires for each reco
d or
> only once?
> Thanks,
>|||Hi Jim
The BULK INSERT statement has a FIRE_TRIGGERS option and BCP has it as a
hint, to make INSERT triggers fire. See Books Online for more.
John
"JIM.H." wrote:

> In case of a bulk insert, the “FOR INSERT” trigger fires for each reco
d or
> only once?
> Thanks,
>

Insert Trigger and Bulk Insert

I am using Bulk Insert to insert multiple rows into a table (Invoice_Lines_Temp). I need to update/insert rows in another table (Invoice_Lines) as these rows are inserted into Invoice_Lines_Temp. As I understand, the trigger is only fired once for the insert so only one row is affected in Invoice_Lines, and this is what I'm seeing.

How would someone modify this trigger to update/insert the Invoice_Line table for each record inserted into Invoice_Line_Temp?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

Declare @.Transaction_ID int,

@.Line_Number int,

@.Item_Desc varchar(32),

@.Invoice_Date datetime,

@.Group_Number int,

@.Item_Code varchar(8),

@.Quantity_Sold decimal,

@.Section_Number int,

@.LastUpdate datetime

Select @.Transaction_ID = inserted.Transaction_ID,

@.Line_Number = inserted.Line_Number,

@.Item_Desc = Inserted.Item_Desc,

@.Invoice_Date = inserted.Invoice_Date,

@.Group_Number = inserted.Group_Number,

@.Item_Code = inserted.Item_Code,

@.Quantity_Sold = inserted.Quantity_Sold,

@.Section_Number = inserted.Section_Number,

@.LastUpdate = inserted.LastUpdate

from inserted

IF Exists

(Select dbo.invoice_line.transaction_id, dbo.invoice_line.line_number

from dbo.invoice_line

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number)

Begin

update dbo.invoice_line

set Item_Desc = @.Item_Desc,

Invoice_Date = @.Invoice_Date,

Group_Number = @.Group_Number,

Item_Code = @.Item_Code,

Quantity_Sold = @.Quantity_Sold,

Section_Number = @.Section_Number,

LastUpdate = @.LastUpdate

where dbo.invoice_line.transaction_id = @.Transaction_id

and dbo.invoice_line.Line_Number = @.Line_Number

end

ELSE

Begin

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

VALUES

(newid(),@.Transaction_ID,@.Line_Number,@.Item_Desc,@.Invoice_Date,

@.Group_Number,@.Item_Code,@.Quantity_Sold,@.Section_Number,@.LastUpdate)

end

Try this:

Code Snippet

ALTER TRIGGER [dbo].[Invoice_Line_Temp_Insert]

on [dbo].[Invoice_Line_Temp]

for Insert

As

BEGIN

UPDATE il

SET Item_Desc = i.Item_Desc,

Invoice_Date = i.Invoice_Date,

Group_Number = i.Group_Number,

Item_Code = i.Item_Code,

Quantity_Sold = i.Quantity_Sold,

Section_Number = i.Section_Number,

LastUpdate = i.LastUpdate

FROM dbo.invoice_line il

INNER JOIN inserted

ON il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number

INSERT INTO dbo.Invoice_Line

(InvoiceLineId,Transaction_ID,Line_Number,Item_Desc,Invoice_Date,

Group_Number,Item_Code,Quantity_Sold,Section_Number,LastUpdate)

SELECT newid(), i.Transaction_id, i.Line_number, i.Item_Desc, i.Invoice_Date,

i.GroupNumber, i.Item_Code, i.Quantity_Sold, i.Section_Number, i.LastUpdate

FROM inserted

WHERE NOT EXISTS

( SELECT * FROM dbo.Invoice_Line il

WHERE il.transaction_id = i.Transaction_id

AND il.Line_Number = i.Line_Number)

END

|||Worked great! More elegant as well. thanks...

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
--Harvinder
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:
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...
>
>
|||> 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...[vbcol=seagreen]
> 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:

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 cluster
ed
> 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...
>
>|||> 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...[vbcol=seagreen]
> 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:
>

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
>> >
>>

Insert performance --2nd post

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)?
Also we tried following steps:
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...
Thanks
--HarvinderHi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:
> 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)?
> Also we tried following steps:
> 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...
>
> Thanks
> --Harvinder
>

Insert performance --2nd post

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)?
Also we tried following steps:
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...
Thanks
--Harvinder
Hi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:

> 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)?
> Also we tried following steps:
> 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...
>
> Thanks
> --Harvinder
>

Insert performance --2nd post

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)?
Also we tried following steps:
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...
Thanks
--HarvinderHi
Look at performance counters for Page Splits and Extent Allocations.
Also, if you have hardware disk cache, the data is in disk cache on the 2nd
run.
Regards
Mike
"harvinder" wrote:

> 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 bot
h
> 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 dropcleanbuffe
rs
> and checkpoint to make sure memory is clean for 2nd run)?
> Also we tried following steps:
> 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 fro
m
> table and insert again runs very fast...
>
> Thanks
> --Harvinder
>