I don't know whether MySQL supports an ODBC 'bulk load' of data or what
you would have to add to the ODBC connection string to make it work, but
a bulk load process bypasses rollback enabling and checks of integrity
constraints for individual row inserts, deletes, and updates. In many
databases systems, bulk loads work many times faster than other database
As an alternative to ODBC, SAS can write a load file and MySQL can load
that (possibly via a SAS pass-thru to MySQL). The MySQL LOAD FILE
reportedly works very quickly.
for information about SAS/Access to MySQL in SASV9.12+ and options for
insert buffer size settings.
From: firstname.lastname@example.org [mailto:email@example.com]
On Behalf Of Dimitri Shvorob
Sent: Thursday, December 21, 2006 1:53 AM
Subject: Need for speed: writing to a database with SAS/ACCESS
Occasionally, I have to transport a SAS dataset into a (local,
single-user) MySQL database, and have used MySQL and ODBC engines of
SAS/ACCESS for this task. In both cases, and regardless of whether I
transfer data with something like 'data db.table; set sas.dataset;
run;', or with PROC COPY, I have found writes to be *very* slow - a
singe numeric column with 100,000 rows might well take 10 minutes to
pass to MySQL. I was wondering if there are ways to improve the write
speeds, and would appreciate a suggestion or a helpful link.