BookmarkSubscribeRSS Feed
Imroze
Fluorite | Level 6

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

1 REPLY 1
Imroze
Fluorite | Level 6

hi guys,

any suggestions please.

thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 1 reply
  • 341 views
  • 0 likes
  • 1 in conversation