Técnicas para mejorar el rendimiento de las consultas SQL en espacios de trabajo de la capa de servicios de datos
- Optimización de READ SQL general para DB2
- Optimizar consultas basadas en las directrices de optimización de la consulta
- Insertar predicados en la cláusula OUTER JOIN siempre que sea posible
- Duplicar la condición de constante para diferentes tablas siempre que sea posible
- Utilizar definiciones de tabla anidada para sustituir vistas de espacios de trabajo
- División de consultas
Utilizar sintaxis de expresiones regulares en Oracle
Optimización READ SQL general para DB2
y Oracle
Las reglas parecen conseguir un mejor tiempo de respuesta en DB2.
Optimizar consultas basadas en las directrices de optimización de la consulta
- Indexe todos los predicados en cláusulas JOIN, WHERE, ORDER BY y GROUP BY.
HCL Commerce suele depender en gran medida de los índices para mejorar el rendimiento y la escalabilidad de SQL. Sin los índices adecuados, las consultas SQL pueden causar exploraciones de tabla, lo que provoca problemas de rendimiento o de bloqueo. Se recomienda indexar todas las columnas de predicados. La excepción es cuando los datos de columna tienen una cardinalidad muy baja.
- Evite el uso de funciones en los predicados.La base de datos no utiliza el índice si hay una función en la columna. Por ejemplo:
Como resultado de la función UPPER(), los optimizadores de base de datos no utilizan el índice en COL1.SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'
Si la función se puede evitar en el SQL, es necesario crear un índice basado en función en Oracle o columnas generadas en DB2 para mejorar el rendimiento.
- Evite el uso del carácter comodín (%) al principio de un predicado.El predicado
LIKE '%abc'
produce una exploración de tabla completa. Por ejemplo:SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'
Esta es una limitación conocida en todas las bases de datos.
- Evite columnas innecesarias en la cláusula SELECT.
Especifique las columnas en la cláusula
SELECT
en lugar de utilizarSELECT *
. Las columnas innecesarias imponen cargas adicionales en la base de datos, lo que ralentiza no solo el SQL específico, sino todo el sistema. - Utilice la unión interna en lugar de la unión externa, si es posible.
La unión externa solo debe utilizarse si es necesario. La utilización de la unión externa limita las opciones de optimización de la base de datos, lo que suele dar como resultado una ejecución más lenta del SQL.
- DISTINCT y UNION solo deben utilizarse si es necesario.
Los operadores DISTINCT y UNION causan la operación de clasificación, lo que ralentiza la ejecución de SQL. Utilice UNION ALL en lugar de UNION, si es posible, ya que es mucho más eficaz.
Oracle 10g y 11g requiere que las columnas CLOB/BLOB se coloquen al final de las sentencias.
De lo contrario, se produce un error cuando el tamaño del valor de entrada tiene más de 1000 caracteres.
- La cláusula ORDER BY es obligatoria en SQL si el conjunto de resultados clasificados es el previsto.
La palabra clave ORDER BY se utiliza para clasificar el conjunto de resultados por las columnas especificadas. Sin la cláusula ORDER BY, el conjunto de resultados se devuelve directamente sin ninguna clasificación. El orden no queda garantizado. Tenga en cuenta el impacto del rendimiento que supone añadir la cláusula ORDER BY, ya que la base de datos necesita clasificar el conjunto de resultados, lo que se convierte en una de las operaciones más costosas de la ejecución de SQL.
Insertar predicados en la cláusula OUTER JOIN siempre que sea posible
Para consultas SQL con el operador LEFT OUTER JOIN, trasladar predicados de la tabla derecha de la cláusula WHERE a la condición ON ayuda al optimizador de base de datos a generar una consulta más eficaz. Los predicados de la tabla izquierda pueden permanecer, en la cláusula WHERE.
Del mismo modo, para las consultas SQL con el operador RIGHT OUTER JOIN, los predicados de la tabla derecha deberían trasladarse de la cláusula WHERE a la condición ON.
Por ejemplo, la consulta subóptima se reescribe insertando los predicados aplicables a la tabla TAB_B en la cláusula ON. Los predicados específicos de TAB_A en la cláusula WHERE pueden o bien permanecer o bien insertarse en la cláusula ON:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1=123 AND TAB_B.COL2=456;
Sentencia SQL optimizada:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2=456 WHERE TAB_A.COL1=123;
Los predicados de las uniones INNER pueden permanecer en la cláusula WHERE. Si las tablas TAB_A y TAB_B se definen como vistas, el optimizador puede insertar estos predicados en las vistas.
Duplicar la condición de constante para diferentes tablas siempre que sea posible
Cuando dos tablas, A y B, se unen y hay un predicado constante en una de las columnas unidas, por ejemplo, A.id=B.id y A.id in (10, 12), el predicado constante debería duplicarse para la columna unida de la segunda tabla. Es decir, A.id=B.id y A.id en (10, 12) y B.id en (10, 12).
Por ejemplo, TAB_A tiene una relación de unión externa izquierda con TAB_B. Por ejemplo, TAB_A tiene una condición de tabla cruzada con TAB_B, cree una condición específica de TAB_B adicional basada en el requisito de TAB_A y mantenga las condiciones de tabla cruzada en la cláusula ON:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1;
Sentencia SQL optimizada:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1 WHERE TAB_A.COL1 IN (123, 456);
En especial, si el predicado constante solo tiene el valor 1 (es decir, COL1=123), el segundo predicado también debe convertirse a un predicado constante.
Por ejemplo:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1=123 AND TAB_B.COL2=TAB_A.COL1;
Sentencia SQL optimizada:
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2=123 WHERE TAB_A.COL1=123;
Utilizar definiciones de tabla anidada para sustituir vistas de espacios de trabajo
SELECT
CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID,
ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUE
FROM CATENTRY, ATTRVALUE, ATTRIBUTE
WHERE
ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND CATENTRY.CATENTRY_ID IN (10683)
AND ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 )
AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID
ORDER BY
ATTRIBUTE.SEQUENCE
SELECT
CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID,
ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUE
FROM (
SELECT
CATENTRY_ID
FROM
DB2INST1.CATENTRY
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.CATENTRY
WHERE DB2INST1.CATENTRY.CATENTRY_ID = WCW101.CATENTRY.CATENTRY_ID)
UNION ALL
SELECT
CATENTRY_ID
FROM
WCW101.CATENTRY
WHERE WCW101.CATENTRY.CONTENT_STATUS <> 'D'
) CATENTRY,
(
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
DB2INST1.ATTRVALUE
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.ATTRVALUE
WHERE DB2INST1.ATTRVALUE.ATTRVALUE_ID = WCW101.ATTRVALUE.ATTRVALUE_ID)
UNION ALL
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
WCW101.ATTRVALUE
WHERE WCW101.ATTRVALUE.CONTENT_STATUS <> 'D'
) ATTRVALUE,
(
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
DB2INST1.ATTRIBUTE
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.ATTRIBUTE
WHERE DB2INST1.ATTRIBUTE.ATTRIBUTE_ID = WCW101.ATTRIBUTE.ATTRIBUTE_ID)
UNION ALL
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
WCW101.ATTRIBUTE
WHERE WCW101.ATTRIBUTE.CONTENT_STATUS <> 'D'
) ATTRIBUTE
WHERE
ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND CATENTRY.CATENTRY_ID IN (10683)
AND ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 )
AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID
ORDER BY
ATTRIBUTE.SEQUENCE
LANGUAGE_ID IN ( -1,-2,-3,-4,-5 )
se incluye en la definición de la tabla anidada ATTRVALUE en la consulta siguiente:
SELECT
CATENTRY.CATENTRY_ID, ATTRIBUTE.ATTRIBUTE_ID, ATTRIBUTE.NAME, ATTRIBUTE.LANGUAGE_ID, ATTRIBUTE.CATENTRY_ID,
ATTRVALUE.ATTRVALUE_ID, ATTRVALUE.ATTRIBUTE_ID, ATTRVALUE.CATENTRY_ID, ATTRVALUE.STRINGVALUE
FROM (
SELECT
CATENTRY_ID
FROM
DB2INST1.CATENTRY
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.CATENTRY
WHERE DB2INST1.CATENTRY.CATENTRY_ID = WCW101.CATENTRY.CATENTRY_ID)
UNION ALL
SELECT
CATENTRY_ID
FROM
WCW101.CATENTRY
WHERE WCW101.CATENTRY.CONTENT_STATUS <> 'D'
) CATENTRY,
(
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
DB2INST1.ATTRVALUE
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.ATTRVALUE
WHERE DB2INST1.ATTRVALUE.ATTRVALUE_ID = WCW101.ATTRVALUE.ATTRVALUE_ID)
AND DB2INST1.ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 )
UNION ALL
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
WCW101.ATTRVALUE
WHERE WCW101.ATTRVALUE.CONTENT_STATUS <> 'D'
AND WCW101.ATTRVALUE.LANGUAGE_ID IN ( -1,-2,-3,-4,-5 )
) ATTRVALUE,
(
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
DB2INST1.ATTRIBUTE
WHERE NOT EXISTS (
SELECT '1'
FROM WCW101.ATTRIBUTE
WHERE DB2INST1.ATTRIBUTE.ATTRIBUTE_ID = WCW101.ATTRIBUTE.ATTRIBUTE_ID)
UNION ALL
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
WCW101.ATTRIBUTE
WHERE WCW101.ATTRIBUTE.CONTENT_STATUS <> 'D'
) ATTRIBUTE
WHERE
ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND CATENTRY.CATENTRY_ID IN (10683)
AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID
ORDER BY
ATTRIBUTE.SEQUENCE
$CM:READ$
, $CM:WRITE$
y $CM:BASE$
. Estos códigos se sustituirán en tiempo de ejecución por los nombres de esquema READ, WRITE y BASE cuando la Capa de servicios de datos ejecute el SQL.Consulte Modelo de datos de espacios de trabajo para obtener más información.
BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[CatalogEntryIdentifier[(UniqueID=)]]+IBM_Attributes
base_table=CATENTRY
sql=
SELECT CATENTRY.$COLS:CATENTRY_ID$, ATTRIBUTE.$COLS:ATTRIBUTE$,ATTRVALUE.$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE, ATTRIBUTE
WHERE
ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND CATENTRY.CATENTRY_ID IN (?UniqueID?)
AND ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$)
AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID
ORDER BY
ATTRIBUTE.SEQUENCE
<!-- the following query is optimized for workspaces -->
cm
sql=
SELECT CATENTRY.$COLS:CATENTRY_ID$, ATTRIBUTE.$COLS:ATTRIBUTE$,ATTRVALUE.$COLS:ATTRVALUE$
FROM (
SELECT
CATENTRY_ID
FROM
$CM:BASE$.CATENTRY
WHERE NOT EXISTS (
SELECT '1'
FROM $CM:WRITE$.CATENTRY
WHERE $CM:BASE$.CATENTRY.CATENTRY_ID = $CM:WRITE$.CATENTRY.CATENTRY_ID)
UNION ALL
SELECT
CATENTRY_ID
FROM
$CM:WRITE$.CATENTRY
WHERE $CM:WRITE$.CATENTRY.CONTENT_STATUS <> 'D'
) CATENTRY,
(
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
$CM:BASE$.ATTRVALUE
WHERE NOT EXISTS (
SELECT '1'
FROM $CM:WRITE$.ATTRVALUE
WHERE $CM:BASE$.ATTRVALUE.ATTRVALUE_ID = $CM:WRITE$.ATTRVALUE.ATTRVALUE_ID)
AND $CM:BASE$.ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$)
UNION ALL
SELECT
ATTRVALUE_ID,LANGUAGE_ID,CATENTRY_ID,ATTRIBUTE_ID,STRINGVALUE
FROM
$CM:WRITE$.ATTRVALUE
WHERE $CM:WRITE$.ATTRVALUE.CONTENT_STATUS <> 'D'
AND $CM:WRITE$.ATTRVALUE.LANGUAGE_ID IN ($CONTROL:LANGUAGES$)
) ATTRVALUE,
(
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
$CM:BASE$.ATTRIBUTE
WHERE NOT EXISTS (
SELECT '1'
FROM $CM:WRITE$.ATTRIBUTE
WHERE $CM:BASE$.ATTRIBUTE.ATTRIBUTE_ID = $CM:WRITE$.ATTRIBUTE.ATTRIBUTE_ID)
UNION ALL
SELECT
ATTRIBUTE_ID,LANGUAGE_ID,CATENTRY_ID,SEQUENCE,NAME
FROM
$CM:WRITE$.ATTRIBUTE
WHERE $CM:WRITE$.ATTRIBUTE.CONTENT_STATUS <> 'D'
) ATTRIBUTE
WHERE
ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND CATENTRY.CATENTRY_ID IN (?UniqueID?)
AND ATTRIBUTE.LANGUAGE_ID=ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID
ORDER BY
ATTRIBUTE.SEQUENCE
END_XPATH_TO_SQL_STATEMENT
División de consultas
Si la aplicación de estas técnicas no ofreció las mejoras de rendimiento necesarias, podría considerar la posibilidad de dividir consultas que unen muchas tablas en varias consultas. El objetivo de la división de la consulta es minimizar el número de vistas empleadas en la unión. Si va a dividir una consulta de un solo paso, tendrá que convertirla en una consulta de dos pasos con una o más consultas de asociación. Al dividir una de las sentencias SQL de asociación para una consulta de dos pasos, debe añadir sentencias SQL de asociación adicionales a la definición del perfil de acceso. Cuando se dividan las consultas, deberá utilizar uniones internas en vez de uniones externas, siempre que sea posible.
Extreme las precauciones al dividir las consultas. Si sigue este método de forma demasiado exhaustiva, puede dar lugar a numerosas consultas, cada una de ellas seleccionando de una sola tabla. En este caso, tendrá que realizar uniones de tabla en la memoria escribiendo código Java, por ejemplo, utilizando un compositor gráfico. En algunos casos, deberá introducir el resultado de una consulta en otra. Estos tipos de consultas solo deben considerarse como último recurso, ya que pueden surgir problemas de mantenimiento, personalización y migración.
![Oracle](../../base/images/ngoracle.gif)
Utilizar sintaxis de expresiones regulares en Oracle
Oracle optimizer normalmente genera consultas muy eficaces, en las que se crea una tabla temporal basada en el conjunto de resultados de las vistas de uniones de consulta originales. Una tabla temporal se puede crear utilizando una sintaxis de expresión de tabla regular.
Por ejemplo, la siguiente consulta que capta valores de atributo de producto puede ejecutarse mucho más rápido después de reescribirla utilizando la sintaxis de expresión de tabla regular:
BEGIN_ASSOCIATION_SQL_STATEMENT
name=IBM_CatalogEntryAttributeValue
base_table=CATENTRY
sql =
SELECT
CATENTRY.$COLS:CATENTRY$, ATTRVALUE.$COLS:ATTRVALUE$,
ATTRVALUE2.$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE
JOIN ATTRIBUTE
ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2
ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID
AND ATTRVALUE2.CATENTRY_ID = 0
AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID
WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$)
AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$)
END_ASSOCIATION_SQL_STATEMENT
Utilizando la sintaxis de expresiones regulares:
BEGIN_ASSOCIATION_SQL_STATEMENT
name=IBM_CatalogEntryAttributeValue
base_table=CATENTRY
sql =
WITH TEMP_TABLE AS (
SELECT
CATENTRY.CE_$COLS:CATENTRY$, ATTRVALUE.ATTR_$COLS:ATTRVALUE$,
ATTRVALUE2.ATTR2_$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE
JOIN ATTRIBUTE
ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID
AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2
ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID
AND ATTRVALUE2.CATENTRY_ID = 0
AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID
WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$)
AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID
AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$)
) SELECT * FROM TEMP_TABLE
END_ASSOCIATION_SQL_STATEMENT
Esta técnica permite que el optimizador de Oracle inserte los predicados necesarios en las vistas. A medida que se filtran más datos en las primeras etapas del proceso de consultas, las uniones posteriores deberán aplicarse a un juego de datos mucho más pequeño. A menudo esto supone mejoras de rendimiento significativas.