If you are using SAS^® Customer Intelligence Studio and selecting data that is joined with five or more tables, campaign results are generated after a much longer time than expected.

The issue occurs when all the following are true:

• Five or more tables are involved in the join.
• All the join criteria use the same column name.
• The first table's join column is not used in all join criteria.
• All the tables are in the same library.
• The library is a database management system (DBMS) library, like Oracle or Teradata.

The performance issue occurs because the entire join is not performed in the database, so more data is pulled back to SAS^® than expected. However, the final results are correct.

The only way to confirm that you are experiencing this issue is to enable the SASTRACE option, as shown here:

Additional messages are then written to the SAS log. The wording of the message that appears is dependent on the DBMS that is used. For example, with Oracle or Teradata, you might see a message that is similar to one of the following:

Note: If you use SAS Customer Intelligence Studio, you need to enable additional logging. Contact SAS Technical Support and reference this SAS Note to obtain more details.

This issue can occur with any SQL procedure query that joins five or more tables that are in a DBMS. In most cases, the query can be restructured to avoid the performance degradation. The following code syntax provides an example of how to restructure the query.

Here is the original query:

In this case, change the query so that all the left joins use "table1.client_id" on the left side of the join criteria:

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M6 is available at:

A fix for this issue for Base SAS 9.4_M5 is available at:

A fix for this issue for Base SAS 9.4_M4 is available at: