insert question

This is more of a sql query syntax question then anything but I need to run it on my handheld.

I have 3 data collection tables, and 3 staging tables that will be used to PUSH the data back to the SQL Server.

so here is what happens:

to populate staging table 1 I do

insert into stagingtable1 (id, col1, col2, col3) select newid, col1, col2, col3 from datacollection table1

staging table 2:

insert into stagingtable2 (id) select id from stagingtable1

staging table 3.

insert into stagingtable2 (id - (now this ID field needs to be the same id field as staging table 2 and staging table 1 have) col1, col2, col3, col4, from DataCollectionTable2

how can I create the insert SQL for my staging table 3?

revised:

there is no columns I can join on to get the uploadID to insert into stagingtable 3

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
>