Date:         Thu, 21 Dec 2006 13:47:28 -0500
Reply-To:     "Putnam, Darryl" <Darryl.Putnam@CONSTELLATION.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Putnam, Darryl" <Darryl.Putnam@CONSTELLATION.COM>
Subject:      Re: Need for speed: writing to a database with SAS/ACCESS
Comments: To: "Pardee, Roy" <pardee.r@GHC.ORG>
In-Reply-To:  A<8AD8F86B3312F24CB432CEDDA71889F2031356FF@ex06.GHCMASTER.GHC.ORG>
Content-Type: text/plain; charset="us-ascii"

Also try the bulkload option

Darryl Putnam
IT, Corporate Credit & Risk
darryl.putnam@constellation.com
(o) 410-468-3637
(c) 301-788-3347
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Pardee, Roy
Sent: Thursday, December 21, 2006 11:53 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Need for speed: writing to a database with SAS/ACCESS

Have you tried playing w/the DBCOMMIT option on the libname statement?
Try bumping that up to say, 10k & see if it makes a difference.

Other options to play with available here:

http://support.sas.com/onlinedoc/913/getDoc/en/acreldb.hlp/a002915222.ht
m

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Dimitri Shvorob
Sent: Wednesday, December 20, 2006 10:53 PM
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.
>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee.  If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1