BookmarkSubscribeRSS Feed
erez3231
Calcite | Level 5

i've written an sql query that has a run time of about 14 seconds when i run it on ms management studio, but as soon as i try to run the query via explicit sql pass-through in SAS EG or DI using ODBC the run time exceeds 20 (!!!) hours.

 

I've boiled down the issue to the buffer size option in the connection string, when the buffer size is set to zero, the execution time is about 14 to 17 seconds. on examination of the sas trace log i've noticed that when the buffer size is set to any number other then 0 the fetch phase of the odbc engine consumes about 99% of the long process time.

 

I'm inclined to leave the buffer at zero but i have a feeling the my SQL server DBA will be more then upset with that, as the sql server is writing one row at a time to SAS. unfortunetly OLE DB is not an option so i cant compare methods. any one has any idea about whats going on?

 

 

 

My code:

OPTIONS SASTRACE='D,,T,SA' SASTRACELOC = SASLOG NOSTSUFFIX; 
PROC SQL;
CONNECT TO ODBC (DEFER=YES READBUFF=0 INSERT_SQL=YES INSERTBUFF=32000 UPDATE_MULT_ROWS=YES AUTOCOMMIT=YES DATAsrc=AML AUTHDOMAIN="FCAMLFAPP Core"); CREATE TABLE work.REL_ACC AS SELECT * FROM CONNECTION TO ODBC ( SELECT LTRIM(RTRIM(ALL_EMPS.employee_number)) AS employee_number, BRDG.account_number, 'ASSOC_ACC' as ACC_TYPE, ACC.x_account_primary_branch_nbr as BRANCH_NUM_INT from FCALM_STG.ALL_EMPS as ALL_EMPS INNER JOIN FCALMCAPP.FSC_PARTY_ASSOC as ASSOC on ALL_EMPS.party_number = ASSOC.party_number AND ASSOC.change_current_ind = 'Y' INNER JOIN FCALMCAPP.X_FSC_PARTY_ACCOUNT_BRIDGE_BNHP as BRDG on ASSOC.related_party_number = BRDG.party_number AND BRDG.change_current_ind = 'Y' INNER JOIN FCALMCAPP.FSC_ACCOUNT_DIM as ACC on BRDG.account_number = ACC.account_number AND ACC.change_current_ind = 'Y' where ACC.account_status_desc = 'פעיל' AND ACC.x_account_primary_branch_nbr NOT IN (556,775) AND BRDG.role_key IN (1,2) ); DISCONNECT FROM ODBC; quit;
1 REPLY 1
kiranv_
Rhodochrosite | Level 12

 As far as I know there is no bulk load utility that move lot of rows from SQL server to SAS.

Try this, I am not sure whether it will work, create a permanent table in SQL server table(if you have rights to create) for your joins and then move that table to SAS in next step.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 818 views
  • 0 likes
  • 2 in conversation