Potential SQL Issues

Potential issues found in the SQL in your workloads appear in the Performance Issues region of the query details page when you click a query in the list on the Data Warehouse Queries page. Potential SQL issues are common mistakes made in writing SQL. All SQL issues that Workload Experience Manager (Workload XM) identifies are listed in the following table.

Potential SQL Issues Impact & Recommendation
>5 table joins or > 10 join conditions found. Possible performance impact depending on table sizes, partitioning keys, filter and join conditions that are specified in the query. To address this issue, denormalize tables to eliminate the need for joins.
>10 columns present in GROUP BY list. Possible performance impact, depending on the number of distinct groups and the memory configuration. This issue is not raised if the source platform is Impala. To address this issue, evaluate the memory requirements for the query.
>10 Inline Views present in query. Possible performance impact, depending on the memory configuration, especially if complex expressions are present in inline views on Impala. To address this issue, evaluate the memory requirements and materialize inline views.
>50 query blocks present in large query. Possible performance impact, depending on the memory configuration. To address this issue, evaluate the query memory requirements, split the query into smaller queries, and materialize duplicate blocks.
>2000 expressions found in WHERE clause of a single query. This is a hard limit enforced by Impala. The query fails if it contains >2000 expressions. To address this issue, consolidate expressions by replacing repetitive sequences with single operators like IN or BETWEEN.
Cartesian or CROSS join found. Performance impact if tables are large. To address this issue, rewrite the query to add join conditions and eliminate Cartesian joins.
High cardinality GROUP BY column found. Possible performance impact, depending on the number of distinct groups and the memory configuration. To address this issue, evaluate the memory requirements for the query.
Joins across large tables found. Possible performance impact, depending on the partitioning keys, filter and join conditions that are specified in the query. To determine the cause, evaluate the EXPLAIN output on Impala. To address this issue, evaluate the filter conditions, join conditions, and the memory requirements for the query and for possible table partitioning strategies.
Join on a large table found. Possible performance impact, depending on the partitioning keys, filter and join conditions that are specified in the query. To determine the cause, evaluate the EXPLAIN output on Hive or Impala. To address this issue, evaluate the filter conditions, the join conditions, the memory requirements for the query, and the possible table partitioning strategies.
Many single-row inserts found. Possible performance impact because singleton inserts create too many small files instead of creating fewer large files. To address this issue, batch inserts together to prevent creating too many small data files.
Popular CASE expression across queries found. Possible performance improvement. Consider materializing the CASE expression.
Popular filter conditions found. Possible performance impact if the tables are large and are not partitioned. To address this issue, evaluate the possible table partitioning strategies on these filter conditions.
Popular inline views across queries found. Possible performance impact, depending on the memory configuration, especially if complex expressions are used in inline views on Impala. To address this issue, consider materializing the inline view.
Popular subqueries across queries found. Possible performance improvement. Consider materializing the subqueries.
Query has no filters. Possible performance impact if the result set that is returned is very large. To address this issue, rewrite the query to add filtering conditions and to reduce the size of the result set that is returned.
Query on partitioned table is missing filters on partitioning columns. Possible performance impact if the tables are large. To address this issue, rewrite the query to add filtering conditions.
Query with filter conditions on a large table found. Possible performance impact if the tables are large and are not partitioned. To address this issue, evaluate the possible table partitioning strategies on these filter conditions.
Query with inline views found. Possible performance impact, depending on the memory configuration, especially if complex expressions are used in inline views on Impala. To address this issue, if the inline view is duplicated, evaluate whether materializing the inline view is advantageous.
Table might contain too many partitions (>30K). Can crash the Hive metastore. To address this issue, re-evaluate the partitioning key strategy. Queries that access many partitions might not complete.
Table might contain too many partitions (>50K). Can crash the Hive metastore. To address this issue, re-evaluate the partitioning key strategy. Queries that access many partitions might not complete.
Table might contain too many partitions (>100K). Can crash the Hive metastore. To address this issue, re-evaluate the partitioning key strategy. Queries that access many partitions might not complete.
Unsupported commands: UPDATE or DELETE. UPDATE and DELETE are not supported on CDH. These queries fail. To address this issue, rewrite the query to use views or partitioning strategies that mimic UPDATE and DELETE. Use of partition-based INSERT with OVERWRITE or using SELECT with views over main and delta tables are common workaround strategies.