sql server odbc:
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!
