Hi All,
Please find the below query for optimization.
proc sql ;
connect to db2 (ssid=&ssid) ;
create table &work..xyz as
select * from connection to db2
(select a.x123
, a.x124
, a.x125
, a.x126
, a.x127
, a.x128
.........
, upper(d.x129) as x129
, upper(d.x130) as x130
, d.x131
, case when b.x131 is null
then c.x131
else b.x131 end as x131
.............................../*more variables******/
from J.ATABLE a
left join J.BTABLE b
on a.X132 = b.X133
left join J.CTABLE0 c
on a.X134 = c.X135
left join J.FTABLE F
on a.X136 = f.X136
and a.X137 = f.X137
and a.X138 = f.X138
and a.X139 = f.X139
left join J.DTABLE d
on a.X136 = d.X136
and a.X137 = d.X137
and a.X138 = d.X138
and a.X139 = d.X139
and d.X140 <= &XYZ
and d.X141 >= &XYZ
, (select z.X142, z.X143
from J.ZTABLE z
left join J.YTABLE y
on z.X142 = y.X142
where z.X143 in ('3','4')
and z.X144 = '1'
and z.X145 <= &XYZ
and y.X146='10' and y.X147 = '9'
) e
where a.X141 <= &XYZ
and a.X147 >= &XYZ
and a.X148 = e.X149
and a.X150 = e.X151
&MORE_WHERE_BASED_ON_CONDITIONS /* online testen 'FORE' */
) ;
%put RC =&sqlxrc ;
%put MSG =&sqlxmsg ;
%put N =&sqlobs ;
disconnect from db2 ;
quit ;
Till know I have added indexes to all the variable's, which is either in on condition or in where condition. apart from suggestions on indexing is there any other way.
The table will create the data with more than 4 billion records.
Thank you
hi guys,
any suggestions please.
thank you
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.