Code running slow in SAS 94

[char22]

Hi,

Just migrate to SAS 9.4 , and tested some similar Code which consume a lot more time to process in SAS 9.4

The Code are generated from SAS Data Integration.

There a slight different, in SAS 9.3 it has

%let SYS_SQL_IP_SPEEDO = Y;
%let SYS_SQL_MAPPUTTO = sas_put;
%let SYS_SQLREDUCEPUT = DBMS;

But , it automatically generated from SAS Data Integration

Try to query from work.WCUST90 view , it resulted differently in 9.3 and 9.4

The count command : select count(1) from work.WCUST90;

on SAS 9.3 consume 39 secs , while in SAS 9.4 it consume 9 hours!!!

(On SAS 9.4 it has some warning : "The execution of this query involves performing one or more Cartesian product joins that can not be optimized." while in 9.3, it has no warning)

Warning : the code quite long.

Please give attention on the end of code : select count(1) from work.WCUST90 , because that was the major difference

Attached files are the codes , and the logs.

Please help..

Thanks

Regards,

Charlie

[rogerjdeangelis]

I leave up to others especially SAS to figure out why it took longer.

Here is my summary of the code generator and it may not totally correct. I only spent about 20 minutes looking at the faster log.

Your data seems very small to me, no reason for 12hrs.

Th SAS code generators is relying
on sql views and in your case it looks like 80%
of the views are not needed. The generator does not
understand when to use a datastep.

I could be wrong but It looks like the last five long sql left join views can be combined into one
sql procedure. It looks like you are using left joins to add fields.
You should be able to optimize a single proc sql with multiple left joins.


Because of all the unnecessary code you are asking a lot
from the SAS interpreter. Perhaps SAS enhancements with 9.4 are the issue.

These code snippets very strange.



1045       proc sql;
1046          create table work.etls_mapped as
1047             select
1048                CFCIF_2
1049                   label = 'CFCIF#',
1050                CFASEQ,
1051                CFAPTY,
1052                CF2DT2,
1053                CF2PRV,
1054                CFSTSN
1055          from work.W5OB6DH5
1056          ;

seems circular

1061       data work.W5OB6DH5;
1062         set work.etls_mapped;
1063       run;

Generally this is not a good programming practice.

1248       data
1249            work.W5O7ZDVI
1250            work.W5O7ZDVS
1251            work.W5OB570T ;
1252          set &SYSLAST;
1253          output work.W5O7ZDVI;
1254          output work.W5O7ZDVS;
1255          output work.W5OB570T;
1256       run;


Multiple of these (substitute a keep and where)

replace with
    from or set  &syslast(keep cp.. where=(trim...='4'))

1343       proc sql;
1344          create view work.W5O7ZH2B as
1345             select
1346                CP3UCD
1347                   label = 'CP3UCD',
1348                CP3DSC
1349                   label = 'CP3DSC',
1350                cp3rid,
1351                cp3uic,
1352                cp3uid
1353          from &SYSLAST
1354             where trim(left(cp3uic)) = '4'
1355          ;



Auto code generation is a great marketing tactic.
Code generators do not comment code?

[char22]

Hi Roger,

Thanks for your insight..

I will try to modified the code

Thanks

[LinusH]

This quite a large job. You can't expect that the community users is happy scrolling through hundreds lines of code and log.
Please narrow down the problem, ideally to a single step.
This is the time wham (temporarily) change from views to tables on the transformations ouput.

[char22]

Hi Linus,

I update the post, so community user know which lines to check..

Thanks

[LinusH]

This note can be referring any of the preceding views.

Again, create work tables all the way to find the bottleneck.

[ballardw]

I don't recognize this:

ODS tagsets.sasreport13

If this is custom tagset you might need to update it for the new environment.

I would look into network traffic involving the component data sets of the work.WCUST90 view. Are all of the data sets in the same location as when running under 9.3 or on a different machine than the 9.4 install? You are nesting so many views that any small change in the data access is going to increase times drastically.

How has the size of those component sets changed?

[Patrick · Accepted solution]

I believe you've identified the correct log message you need to pay attention to:

"The execution of this query involves performing one or more Cartesian product"

This message gets generated in the select count step BUT as these are views the actual code creating the cartesion product sits somewhere else.

For debugging purposes:

Switch all the views over to tables in your DI job (that's just unchecking a check-box) and run your code. Now you'll really see where the long running step resides.

There can be multiple reasons why something isn't working anymore (like some metadata "lost" when importing the .spk into the new environment, or slow DB connection, I/O issues,....). These are the next steps you have to concentrate on once you've got actual runtimes with physical tables and not views.

[char22]

Hi Patrick,

This is quite weird, but, after changing the views to tables. It run normal again.

Thanks

[Patrick]

Then may be @rogerjdeangelis statement goes into the right direction:

"Because of all the unnecessary code you are asking a lot from the SAS interpreter. Perhaps SAS enhancements with 9.4 are the issue."

I've observed that if you combine "too many" views in a SQL the SAS SQL optimizer can get "confused" and select a very sub-optimal execution path. I could imagine it to be possible that some changes/optimizations to the SQL optimizer for 9.4 could also have a negative impact in certain cases.

What I normally try to do in DIS jobs is to keep the "small" tables physical and only use views for the "big" tables and only if it makes "sense".