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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.