topic Re: SAS upload to Voyager/TRINO is very slow in SAS Programming

SAS upload to Voyager/TRINO is very slow

michal_1407 — Wed, 03 Jan 2024 10:34:55 GMT

Hi,

in my work I need to upload temporary table from SAS server to EAP server. Now we are using TRINO/Voyager and under SAS we can do it running the below codes, but it is very sloooooow.

Uploading 428 records from SAS (sashelp.cars) to EAP takes 13/24 minutes. I have experience with hadoop and under hadoop it takes 2-3 minutes.

Do you know how can I speed up this step ? I tried to add “BULKLOAD=YES” option but no change.

Best Regards

29 libname libtest jdbc classpath='/opt/sas/JDBC3/'

30 class='io.trino.jdbc.TrinoDriver'

31 URL='jdbc:trino://ccc.aaa.bbb.net:8980/eap/aml?SSL=true&SSLVerification=CA&SSLTrustStorePath=/opt/sas/JDBC3/cacerts.jks&SSLTrustStorePassword=changeit'

32 user=&sysuserid.

33 password="&pass."

34 schema=my_schema

36 ;

NOTE: Libref LIBTEST was successfully assigned as follows:

Engine: JDBC

38 data libtest.sas_test_upl_presto2;

39 set sashelp.cars;

40 run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: The data set LIBTEST.SAS_TEST_UPL_PRESTO2 has 428 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 13:08.07

cpu time 0.35 seconds

Re: SAS upload to Voyager/TRINO is very slow

Patrick — Wed, 03 Jan 2024 11:16:40 GMT

13 minutes for 428 rows with a few variables! Even taking some latency into account this is just "ridiculous". Don't even bother about looking into bulk loading for now.

I suggest you (your SAS admin) raise a track with SAS tech support to get to the bottom of this issue - even though it's likely not on the SAS side.

Re: SAS upload to Voyager/TRINO is very slow

michal_1407 — Wed, 03 Jan 2024 13:05:44 GMT

yes, i agree with you. but know i try to find any additional SAS option to improve/solve this problem but without any success : /

Re: SAS upload to Voyager/TRINO is very slow

SASKiwi — Wed, 03 Jan 2024 21:27:50 GMT

Try adding the INSERTBUFF option to your database LIBNAME. I normally set this to at least 10,000 rows as default settings are normally way too slow. You can try different values to see what gives best performance. DBCOMMIT is another option worth trying.

Re: SAS upload to Voyager/TRINO is very slow

michal_1407 — Tue, 09 Jan 2024 08:03:27 GMT

I added this option but the execution time was similar, without any significant change : /

Re: SAS upload to Voyager/TRINO is very slow

andreas_lds — Tue, 09 Jan 2024 09:35:48 GMT

Maybe i am wrong, but

@michal_1407 wrote:

...

real time 13:08.07

cpu time 0.35 seconds

means, that sas needed 0.35 seconds to its job, but had to wait 13 minutes all in all so that other processes can complete their part of the task. With the tiny dataset used, i would exclude the network connection as possible bottleneck. Which SAS version is used? Older version used older version of Java, so maybe there is a problem in with the jdbc driver combined with a elderly Java version - just guessing.

Re: SAS upload to Voyager/TRINO is very slow

michal_1407 — Tue, 09 Jan 2024 09:54:17 GMT

we use SAS server. Yes, real time takes 13 minutes because using my code each row is inserted to EAP by TRINO row by row. So if I have 428 rows there will be 428 insert commands to EAP by TRINO and it is stupid because i dont know how to force SAS to do it in one INSERT. I use JDBC driver to connect.

AUTOMATIC SYSVER 9.4
AUTOMATIC SYSVLONG 9.04.01M7P080620
AUTOMATIC SYSVLONG4 9.04.01M7P08062020

libname libtest jdbc classpath='/opt/sas/JDBC3/'
class='io.trino.jdbc.TrinoDriver'

Re: SAS upload to Voyager/TRINO is very slow

Patrick — Tue, 09 Jan 2024 12:18:57 GMT

@michal_1407 wrote:

we use SAS server. Yes, real time takes 13 minutes because using my code each row is inserted to EAP by TRINO row by row. So if I have 428 rows there will be 428 insert commands to EAP by TRINO and it is stupid because i dont know how to force SAS to do it in one INSERT. I use JDBC driver to connect.

AUTOMATIC SYSVER 9.4
AUTOMATIC SYSVLONG 9.04.01M7P080620
AUTOMATIC SYSVLONG4 9.04.01M7P08062020

libname libtest jdbc classpath='/opt/sas/JDBC3/'
class='io.trino.jdbc.TrinoDriver'

The two libname options which allow you to insert more than one row at a time are dbcommit (set: dbcommit=0) and insertbuff

...but... for 428 rows also a insert/commit per row should take much much less than 13 minutes - unless this creates for your case every time a new connection with some latency.

Re: SAS upload to Voyager/TRINO is very slow

AhmedAl_Attar — Tue, 09 Jan 2024 12:51:01 GMT

Hi @michal_1407

I would suggest

Using the SASTRACE option to see what's going on while communicating with the Database

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

Reading this paper Accessing Your Favorite Database with SAS® and JDBC to see if it offers any tips.
Read the SAS/ACCESS Interface to JDBC Help Docs (Make sure you select the relevant SAS release docs)
- https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0zm6fjwtgsnrzn1fegvyhl3yrwd.htm
- https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1chghbbeu5k4sn1jh2jax39cgse.htm

Hope this helps,

Ahmed