Slow during INSERT binary data

  • Thread starter Thread starter Guogang
  • Start date Start date
G

Guogang

Hi,

I have a question about the performance of inserting binary data.

I am using SQL 2000, with SP 3A.

We do a lot of binary data insert (about 300 files of the size 1M Byte
everyday). I discovered that most of the inserts are done smoothly, and
quick (no longer than 1 second). But, once a while, one of the insert will
take a long time (around 60 seconds). Then all the insert after that will be
quick and smooth again.

I think it might be related to some internal indexing, or statistics update
of SQL database. Somebody can give some insight of this problem? And, what
can I do to eliminate or at least reduce the frequency of this problem?

Thanks,
Guogang
 
It could be lots of things or simply blocking. Try running a profiler and
perfmon trace to see what is happening when the slowness occurs.
 
It does not look like blocking. Let me provide more details:

I do the insert in a stored procedure which is called from a .NET program
using ADO. There is a 30 seconds timeout (SqlCommand.CommandTimeout). At
some point, database hit the barrier, my insert statement in the stored
procedure takes more than 30 seconds, causing timeout. This statement is
dropped. My program will try to do the insert again by calling the stored
procedure. And, the timeout happens repeatedly without exceptoin. So, my
program is stucked, and can never go any step further.

So, I run the statement in query analyzer which does not have timeout. I
wait patiently for more than 1 minutes. The insert finally succeded. After
this break throught. I run my program, the statement in the stored procedure
starts to go quick and smooth.

So, it is very likely that some updates in SQL database need a push from my
insert statement to finish.

Thanks
 
Hi,

Is your database growing by around 300MB each time?

If so, then you may benefit by pre-extending the database to a longer term
suitable size.

If not, then sorry...

- Tim
 
Try monitoring Checkpoint pages/sec from perfmon while your process is
running to see if the slowdown coincides with a lot of checkpoint activity.
If so, this may indicate your disk subsystem can't keep up with the write
demands.

Placing logs on different drives/arrays can help as will RAID 10 (instead of
RAID 5). Although you could reduce the checkpoint frequency by changing the
'recovery interval' configuration option, it's usually best to leave it the
default (0). Note that you can override the default CommandTimeout in your
application.
 
Back
Top