T3.3: Uses SQL with InterSystems IRIS

Resumen

SQL Embedded y SQL Dinámico representan dos enfoques fundamentales para ejecutar SQL en InterSystems IRIS.

SQL Embedded

• Sintaxis: Usa la sintaxis &sql()
• Compilación: Compilado en tiempo de compilación de clase, generando código ObjectScript in-line para máxima eficiencia de ejecución
• Validación: Compilador valida sintaxis y verifica existencia de tabla en tiempo de compilación, permitiendo detección temprana de error
• Verificación de Privilegio: NO realiza verificación de privilegio; asume que aplicaciones manejan autorización antes de ejecutar consultas

SQL Dinámico

• Implementación: A través de la clase %SQL.Statement, prepara y ejecuta consultas en runtime
• Similar A: Programación ODBC o JDBC pero ejecuta dentro del mismo contexto de proceso que el motor de base de datos
• Validación en Tiempo de Compilación: No puede validar en tiempo de compilación; macros de preprocesador no pueden usarse dentro de ellas
• Ventaja: Flexibilidad para construir consultas especializadas basadas en entrada de usuario o condiciones de runtime

Comparación de Rendimiento

• Consultas en Caché: Ambos enfoques soportan consultas en caché para rendimiento de re-ejecución mejorado
• Ejecución Inicial: SQL dinámico ligeramente menos eficiente debido a falta de generación de código in-line
• Ejecuciones Subsecuentes: Ambos se benefician de caching de consulta

Comportamiento de Verificación de Privilegio

• SQL Dinámico: Aplica verificación de privilegio SQL para interfaces ODBC, JDBC, y SQL Shell
• SQL Embedded: Evita verificaciones de privilegio enteramente

Manejo de Parámetro

• SQL Dinámico: Acepta valores de entrada literal a través de placeholders ? y variables host :var
• SQL Embedded: Usa variables host de entrada y salida exclusivamente (sintaxis :var)

Cuándo Usar Cada Enfoque

• SQL Embedded: Cuando consultas son conocidas en tiempo de compilación
• SQL Dinámico: Cuando consultas necesitan construcción en runtime

Uso Interno

SQL dinámico es usado internamente por el SQL Shell, interfaz Execute Query del Management Portal, y utilidades de importar/exportar datos.

Resumen

InterSystems IRIS proporciona varias extensiones SQL más allá de estándares SQL-92 que aprovechan su arquitectura orientada a objetos y motor de base de datos multidimensional.

Sintaxis Arrow (->)

• Propósito: Habilita joins implícitos navegando referencias de objeto directamente en consultas SQL
• Ejemplo: "SELECT Name, Company->Name FROM Sample.Employee" recupera nombres de empleado y nombres de su compañía sin JOIN explícito
• Navegación Compuesta: Soporta referencias encadenadas como Film->Category->CategoryName
• Tablas Hijo: También funciona con referencias de tabla hijo
• Requisitos de Privilegio: Requiere privilegios SELECT sobre datos referenciados en ambas tablas, incluyendo privilegios a nivel de columna sobre el campo ID de tablas referenciadas

Cláusula TOP

• Propósito: Controla tamaño de conjunto de resultado y proporciona beneficios de optimización de consulta
• Cambio de Optimización: Cuando se combina con ORDER BY, cambia optimización de "tiempo más rápido para retornar todos los datos" a "tiempo más rápido para retornar primera fila"
• Ideal Para: Displays paginados
• Valores Aceptados: Literales enteros, parámetros de entrada SQL dinámico (?), o variables host SQL embedded (:var)
• TOP ALL: Permite ORDER BY en subconsultas y sentencias CREATE VIEW sin restringir conteo de fila
• Comportamiento de Caching: Valores TOP sin paréntesis se cachean como variables de parámetro, permitiendo reutilización de consulta con diferentes límites sin re-preparación

Integración de Tecnología de Objeto

InterSystems IRIS integra SQL estrechamente con su tecnología de objeto, permitiendo acceso relacional y objeto sin sacrificar rendimiento.

Características Adicionales Específicas de IRIS

• Indexación Bitmap: Optimizada para aplicaciones de almacenamiento de datos, proporcionando rendimiento excepcional para consultas analíticas complejas
• Comando %CHECKPRIV: Habilita verificación de privilegio programática antes de ejecutar consultas

```sql -- Ejemplo de %CHECKPRIV: SELECT %CHECKPRIV('SELECT') FROM Sample.Person -- Retorna 1 si el usuario tiene privilegio SELECT, 0 de lo contrario ```

• TUNE TABLE: Recopila estadísticas de tabla (selectividad, tamaño de extent, tamaño de map) para el optimizador de consulta
• Sin Compresión de Tabla: A diferencia de bases de datos relacionales tradicionales, aplicaciones IRIS no requieren compresión de tabla periódica en entornos desplegados

Resumen

Planes de consulta son herramientas esenciales para comprender cómo InterSystems IRIS ejecuta sentencias SQL e identificar oportunidades de optimización.

