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
28
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
35
36 ;
NOTE: Libref LIBTEST was successfully assigned as follows:
Engine: JDBC
37
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
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.
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.
I added this option but the execution time was similar, without any significant change : /
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.
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'
@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.
Hi @michal_1407
I would suggest
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
Hope this helps,
Ahmed
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: