sybperl-l Archive

From: Michael Peppler <mpeppler at peppler dot org>
Subject: DBD::Sybase Bulk load vs. insert speeds
Date: Aug 15 2004 7:57AM

Rene van Leeuwen ran some tests to compare the new bulk-load facility in
DBD::Sybase 1.04.6 with normal inserts, and with Sybase's "bcp" utility.
His tests show about 40% speed increase when using the bulk-load API vs.
a normal prepare()/execute() loop.

I ran some tests for myself as well, using Sybase 12.5.2 on my lowly
Fedora Core 1 laptop, and got the following results:

[mpeppler@localhost tmp]$ time ./test-ins.pl
Sent 19538 rows to the server
 
real    0m58.687s
user    0m9.960s
sys     0m1.050s
[mpeppler@localhost tmp]$ time ./test-blk.pl
Sent 19538 to the server
 
real    0m21.805s
user    0m2.140s
sys     0m0.110s
[mpeppler@localhost tmp]$ time bcp testdb..com_props_log in
../com_props_log.bcp -Usa -P -Selanor -c
 
Starting copy...
1000 rows sent to SQL Server.
...
19000 rows sent to SQL Server.
 
19538 rows copied.
Clock Time (ms.): total = 11450  Avg = 0 (1706.38 rows per sec.)
 
real    0m11.885s
user    0m0.710s
sys     0m0.070s

The target table is fairly narrow, and the "insert" script has
AutoCommit turned on (I don't have enough log space to run this with
AutoCommit off right now).

Here we see that using the BLK API improves the speed by over 60%, and
using the "bcp" binary drops another 50% off that. The larger difference
between 'insert' and 'bulk' that I see compared to Rene's tests are most
likely due to the very slow IO on my laptop.

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler@peppler.org                       http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html