BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jdmarshg
Obsidian | Level 7

Hello everyone I'm using SAS DI Studio. 

User written code captures counts form a SQL Server database while looping through files. 

During the first loop for some reason I'm getting a count of 0 after data is loaded to a table. 

It seems as if the count is capturing the state of the table before it is completed.

Is there a way to pause between before running a proc sql statement?

 

SAS is installed on Linux. 

Any insight would be greatly appreciated as this is very frustrating as to why when I run the SAS base code within the code editor i get the proper count back but the package for some reason returns 0.

 

Regards,

Jonathan

Jon

1 ACCEPTED SOLUTION

Accepted Solutions
hbi
Quartz | Level 8 hbi
Quartz | Level 8

Hi,

 

This sounds like a mysterious problem. My first thought is that you might consider tweaking "insertbuff" and "dbcommit" parameters to a tiny value (see tests 1, 2 and 3). This is not a good long-term solution (because it hurts performance), but it might get you closer to pinpointing whether the problem is perhaps due to certain kinds of concurrency.

 

Test 4 (sleeping for 10 seconds) would be an unusual coding technique, but it does address your desire to "pause" the program.

 

/* TEST 1 - decrease insertbuff and dbcommit to a very small value */ 
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=50 dbcommit=50;
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;

  SELECT 'test1', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 2 - same as test 1, but move the count to a separate PROC SQL statement */ 
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;
QUIT;

PROC SQL;
  SELECT 'test2', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 3 - see if PROC APPEND produces makes a difference */ 
PROC APPEND BASE=MSSQL.test_table DATA=dataset_with_lots_of_rows; RUN;

PROC SQL;
  SELECT 'test3', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 4 - introduce 10 seconds of sleep */ 
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;
QUIT;

DATA work.sleep_briefly(DROP=zzz);
    %LET the_time = %SYSFUNC(TIME(),TIME.);
    SYSECHO "Sleeping for 10 seconds at ... &the_time";
    zzz = sleep(10,1); /* sleep 10 seconds; 1 means use seconds */
RUN;

PROC SQL;
  SELECT 'test4', COUNT(*) FROM MSSQL.test_table;
QUIT;

Test 5 is a last resort because committing one row at a time will really hurt performance. Test 6 is something to try if you have pinpointed which of the tests produce desirable results, at which point, you can tweak dbcommit and insertbuff to restore database performance to levels that you can live with. 

 

/* TEST 5 - if the problem persists, try even smaller values and add INSERT_SQL=NO */
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=1 dbcommit=1 INSERT_SQL=NO;
/* repeat tests 1 to 4 and see what happens */


/* TEST 6 - increase insertbuff and dbcommit values that will restore decent 
   performance and see if problem still persists with larger values; 
   make sure insertbuff is a value less than dbcommit */
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=100 dbcommit=1000;
/* repeat tests 1 to 4 and see what happens */

 

 

Would love to hear feedback as to whether any of these techniques worked! 

View solution in original post

2 REPLIES 2
hbi
Quartz | Level 8 hbi
Quartz | Level 8

Hi,

 

This sounds like a mysterious problem. My first thought is that you might consider tweaking "insertbuff" and "dbcommit" parameters to a tiny value (see tests 1, 2 and 3). This is not a good long-term solution (because it hurts performance), but it might get you closer to pinpointing whether the problem is perhaps due to certain kinds of concurrency.

 

Test 4 (sleeping for 10 seconds) would be an unusual coding technique, but it does address your desire to "pause" the program.

 

/* TEST 1 - decrease insertbuff and dbcommit to a very small value */ 
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=50 dbcommit=50;
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;

  SELECT 'test1', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 2 - same as test 1, but move the count to a separate PROC SQL statement */ 
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;
QUIT;

PROC SQL;
  SELECT 'test2', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 3 - see if PROC APPEND produces makes a difference */ 
PROC APPEND BASE=MSSQL.test_table DATA=dataset_with_lots_of_rows; RUN;

PROC SQL;
  SELECT 'test3', COUNT(*) FROM MSSQL.test_table;
QUIT;


/* TEST 4 - introduce 10 seconds of sleep */ 
PROC SQL;
  INSERT INTO MSSQL.test_table
  SELECT * 
  FROM dataset_with_lots_of_rows;
QUIT;

DATA work.sleep_briefly(DROP=zzz);
    %LET the_time = %SYSFUNC(TIME(),TIME.);
    SYSECHO "Sleeping for 10 seconds at ... &the_time";
    zzz = sleep(10,1); /* sleep 10 seconds; 1 means use seconds */
RUN;

PROC SQL;
  SELECT 'test4', COUNT(*) FROM MSSQL.test_table;
QUIT;

Test 5 is a last resort because committing one row at a time will really hurt performance. Test 6 is something to try if you have pinpointed which of the tests produce desirable results, at which point, you can tweak dbcommit and insertbuff to restore database performance to levels that you can live with. 

 

/* TEST 5 - if the problem persists, try even smaller values and add INSERT_SQL=NO */
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=1 dbcommit=1 INSERT_SQL=NO;
/* repeat tests 1 to 4 and see what happens */


/* TEST 6 - increase insertbuff and dbcommit values that will restore decent 
   performance and see if problem still persists with larger values; 
   make sure insertbuff is a value less than dbcommit */
LIBNAME MSSQL sqlsvr user=myusr1 password=mypwd1 insertbuff=100 dbcommit=1000;
/* repeat tests 1 to 4 and see what happens */

 

 

Would love to hear feedback as to whether any of these techniques worked! 

jdmarshg
Obsidian | Level 7

Thank you as it was all timing so the sleep gave the SQL Server enough time to return the correct value. 

I also tweeked the insert and commit buffer to lower numbers.

Now it is capturing the correct value. 

 

Much appreciated. 

 

Jonathan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 915 views
  • 0 likes
  • 2 in conversation