topic Writing data to from SAS to BigQuery very slow in SAS Procedures

Writing data to from SAS to BigQuery very slow

JoonaH — Wed, 26 Feb 2025 14:19:41 GMT

I'm using SAS to process data and then write the result in BigQuery database through Simba ODBC driver. I have managed to get this working using proc sql create table, but writing the data takes insanely long time. I tried inserting around 200 rows of data with 4 short string values. This query takes around 7 minutes to finish. Inserting 2000 takes 1 hour. Does anyone know how to insert rows efficiently to BigQuery?

Re: Writing data to from SAS to BigQuery very slow

LinusH — Wed, 26 Feb 2025 15:36:04 GMT

Can you please provide some more information, like:

- SAS version

- What SAS/Access module your are using

- options used in your libnmae statement

- topography of your set-up

Usually bulk-load is the fastest way to load an external DB, that might be available depending on the answers to the questions above.

Using these options might give some clue on the processing:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Re: Writing data to from SAS to BigQuery very slow

Kurt_Bremser — Wed, 26 Feb 2025 15:36:55 GMT

You say that you insert observations. What if you use a simple DATA step to write the SAS dataset to the database?

Re: Writing data to from SAS to BigQuery very slow

SASKiwi — Thu, 27 Feb 2025 01:49:31 GMT

In my experience the default settings that tune database loading are not optimal. I've had excellent load performance by adding these options to the LIBNAME or CONNECTION statement pointing at the database:

libname MyDB ODBC < connection settings > dbcommit = 0 insertbuff = 10000;

However you may need to experiment with these options to get the best performance.

Re: Writing data to from SAS to BigQuery very slow

ChrisNZ — Thu, 27 Feb 2025 10:08:40 GMT

Also look at the BULKLOAD option.

Re: Writing data to from SAS to BigQuery very slow

JoonaH — Fri, 07 Mar 2025 13:49:41 GMT

Thank you all for responses. I haven't had much time to test different setups so maybe the answer hides here somewhere.

@LinusH Log hasn't revealed anything so far, but then again I'm not that good interpreting it.

@Kurt_Bremser I tried using proc sql create table. Now that you suggestes using data step I tried it too, but the performance was the same.

@SASKiwi Tried to fiddle with these settings as you suggested, but didn't get any observable differences yet.

@ChrisNZ (and also @LinusH) couldn't get BULKLOAD to work yet. This is probably the root cause for this issue. Need to dig if my driver supports it. I'm afraid it might not.

Will take quite a while though when I can next look into this so the answer needs to wait for now.

Re: Writing data to from SAS to BigQuery very slow

SASKiwi — Sat, 08 Mar 2025 01:50:20 GMT

To date, you haven't posted any SAS logs of your slow steps. That means we have no idea what techniques you are using. In my experience some techniques are better than others. If you want to make further progress then I suggest you post one or two slow steps so we can get a better idea of what techniques you use.