Capturing Counts from destination SQL Server Database

jdmarshg — Tue, 22 Sep 2015 20:26:21 GMT

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

Re: Capturing Counts from destination SQL Server Database

hbi — Tue, 22 Sep 2015 21:33:30 GMT

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!

Re: Capturing Counts from destination SQL Server Database

jdmarshg — Sat, 26 Sep 2015 00:17:05 GMT

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

topic Re: Capturing Counts from destination SQL Server Database in SAS Data Management

Capturing Counts from destination SQL Server Database

Re: Capturing Counts from destination SQL Server Database

Re: Capturing Counts from destination SQL Server Database