Característica Show Plan

• Propósito: Muestra la estrategia de ejecución de consulta sin realmente ejecutar la consulta
• Uso: En interfaz SQL del Management Portal, ingresar una consulta y hacer clic en "Show Plan"
• Validación: Valida sintaxis, verifica existencia de entidad, y ve la estrategia de ejecución planeada
• SQL Dinámico: Establecer executemode=deferred, emitir la consulta, luego usar SHOW STATEMENT para mostrar la sentencia preparada y plan de ejecución sin consumir recursos de ejecución

Información Revelada por Planes de Consulta

• Métodos de Acceso a Tabla: Escaneo completo de tabla vs. escaneo de índice
• Decisiones de Selección de Índice: Qué índices el optimizador eligió
• Estrategias de Join: Nested loop, hash join, merge join
• Conteos de Fila Estimados: En cada paso del plan
• Orden de Operaciones: Qué tablas son accedidas primero y cómo resultados intermedios son procesados

Valores de Selectividad

• Definición: Porcentaje de filas esperadas satisfacer cada condición
• Interpretación: Valores de selectividad más bajos indican condiciones más selectivas que filtran más datos
• Origen: El optimizador usa estadísticas de tabla recopiladas por TUNE TABLE para estimar estos valores con precisión

Interpretación de Indicadores Clave

• Escaneos Completos de Tabla: Aparecen cuando no existe índice adecuado o cuando escanear es más eficiente que búsquedas de índice para condiciones de alta selectividad
• Uso de Índice: Muestra como "Index Used: indexname" en el plan
• Estimaciones de Costo: Ayudan a comparar formulaciones de consulta alternativas

Validación SQL Embedded

• Verificación de Sintaxis: Show Plan verifica sintaxis y verifica que entidades referenciadas existan
• Reporte de Error: Emite errores SQLCODE apropiados si problemas son detectados
• Cláusula INTO: Show Plan no errará en cláusulas INTO faltantes en SQL embedded ya que pueden aparecer en sentencias FETCH

Comando SHOW STATEMENT

La interfaz %SYSTEM.SQL.Shell() proporciona el comando SHOW STATEMENT para mostrar sentencias preparadas junto con sus planes de ejecución, habilitando refinamiento y optimización de consulta iterativo.

Resumen

Estadísticas SQL Statement Index proporcionan visibilidad en patrones de ejecución de consulta y características de rendimiento a través del sistema.

Comando SHOW STATEMENT

• Propósito: Muestra información sobre sentencias SQL preparadas en el SQL Shell
• Información Mostrada: Texto de sentencia, estado de preparación, y metadata de ejecución
• Valor: Particularmente valioso para identificar cuellos de botella de rendimiento y comprender qué consultas consumen más recursos
• Uso: Ejecutar una consulta, luego emitir SHOW STATEMENT para ver información detallada sin re-ejecutar
• Modo Deferred: Establecer executemode=deferred permite preparar consultas y examinar sentencias sin incurrir costos de ejecución

Métricas de Rendimiento Rastreadas

• Frecuencia de Ejecución: Cuántas veces una consulta ha sido ejecutada
• Tiempo Total de Ejecución: Tiempo acumulativo a través de todas las ejecuciones
• Tiempo Promedio de Ejecución: Tiempo de ejecución por ejecución
• Conteos de Fila: Número de filas retornadas

Análisis de Utilización de Índice

• Visibilidad de Uso de Índice: Estadísticas revelan qué índices están siendo utilizados durante ejecución de consulta
• Identificación de Índice No Usado: Encontrar índices que desperdician almacenamiento y sobrecarga de mantenimiento
• Detección de Índice Faltante: Identificar dónde nuevos índices podrían mejorar rendimiento de consulta

Ajuste de Rendimiento Continuo

• Detección de Degradación de Rendimiento: Analizar patrones de ejecución a través del tiempo
• Impacto de Crecimiento de Datos: Identificar consultas afectadas por crecimiento de datos
• Validación de Optimización: Validar la efectividad de esfuerzos de optimización

Análisis de Consulta en Caché

• Mecanismo: InterSystems IRIS almacena sentencias preparadas para reutilización
• Utilización de Caché: Estadísticas de sentencia ayudan a comprender utilización de caché
• Oportunidades de Optimización: Consultas preparadas repetidamente que podrían compartir sentencias en caché

Decisiones Basadas en Datos

Comprender estadísticas de sentencia permite a desarrolladores tomar decisiones informadas sobre:

• Creación de Índice: Dónde agregar nuevos índices
• Reformulación de Consulta: Cómo reescribir consultas ineficientes
• Diseño de Esquema: Cambios para mejorar rendimiento del sistema general

Estadísticas de tabla son fundamentales para optimización de consulta en InterSystems IRIS. El comando TUNE TABLE recopila estadísticas críticas basadas en datos de tabla actuales, calculando tamaño de map, tamaño de extent, y selectividad para cada campo. Estas estadísticas deben basarse en datos representativos que reflejan la distribución esperada en producción. TUNE TABLE analiza los datos y establece valores que el optimizador de consulta usa para estimar costos y elegir planes de ejecución óptimos. El optimizador se basa fuertemente en valores de selectividad para determinar qué índices usar, qué tabla acceder primero en joins, y si usar escaneos completos de tabla o búsquedas de índice.

