SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Capturing Counts from destination SQL Server Database

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Capturing Counts from destination SQL Server Database

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


Accepted Solutions
Solution
‎09-25-2015 08:15 PM
Contributor hbi
Contributor
Posts: 66

Re: Capturing Counts from destination SQL Server Database

[ Edited ]

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


All Replies
Solution
‎09-25-2015 08:15 PM
Contributor hbi
Contributor
Posts: 66

Re: Capturing Counts from destination SQL Server Database

[ Edited ]

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! 

Contributor
Posts: 70

Re: Capturing Counts from destination SQL Server Database

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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