batch insert into a Huge table performance problems

yes we are on Sql 2005.
Could you tell me a little more about the options with doing this on Sql
2005?

the hardware is exceptional Quad CPU 6 Gb Ram.


"Sammy" wrote in message

> We use this approach for audit trails on our highly transactional
> accounting
> system. It has minimal inpact on the performance. I would suggest
> trying/testing but believe this would be a great improvement. I am also
> curious why your current scenario would take 125 minutes. Seems to me like
> you may have room for optimizing your query that gets the data and
> inserts.
> Possibly you have an issue (converting datatypes, etc.) in your where
> clause
> CreatedDate=Today. Another approach would be bcp. If you are using SQL
> 2005,
> even more choices are available. In my "opinions" I am assuming that your
> hardware is sufficient and you don't have problems with memory/pauge
> faults/bad indexes, etc. that might be causing the slow down.
>
>
>
> "Mike Kansky" wrote:
>
>> It is an idea.
>> But wouldn't that slow down the insert into TableA?
>> There are 1000 inserts a minute into this table, and it is vital for the
>> users to not have any performance issues while using the app....
>>
>>
>>
>> "Sammy" wrote in message
>>
>> > What about adding a Trigger to tableA on insert to add the data to
>> > tableB?
>> > Then it would happen automatically when data is added to tableA?
>> >
>> > "Mike Kansky" wrote:
>> >
>> >> We have a table (TableB) which we use to archive records accumulated
>> >> for
>> >> the
>> >> day from (TableA).
>> >>
>> >> All live applications are using TableA (1000 queries a minute)
>> >>
>> >> When we archive the data from TableA to TableB we use this:
>> >>
>> >> Insert into TableB select * from TablaA with (nolock) where
>> >> CreatedDate=Today
>> >>
>> >> This statement will move 200,000-500,00 and is executed for about 125
>> >> min.
>> >>
>> >> The problem is that all queries that are executed against TableA by
>> >> our
>> >> users while this command is running become slower, maybe 100 times
>> >> slower,
>> >> when this job is not running Application response times are instant,
>> >> when
>> >> the job is executing, the application is dragging feet.
>> >>
>> >> We also have a delete statement that deletes archived records from
>> >> TableA
>> >> and had the same(even worse) problem but we managed to write a
>> >> procedure
>> >> that does delete in batches which solved our problem. Is there any way
>> >> to
>> >> do
>> >> the same for Insert maybe (batch the insert, 50 rows per batch...or
>> >> something)
>> >> Or maybe there is another solution....?
>> >>
>> >>
>> >>
>> >>
>>
>>
>>
 >> Stay informed about: batch insert into a Huge table performance problems 

Mike Kansky (postmaster@zazasoftware.com) writes:
> We have a table (TableB) which we use to archive records accumulated for
> the day from (TableA).
>
> All live applications are using TableA (1000 queries a minute)
>
> When we archive the data from TableA to TableB we use this:
>
> Insert into TableB select * from TablaA with (nolock) where
> CreatedDate=Today
>
> This statement will move 200,000-500,00 and is executed for about 125 min.

It should not take 125 minutes to insert half a million rows. Is there
an index on CreatedDate? How does the exact query look like? Is there any
trigger on TableB? Is TableB on a linked server?

> The problem is that all queries that are executed against TableA by our
> users while this command is running become slower, maybe 100 times slower,
> when this job is not running Application response times are instant, when
> the job is executing, the application is dragging feet.

Are queries against other tables running swiftly? (I would not expect so.)

Can you post the output from sp_spaceused on the table?

Which version of SQL Server are you using.

> We also have a delete statement that deletes archived records from
> TableA and had the same(even worse) problem but we managed to write a
> procedure that does delete in batches which solved our problem. Is there
> any way to do the same for Insert maybe (batch the insert, 50 rows per
> batch...or something)

Yes, but if you don't do it properly, it could make matters worse. The
best strategy would be something like:

INSERT tblB (...)
SELECT ....
FROM tblA
WHERE CreatedDate = @today
AND somekey >= @start
AND somekey < @end

And then there is an index on (CreatedDate, somekey), so that the rows
can be located quickly. For each batch you would set @start to the
previous @end value.

If CreatedDate has both date and time, you could simply iterate over
CreatedDate, and take batches of 30 minutes of whatever.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: batch insert into a Huge table performance problems 

TableB has no trigger, and is on the same server in deffirent database.
there is an Index on CreatedDate
Here's an exact query:

insert into TableB
select field1, field2, field3....to 15
from tableA with (nolock) where CreatedDate<Today at 00:00

We are using Sql 2005


So you are suggesting to open a cursor and keep 30 minute intervals there to
pass into the Insert statement?






"Erland Sommarskog" wrote in message

> Mike Kansky (postmaster@zazasoftware.com) writes:
>> We have a table (TableB) which we use to archive records accumulated for
>> the day from (TableA).
>>
>> All live applications are using TableA (1000 queries a minute)
>>
>> When we archive the data from TableA to TableB we use this:
>>
>> Insert into TableB select * from TablaA with (nolock) where
>> CreatedDate=Today
>>
>> This statement will move 200,000-500,00 and is executed for about 125
>> min.
>
> It should not take 125 minutes to insert half a million rows. Is there
> an index on CreatedDate? How does the exact query look like? Is there any
> trigger on TableB? Is TableB on a linked server?
>
>> The problem is that all queries that are executed against TableA by our
>> users while this command is running become slower, maybe 100 times
>> slower,
>> when this job is not running Application response times are instant, when
>> the job is executing, the application is dragging feet.
>
> Are queries against other tables running swiftly? (I would not expect so.)
>
> Can you post the output from sp_spaceused on the table?
>
> Which version of SQL Server are you using.
>
>> We also have a delete statement that deletes archived records from
>> TableA and had the same(even worse) problem but we managed to write a
>> procedure that does delete in batches which solved our problem. Is there
>> any way to do the same for Insert maybe (batch the insert, 50 rows per
>> batch...or something)
>
> Yes, but if you don't do it properly, it could make matters worse. The
> best strategy would be something like:
>
> INSERT tblB (...)
> SELECT ....
> FROM tblA
> WHERE CreatedDate = @today
> AND somekey >= @start
> AND somekey < @end
>
> And then there is an index on (CreatedDate, somekey), so that the rows
> can be located quickly. For each batch you would set @start to the
> previous @end value.
>
> If CreatedDate has both date and time, you could simply iterate over
> CreatedDate, and take batches of 30 minutes of whatever.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel RemoveThis @sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: batch insert into a Huge table performance problems 

Mike Kansky (postmaster@zazasoftware.com) writes:
> TableB has no trigger, and is on the same server in deffirent database.
> there is an Index on CreatedDate

Is that a clustered index or a non-clustered index?

> Here's an exact query:
>
> insert into TableB
> select field1, field2, field3....to 15
> from tableA with (nolock) where CreatedDate<Today at 00:00

Since "Today at 00:00" is not legal T-SQL syntax, this is obviously
the actual query.

I asked for the outut of sp_spaceused, since I want to get an idea
of how big this table is. Could you provide that?

> So you are suggesting to open a cursor and keep 30 minute intervals
> there to pass into the Insert statement?

Well, it would not be a cursor with DECLARE CURSOR and that. Just a
loop that moves data for a smaller interval. It helps if the interval
can be aligned with the clustered index.



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: batch insert into a Huge table performance problems