Up Prev Next
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
[mpeppler@localhost tmp]$ time ./test-blk.pl
Sent 19538 to the server
[mpeppler@localhost tmp]$ time bcp testdb..com_props_log in
../com_props_log.bcp -Usa -P -Selanor -c
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.)
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 Peppler Data Migrations, Inc.
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short
or long term contract positions - http://www.peppler.org/resume.html