Dimitri: 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 copying methods.

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.

Also see http://support.sas.com/software/91x/acreldbwhatsnew900.htm for information about SAS/Access to MySQL in SASV9.12+ and options for insert buffer size settings.

Sig

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Dimitri Shvorob Sent: Thursday, December 21, 2006 1:53 AM To: SAS-L@LISTSERV.UGA.EDU 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.

Thank you.