Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

How to Improve SQL Merges

If you are looking to improve performance of your SQL code consider the following options:

  • Include all your numeric, date or datetime variables first in the SELECT statement and leave the character ones for the end. Character variables are not padded when included last, meaning fewer I/O and lower CPU consumption.
  • Add the parameter _METHOD to your SQL code. It will write debugging messages to the log about the type of join used by the SQL optimizer.
    • sqxjm: sort-merge join. Algorithm most commonly used by the optimizer. First it will sort the data files and then perform the join. You can influence the optimizer to try to select this method by adding MAGIC=102 to your proc sql statement.
      proc sql _method magic=102;
    • sqxjndx: index join. Using indexes present in the data sets. You can try to influence the optimizer for this method by adding the data set option IDXWHERE=YES.
      from library.index_data (idxwhere=yes)
    • sqxjhsh: hash join. The smaller table in the join is loaded in memory. Influence the optimizer by increasing the default value of the 64K for the BUFFERSIZE option
      proc sql _method buffersize=1048576