Cuando TUNE TABLE ejecuta, actualiza tanto la definición de tabla SQL como típicamente la definición de clase persistente correspondiente, permitiendo que estadísticas recopiladas sean usadas sin requerir recompilación de clase. Para clases desplegadas, TUNE TABLE actualiza solo la definición de tabla SQL, y el optimizador accede estadísticas indirectamente. Si estadísticas cambian, TUNE TABLE automáticamente recompila todas las consultas en caché que referencian la tabla, asegurando que usen valores actualizados. Sin embargo, si ejecutar TUNE TABLE no cambia ningún valor (por ejemplo, cuando distribución de datos no ha cambiado), consultas en caché no son purgadas y clases no son marcadas para recompilación, evitando sobrecarga innecesaria.

TUNE TABLE ofrece varias opciones para ajuste fino del proceso de recopilación. La opción %SAMPLE_PERCENT especifica qué porcentaje de filas de tabla muestrear, útil cuando valores atípicos no están distribuidos uniformemente. Para tablas con menos de 1000 filas, TUNE TABLE siempre muestrea el extent entero independientemente de esta configuración. La opción %CLEAR_VALUES remueve estadísticas existentes de definiciones de clase y tabla. TUNE TABLE requiere privilegio administrativo %ALTER_TABLE y privilegio %ALTER sobre la tabla específica. Propietarios de tabla automáticamente tienen privilegio %ALTER. El comando puede ejecutarse vía SQL (TUNE TABLE tablename), menú Actions de interfaz SQL del Management Portal, o programáticamente usando $SYSTEM.SQL.Stats.Table.GatherTableStats(). Mejores prácticas incluyen ejecutar TUNE TABLE después de cargas de datos significativas, cuando rendimiento de consulta degrada inesperadamente, después de cambios de esquema que afectan distribución de datos, y periódicamente en entornos de producción para mantener estadísticas precisas conforme datos evolucionan.

InterSystems IRIS implementa seguridad SQL completa que complementa seguridad a nivel de sistema con protecciones granulares adicionales. Privilegios SQL proporcionan control de acceso a nivel de tabla y columna más allá de protecciones a nivel de base de datos. Una distinción crítica es que privilegios SQL pueden otorgarse directamente a usuarios o a roles, mientras privilegios a nivel de sistema son asignados solo a roles. Retener un privilegio SQL otorga implícitamente privilegios de sistema relacionados requeridos para realizar la acción SQL, pero el reverso no es cierto: privilegios a nivel de sistema no implican privilegios a nivel de tabla. Esta separación permite control preciso sobre patrones de acceso a datos.

Verificación de privilegio SQL es aplicada para conexiones ODBC, conexiones JDBC, ejecución SQL dinámico, y operaciones SQL Shell. Sin embargo, sentencias SQL embedded no realizan verificación de privilegio bajo el supuesto de que aplicaciones usando SQL embedded implementan su propia lógica de autorización antes de ejecutar consultas. Similarmente, invocación directa de consultas de clase que no involucran objetos %SQL.Statement es considerada acceso de aplicación y evita verificaciones de privilegio SQL. Este diseño permite a aplicaciones confiables operar eficientemente mientras aplica seguridad para conexiones externas y consultas ad-hoc.

Privilegios SQL operan en múltiples niveles de granularidad. Privilegios a nivel de tabla (SELECT, INSERT, UPDATE, DELETE, ALTER, REFERENCES) controlan operaciones sobre tablas o vistas enteras. Privilegios a nivel de columna proporcionan control más fino, permitiendo privilegio SELECT sobre columnas específicas en lugar de la tabla entera. Esto es particularmente importante para consultas de sintaxis arrow, que requieren privilegio SELECT sobre el ID de tablas referenciadas más las columnas referenciadas. Privilegios administrativos como %ALTER_TABLE habilitan operaciones de modificación de esquema tales como TUNE TABLE. Usuarios que crean tablas automáticamente reciben privilegios de propietario sobre esas tablas. El comando %CHECKPRIV permite verificación de privilegio programática antes de ejecutar operaciones, habilitando aplicaciones para proporcionar retroalimentación apropiada cuando usuarios carecen de permisos necesarios. Los comandos GRANT y REVOKE gestionan asignaciones de privilegio, y privilegios pueden otorgarse WITH GRANT OPTION para permitir a receptores otorgar esos privilegios a otros. Roles pueden combinar tanto privilegios SQL como a nivel de sistema, proporcionando modelos de seguridad flexibles. Mejores prácticas incluyen seguir el principio de privilegio mínimo, usar roles para gestión de privilegio, implementar privilegios a nivel de columna para datos sensibles, y auditar uso de privilegio a través de características de auditoría SQL.