Stored procedure performance mystery

[Magnus Österberg]

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!

[Erland Sommarskog]

Magnus Österberg (ma******@abo.f i) writes:

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;

A number of possible reasons, of which some would have possible to rule
out, had you provided more information.

Are the table empty when you insert the first batch? Or you get this
behaviour if you restart SQL Server and start to insert rows into tables
that already has a million rows?

If you start with empty table, the problem could be in the triggers.

Another possibility is that you pay a penalty to auto-growth. 2% auto-grow
is a tad little.

I would recommend that you run a Profiler trace, and add the events
SP:Completed, SP:Recompile and the auto-grow event. Thereby you might
be able to see where the time is spent. Note that you don't get any
duration for recompilations, but you should always pay attention to
recompatilons, as they some time depending on batch size.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Magnus Österberg]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Magnus Österberg (ma******@abo.f i) writes:

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;
A number of possible reasons, of which some would have possible to rule
out, had you provided more information.

Are the table empty when you insert the first batch? Or you get this
behaviour if you restart SQL Server and start to insert rows into tables
that already has a million rows?

Yes, all four tables are empty at start.

If you start with empty table, the problem could be in the triggers.

Another possibility is that you pay a penalty to auto-growth. 2% auto-grow
is a tad little.
I changed it back to 10... no change.

I would recommend that you run a Profiler trace, and add the events
SP:Completed, SP:Recompile and the auto-grow event. Thereby you might
be able to see where the time is spent. Note that you don't get any
duration for recompilations, but you should always pay attention to
recompatilons, as they some time depending on batch size.

I'll try that and tell you the results!

Thanks!


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[wdieteri]

With the way MS-SQL server does caching with stored procedures and
indexes you could be running into a problem with an old cached query
plan.

Try adding a WITH RECOMPILE to the stored procedure. This may show
down the stored procedure for each execution, will not if you are using
dynamic queries in the SP, but if this is hte problem then you will not
have the decreased time as you go along.

[Magnus Österberg]

<wd******@rmi.n et> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

With the way MS-SQL server does caching with stored procedures and
indexes you could be running into a problem with an old cached query
plan.

Try adding a WITH RECOMPILE to the stored procedure. This may show
down the stored procedure for each execution, will not if you are using
dynamic queries in the SP, but if this is hte problem then you will not
have the decreased time as you go along.

I now added WITH RECOMPILE to all four SP's. I am still facing the growth in
execution time.

#1 1min 1s
#2 1min 5s
#3 1min 20s
#4 1min 25s
..
..
..

[Erland Sommarskog]

Magnus Österberg (ma************ **@abo.fi) writes:

Yes, all four tables are empty at start.

In that case, I would first fill up the table with the size you
expect in production, and then try to improve performance from there.
Most likely the problems are in the triggers.

If you are lucky, the problem with go away with increasing size. The
query plans for empty tables may not be good when the tables fills up.

A tip for triggers is that if you refer to inserted/deleted in several
places in the trigger, that you copy the columns you need into table
variables, since inserted/deleted are usually slow.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp