By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Stored procedure performance mystery

P: n/a
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!


Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Magnus Österberg (ma******@abo.fi) 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Magnus Österberg (ma******@abo.fi) 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****@sommarskog.se

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

Jul 23 '05 #3

P: n/a
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.

Jul 23 '05 #4

P: n/a

<wd******@rmi.net> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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
..
..
..

Jul 23 '05 #5

P: n/a
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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.