Estadares SQL

Programación en MS SQL

Lógica de Negocios en BD

Caso Generación de Información Masiva

Caso Carga de Información Masiva

SSIS

Herramientas de optimización e información de las consultas en general

- Evitar el uso de la sentencia “SELECT *” . Esta sentencia genera un impacto en el rendimiento de la consulta debido a que el motor SQL antes de buscar la data realiza primero una lectura de toda al estructura de la tabla o tablas afectadas.

Ejemplo:

No se recomienda:

SELECT *

FROM operación

Se recomienda:

SELECT ope_numero_operacion, ope_codigo_cliente

FROM operacion

- Si se realiza una consulta de varias tablas, y la relación se realiza de manera implícita, se recomienda especificar en cada columna de la consulta el nombre de la tabla a la que pertenece. Esto mejora el rendimiento de la consulta debido a que el motor SQL ya no dedicará tiempo y recursos en buscar a qué tabla pertenece cada columna.

Ejemplo:

No se recomienda:

SELECT ope_numero_operacion, cli_nombre_comercial

FROM operacion, cliente

where cliente.cli_codigo = operacion.ope_codigo_cliente

Se recomienda:

SELECT operacion.ope_numero_operacion, cliente.cli_nombre_comercial

FROM operacion, cliente

where cliente.cli_codigo = operacion.ope_codigo_cliente

Si se realiza una consulta de varias tablas y la relación se realiza de manera implícita, se debe ingresar el orden correcto de las tablas en la clausula “FROM” y “WHERE”. Esto puede reducir la cantidad de registros leídos por la consulta.

Ejemplo:

No se recomienda:

SELECT aux_descripcion

FROM tio_aux, reclasificacion_ctb

WHERE tio_aux.aux_codigo_tioaux = reclasificacion_ctb.rctb_tio_aux

AND rctb_periodo = '200312'

Se recomienda:

SELECT aux_descripcion

FROM reclasificacion_ctb, tio_aux

WHERE rctb_periodo = '200312'

AND reclasificacion_ctb.rctb_tio_aux = tio_aux.aux_codigo_tioaux

Asimismo, cuando las condiciones de la clausula WHERE incluyen el operador AND, se debe ubicar la condición que haga un mejor filtro por delante de las demás, de tal forma que si no se cumple esta condición, el motor de la base de datos ya no valida las condiciones restantes.

- En lo posible, se debe tratar de evitar el uso de cursores. Los cursores utilizan muchos recursos y disminuyen el rendimiento de las aplicaciones. En caso que no haya otra opción que utilizar cursores, al finalizar su uso no sólo se debe cerrar el cursor, sino también se le debe liberar de memoria (DEALLOCATE cursor).

- No se recomienda utilizar tablas temporales a no ser que sea completamente necesario. Un escenario ejemplo sería si con éstas se evita utilizar cursores. En este caso, si se van a utilizar tablas temporales, éstas deben ser creadas considerando los siguientes puntos:

• Incluir sólo las columnas que se van a utilizar.

• No utilizar la sentencia SELECT INTO para poblar la tabla, ya que esta sentencia utiliza LOCKS en varios objetos. En su lugar, se debe crear la tabla de manera regular y luego utilizar la sentencia INSERT INTO para poblarla.

- Evitar utilizar funciones en la clausula WHERE. Las llamadas a funciones impactan el rendimiento de la consulta. Se recomienda buscar alternativas con las que se obtengan los mismos resultados.

Ejemplo:

No se recomienda:

SELECT cli_codigo

FROM cliente

WHERE SUBSTRING(cli_apellido_paterno,1,1) = 'M'

Se recomienda:

SELECT cli_codigo

FROM cliente

WHERE cli_apellido_paterno like 'M%'

----------------------------------------------

Optimizaciones Comunes:

Funciones y expresiones que eliminan el uso de índices.

En muchos casos, el optimizador de consultas no puede utilizar índices de las columnas de la tabla cuando se aplican funciones o expresiones a la columna indexada. Si fuera el caso se debería re-escribir la consulta.

Ejemplo:

En todos los casos la columna referida “en el WHERE” es una columna con índice del tipo UNIQUE CLUSTERED INDEX; se indican los campos con SELECT * sólo con fines demostrativos.

Consultas que no utilizan el índice Consultas optimizadas

SELECT *

FROM big_sales

WHERE SUBSTRING(stor_id, 1, 2) = '63'

-- function sobre el índice de una columna. SELECT *

FROM big_sales

WHERE stor_id LIKE '63%'

SELECT *

FROM big_sales

WHERE (stor_id -146) = '7896'

-- Conversión implícita y expresión. SELECT *

FROM big_sales

WHERE stor_id = '8042'

SELECT *

FROM jobs

WHERE (job_id + 7) = 14

-- Expresión matemática sobre columna SELECT *

FROM jobs

WHERE job_id = 7

DECLARE @job_id VARCHAR(5)

SELECT @job_id = '2'

SELECT *

FROM jobs

WHERE CAST(job_id AS VARCHAR(5)) = @job_id

-- Conversión explícita DECLARE @job_id VARCHAR(5)

SELECT @job_id = '2'

SELECT *

FROM jobs

WHERE job_id = CAST(@job_id AS SMALLINT)

CREATE INDEX employee_hire_date

ON employee(hire_date)

GO

-- Obtener todos lo empleados contratados

-- en el primer cuatrimestre de 1990

SELECT hire_date

FROM employee

WHERE DATEPART(YEAR, hire_date) = 1990

AND DATEPART(quarter, hire_date) = 1

-- Funciones sobre la colum CREATE INDEX employee_hire_date

ON employee(hire_date)

GO

-- Obtener todos lo empleados contratados

-- en el primer cuatrimestre de 1990

SELECT hire_date

FROM employee

WHERE hire_date >= '1/1/1990'

AND hire_date < '4/1/1990'

Optimización de subconsultas.

Como regla general, se debe intentar reemplazar las subconsultas con joins en donde sea posible. Algunas veces el optimizador automáticamente realiza esta conversión por joins; pero no siempre se puede esperar que haga un buen trabajo con eso. Joins explícitos le dan al optimizador más opciones para elegir el orden de las tablas y obtener un mejor plan.

Ejemplo:

Consulta con sub-consultas. Consultas con Joins

...