Writing data to from SAS to BigQuery very slow

[JoonaH]

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?

[LinusH]

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;

[Kurt_Bremser]

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

[SASKiwi]

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.

[ChrisNZ]

Also look at the BULKLOAD option.

[JoonaH]

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.

[SASKiwi]

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.