ASE in IBM RS/6000 and "bcp" question

[ericwong]

Hi all,

Are there anyone have experience to install Sybase ASE 12.5 server in IBM RS/6000 AIX 4.3.3 - ML09. I had installed the ASE, but the performace is very bad. Are there any basic performace tuning process? And, when I load "bcp" data into the ASE12.5, the IO performace is very slow.
For example:
When I load a 9MB "bcp data file" with text and varchar field and 40000 rows, I need over 30mins. I find the CPU is 90% I/O waiting. And, when I check the disk I/O, there are only 0.5kbps writing rate. My disks are striping over three 10Krpm Hard disk. I guess the problem come the "bcp" command. The "bcp" command is very slow to load data into the server. How to maximize the "bcp" loading.


Best Regards
Eric Wong

 

[ghwin]

Eric,

There are two types of BCP, slow and fast. You cannot choose which. Sybase auto-picks depending on whether you have an index on it or not.

Might you have an index on your load table? If so, Sybase ASE must do transaction logging, which can be very slow (thus called Slow BCP; Fast BCP does not log transactions but only works on non-indexed tables). If this is your problem, see if you can drop all of your indices, load data thru BCP, then rebuild your index again (all of this usually done in a script). You'll need to test to see if this method is any faster than the 30mins it is taking now.

My bet is the index since 9MB should not take anywhere near 30mins to load.

Also, a hint to speed up building indices on the fly. Add worker processes and parallel processing (if you have more than 1 engine) under sp_configure. I can't remember the exact syntax but the online tech library is very helpful. Doing this will speed up index building by about 1000%.

Good Luck,

Greg Hwin