My application fetches a batch of data through a web service and writes 1000
entities per batch to a SQL Server 2000 database. There are 4 tables in
every batch. There are the following number of SQL commands executed per
average of every batch;

Table #1: always 1
Table #2: 5
Table #3: 5
Table #4: 3

The problem is that the performance slows down for every batch. Below is an
excerpt from my log file;

2004-12-15 12:00:01 Starting job... (RAM usage: 6,38 mb)

2004-12-15 12:00:39 data fetch time: 00:00:28 (RAM usage: 23,04 mb)
2004-12-15 12:00:39 Total data fetch time: 00:00:37 (RAM usage: 23,04 mb)
2004-12-15 12:00:39 Inserting/updating 1000 entities...
2004-12-15 12:01:20 Write SQL time: 00:00:40

2004-12-15 12:01:49 data fetch time: 00:00:24 (RAM usage: 26,87 mb)
2004-12-15 12:01:49 Total data fetch time: 00:00:29 (RAM usage: 26,87 mb)
2004-12-15 12:01:49 Inserting/updating 1000 entities...
2004-12-15 12:02:59 Write SQL time: 00:01:10

2004-12-15 12:04:06 data fetch time: 00:00:29 (RAM usage: 27,48 mb)
2004-12-15 12:04:06 Total data fetch time: 00:01:06 (RAM usage: 27,48 mb)
2004-12-15 12:04:06 Inserting/updating 1000 entities...
2004-12-15 12:05:30 Write SQL time: 00:01:23

2004-12-15 12:06:05 data fetch time: 00:00:31 (RAM usage: 27,03 mb)
2004-12-15 12:06:05 Total data fetch time: 00:00:35 (RAM usage: 27,03 mb)
2004-12-15 12:06:05 Inserting/updating 1000 entities...
2004-12-15 12:07:37 Write SQL time: 00:01:32

As one can see, the Write SQL time increases per every batch.
I would like this time to stay around one minute per batch.

There are one trigger per table. There is one parent table which has a
primary-foreign key relationship to the three sub tables.

I have 2% automatic file size growth set on both the data and the log file.


Thank you in advance to the guru which helps me out with this!

Hi

Autogrow is a performance killer. Grow the DB to the correct size and then
run your processes.

Without knowing exaclty what your code and tables do, it is difficult to
guess.
Look a Performance Monitor to see if you are getting a large number of page
splits.

Regards
Mike

"Magnus Österberg" wrote:

> My application fetches a batch of data through a web service and writes 1000
> entities per batch to a SQL Server 2000 database. There are 4 tables in
> every batch. There are the following number of SQL commands executed per
> average of every batch;
>
> Table #1: always 1
> Table #2: 5
> Table #3: 5
> Table #4: 3
>
> The problem is that the performance slows down for every batch. Below is an
> excerpt from my log file;
>
> 2004-12-15 12:00:01 Starting job... (RAM usage: 6,38 mb)
>
> 2004-12-15 12:00:39 data fetch time: 00:00:28 (RAM usage: 23,04 mb)
> 2004-12-15 12:00:39 Total data fetch time: 00:00:37 (RAM usage: 23,04 mb)
> 2004-12-15 12:00:39 Inserting/updating 1000 entities...
> 2004-12-15 12:01:20 Write SQL time: 00:00:40
>
> 2004-12-15 12:01:49 data fetch time: 00:00:24 (RAM usage: 26,87 mb)
> 2004-12-15 12:01:49 Total data fetch time: 00:00:29 (RAM usage: 26,87 mb)
> 2004-12-15 12:01:49 Inserting/updating 1000 entities...
> 2004-12-15 12:02:59 Write SQL time: 00:01:10
>
> 2004-12-15 12:04:06 data fetch time: 00:00:29 (RAM usage: 27,48 mb)
> 2004-12-15 12:04:06 Total data fetch time: 00:01:06 (RAM usage: 27,48 mb)
> 2004-12-15 12:04:06 Inserting/updating 1000 entities...
> 2004-12-15 12:05:30 Write SQL time: 00:01:23
>
> 2004-12-15 12:06:05 data fetch time: 00:00:31 (RAM usage: 27,03 mb)
> 2004-12-15 12:06:05 Total data fetch time: 00:00:35 (RAM usage: 27,03 mb)
> 2004-12-15 12:06:05 Inserting/updating 1000 entities...
> 2004-12-15 12:07:37 Write SQL time: 00:01:32
>
> As one can see, the Write SQL time increases per every batch.
> I would like this time to stay around one minute per batch.
>
> There are one trigger per table. There is one parent table which has a
> primary-foreign key relationship to the three sub tables.
>
> I have 2% automatic file size growth set on both the data and the log file.
>
>
> Thank you in advance to the guru which helps me out with this!
>
>
>
>
>
>