Very Computer

by **Bruc** » Sun, 20 Jan 2002 04:25:55

Why does my load keep running slower and slower. We are running Sybase
11.9.2 on HP. I am using BCP to load data from a text file on UNIX
into our database. The load (4 million rows) began at 500 rows per
second and after two hours was down to 30 rows per second. The next
day it was loading at 7 rows per second. What could cause such a slow
down. There are no indexes or triggers on the table. The table has
about 10 attributes, one which is TEXT. The TEXT attribute is where
most of the data is being stored. The table is NOT indexed. We have
updated statistics on the table.

by **Bret Halfo** » Sun, 20 Jan 2002 06:40:52

> Why does my load keep running slower and slower. We are running Sybase
> 11.9.2 on HP. I am using BCP to load data from a text file on UNIX
> into our database. The load (4 million rows) began at 500 rows per
> second and after two hours was down to 30 rows per second. The next
> day it was loading at 7 rows per second. What could cause such a slow
> down. There are no indexes or triggers on the table. The table has
> about 10 attributes, one which is TEXT. The TEXT attribute is where
> most of the data is being stored. The table is NOT indexed. We have
> updated statistics on the table.

Sounds like it may be table-scanning to find the insertion point for
each row, but I can't say why it would be doing this.

As an aside, for such a large bcp in job, is there any reason why you
didn't partition the table and do the bcp in multiple parallel
sessions?

-bret

by **Anthony Mand** » Sun, 20 Jan 2002 16:26:00

> Why does my load keep running slower and slower. We are running Sybase
> 11.9.2 on HP. I am using BCP to load data from a text file on UNIX
> into our database. The load (4 million rows) began at 500 rows per
> second and after two hours was down to 30 rows per second. The next
> day it was loading at 7 rows per second. What could cause such a slow
> down. There are no indexes or triggers on the table. The table has
> about 10 attributes, one which is TEXT. The TEXT attribute is where
> most of the data is being stored. The table is NOT indexed. We have
> updated statistics on the table.

I'm not sure why you've bothered with that last bit. If there are
no indexes on your table, an update stats will achive nothing.

First - check your exact version of ASE and bcp. Check if you need
an EBF.

Second - do you have triggers and are they disabled?

Third - what about your devices?

Fourth - monitor with sp_sysmon and check the system's performance
too.

Fifth - what's your batch size? And packet size (since you're using
text)?

Sixth - have you done any server config tuning for bcp?

-am ? 2002

by **who** » Tue, 22 Jan 2002 10:51:46

There are a few things that can cause the bcp to slow. Of course the
obvious is the indices and the triggers but you said that both of
those are not present.
Take a look at other things like,

1) Make certain that the 'select into/bulkcopy/pllsort' option is set
- use sp_dboption <db> 'select', true and then checkpoint
database.
2) if the batchsize is large set the 'truncate log on chkpt' option.
- rule 0f thumb (batch size = 960*<rows per page> * <# of
pages in the # of pre-allocated extents you have>
3) use the 'A' option in bcp to set the packet size.
- you have to play around with the 'additional network
mem' at
<#parallel bcps> * <packet size> * 3 and the
'max network packet size' configuration as well.
(default is 512).
4) 'configure the # of pre-allocated extents'

Some of these you may only want for the bcp as some have trade-offs
between the perfomance of the bcp and the performance of indices.

Take a look at the data: is there more text at the end of the table.
4 million rows on a good server should take no more than 30-40 minutes
with the data that you are describing here. Check the log and the
options that are set here. Is the bcp from the local machine? don't
bother with the update on stats until after the load. Also, this is
overlooked at times. Make sure there is at least 2 times the amount
of free space in the database that you are putting in. This will help.

Hope this helps.

Eric

> Why does my load keep running slower and slower. We are running Sybase
> 11.9.2 on HP. I am using BCP to load data from a text file on UNIX
> into our database. The load (4 million rows) began at 500 rows per
> second and after two hours was down to 30 rows per second. The next
> day it was loading at 7 rows per second. What could cause such a slow
> down. There are no indexes or triggers on the table. The table has
> about 10 attributes, one which is TEXT. The TEXT attribute is where
> most of the data is being stored. The table is NOT indexed. We have
> updated statistics on the table.

by **Pablo Sanche** » Tue, 22 Jan 2002 12:57:34

Quote:> There are a few things that can cause the bcp to slow. Of course
the
> obvious is the indices and the triggers but you said that both of
> those are not present.
> Take a look at other things like,

> << stuff snipped >>

What I found intriguing about the problem is that it runs slower and
slower. Hmmmm. I was thinking that monitoring the system throughout
the load (sar/iostat) and ASE (sp_sysmon) might provide some useful
information.
--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Independent Contractor, available for short-term and long-term
contracts

Very Computer

Why does my BCP Load run slower, and slower, and slower

Why does my BCP Load run slower, and slower, and slower

Why does my BCP Load run slower, and slower, and slower

Why does my BCP Load run slower, and slower, and slower

Why does my BCP Load run slower, and slower, and slower

Why does my BCP Load run slower, and slower, and slower