Consejos para crear y optimizar consultas en SQL.

Hola que tal mis amigos un nuevo articulo para todos ustedes: Este articulo esta dirigido a usuarios medios con experiencia en la administracion de SQL.

Comenzemos:

  De nada serviría tener una base de datos (BDD) si no pudiéramos consultarla. Por eso, el proceso más importante que se debe llevar a cabo en cualquiera es la consulta de la información almacenada.  Como su nombre indica, Microsoft SQL Server es una herramienta que nos permite, precisamente, gestionar la información almacenada en la BDD.Para garantizar un acceso rápido y eficaz los datos que almacenan tus bases de datos, aquí tienes algunos consejos de cómo realizar consultas en SQL.

1. Uso de cursores

Si la consulta utiliza cursores, determina antes si es posible escribirla con un tipo de cursor más eficaz (uno de avance rápido) o con una única consulta. Las consultas únicas mejoran las operaciones de cursor.

Dado que un conjunto de instrucciones de cursor suele constituir una operación de bucle externo, en la que cada fila se procesa una vez con una instrucción interna, puedes contemplar la posibilidad de usar en su lugar una instrucción GROUP BY o CASE. Quizá incluso una subconsulta.

2. Uso de alias

Utilizar varios alias para una sola tabla en la misma consulta para simular la intersección de índices ya no es necesario. SQL Server tiene en cuenta automáticamente la intersección de índices y puede utilizar varios en la misma tabla.

3. Uso de la parametrización

Utiliza la parametrización de consultas para permitir la reutilización de los planes de ejecuciónde consulta almacenados en la memoria caché. Si un conjunto de consultas comparte el mismo hash de consulta y hash de plan de consulta podrías mejorar el rendimiento creando una consulta parametrizada.

Además, si llamas a una consulta con parámetros, en lugar de a varias consultas con valores literales, podrás reutilizar el plan de ejecución de consulta almacenado en la memoria caché.

4. Uso de Exist´s

Cuando queramos hacer una sub-consulta en una base de datos utilizando la sentencia NOT IN, analicemos si podemos cambiar nuestro queries con el uso de la sentencia Exists que es mucho más eficiente que la anterior. O en todo caso, utilizar IN en vez de NOT IN, ya que esto hace un escaneo completo en la tabla descartando opciones a omitir.

5. Uso de Distinct

Utilizar distinct para excluir datos duplicados es muy usado por los programadores para evitar errores de diseño de base de datos y así esconder algunos duplicidad de información, pero esto es un grave error. Es una de las sentencias que más necesita hacer I/O en el disco y forzar bastante el procesador. Por tal motivo, si no es necesario evitemos utilizarla.

6. Uso de Tops

Cuando se quiere traer un grupo de registros es mejor utilizar la sentencia Top y no Rowcount, ya que esta última presenta inconvenientes con listas no ordenadas. En cambio, si la lista es ordenada es más eficiente que la sentencia Top.

7. Uso de*.

Cuando se realizan consultas que van a devolver muchos campos es mejor definir todos los campos que queremos devolver en nuestro queries, ya que el uso de * o All impide el uso de índices de forma eficiente.

8. Verificar si existe un registro

Muchos programadores utilizan el count(*) para ver si un registro existe en la base de datos, pero una forma más eficiente de hacerlo es con Exists. Cuando éste encuentra un registro detiene la búsqueda del mismo.

9. Uso de ORDER BY

Usar ORDER BY en las QUERIES que se lancen sólo si es absolutamente indispensable. Es decir, que si es posible realizar la ordenación en el lado del cliente siempre será mejor que realizarla desde el lado del servidor SQL Server.

En caso de que sea absolutamente necesario realizar la ordenación en el lado del servidor SQL Server deberemos atender a las siguientes recomendaciones:

1. Mantener el número de filas a ordenar al mínimo
2. Mantener el número de columnas a ordenar al mínimo
3. Mantener el ancho (tamaño físico) de las columnas a ordenar al mínimo
4. Ordenar columnas con datos numéricos (NO tipos de datos carácter)

Cuando usemos cualquier mecanismo de ordenación en Transact –SQL, debemos tener en mente todas estas recomendaciones para la mejora del rendimiento.

Si se ha de ordenar por una columna a menudo, debemos considerar el realizar un “Clustered Index” sobre esa columna para la mejora del rendimiento.

10. No usar el comando GROUP BY

No usarlo al menos sin una función de agregación. La cláusula GROUP BY puede usarse con o sin una función de agregación. Pero si queremos obtener un mejor rendimiento, no usaremos la cláusula GROUP BY sin una función de agregación. Esto es porque produce el mismo resultado usar DISTINCT y es más rápido.

Para acelerar el uso de la cláusula GROUP BY debemos seguir las siguientes recomendaciones:

1. Mantener al mínimo el número de filas a devolver por la Query.
2. Mantener al mínimo el número de agrupaciones
3. No agrupar columnas redundantes
4. Cambiar un JOIN por una SUBQUERY cuando hay uno en la misma SELECT que tiene un GROUP BY.
   Si es posible hacer esto, el rendimiento será mayor. Si se tiene que usar un JOIN, intentaremos hacer el GROUP BY por columna desde la misma tabla que la columna o columnas sobre la cual la se usa la función.

Arrays de parametros en procedimientos almacenados en SQL Server

La gran herramienta para poder hacer parte de nuestro trabajo de acceso a datos dentro del propio servidor.

Pero cuando empezamos a crear procedimientos almacenados cada vez más y más complicados llega un momento en el que nos encontramos con la necesidad de que nuestro procedimiento reciba un número indeterminado de parámetros.

Cuando esto me ocurrió a mi recuerdo que me puse a buscar en los BOL como podía pasar un Array a mi procedimiento, pero me encontré con la imposibilidad de trabajar con Arrays en TSQL.

Así que buscando un poco más encontré un par de métodos para hacer el trabajo "a mano" y eso es lo que vamos a comentar aquí.Solución 1. Paso de un string

Una primera solución es pasar un string al procedimiento almacenado donde incluimos todos los parámetros. Dentro del procedimiento almacenado partimos nuestro string en los parámetros que lo componen y podemos trabajar con ellos.

Veamos un ejemplo en el que una serie de valores se pasan al procedimiento almacenado separados por comas y dentro del procedimiento separamos la cadena en sus parámetros constituyentes.

Vamos a pasar el string "valor1,valor2,valor3" para que el procedimiento almacenado lo descomponga en "valor1", "valor2", "valor3".

El procedimiento almacenado será el siguiente:

CREATE PROCEDURE RecibirParametros @Parametros varchar(1000)
--@Parametros es la cadena de entrada
AS
--Creamos una tabla temporal por simplificar el trabajo
--y almacenar los parametros que vayamos obteniendo
CREATE TABLE #parametros (parametro varchar(1000))
SET NOCOUNT ON
--El separador de nuestros parametros sera una ,
DECLARE @Posicion int
--@Posicion es la posicion de cada uno de nuestros separadores
DECLARE @Parametro varchar(1000)
--@Parametro es cada uno de los valores obtenidos
--que almacenaremos en #parametros
SET @Parametros = @Parametros + ','
--Colocamos un separador al final de los parametros
--para que funcione bien nuestro codigo
--Hacemos un bucle que se repite mientras haya separadores
WHILE patindex('%,%' , @Parametros) <> 0
--patindex busca un patron en una cadena y nos devuelve su posicion
BEGIN
  SELECT @Posicion =  patindex('%,%' , @Parametros)
  --Buscamos la posicion de la primera ,
  SELECT @Parametro = left(@Parametros, @Posicion - 1)
  --Y cogemos los caracteres hasta esa posicion
  INSERT INTO #parametros values (@Parametro)
  --y ese parámetro lo guardamos en la tabla temporal
  --Reemplazamos lo procesado con nada con la funcion stuff
  SELECT @Parametros = stuff(@Parametros, 1, @Posicion, '')
END
--Y cuando se han recorrido todos los parametros sacamos por pantalla el resultado
SELECT * FROM #parametros
SET NOCOUNT OFF
GO

Para probar como funciona

EXECUTE RecibirParametros 'valor1,valor2,valor3'
parametro
--------------
valor1
valor2
valor3

por supuesto que habitualmente no querremos separar la cadena de entrada en parámetros sin más, sino que queremos tratarlos de alguna manera. Pero el funcionamiento esencial es el mismo que el aquí presentado. Solución 2. Separación por XML

Como el formato XML cada vez está más de moda para transmitir información se nos puede ocurrir que los parámetros a pasar al procedimiento almacenado podrían tener ese formato, sobre todo sabiendo que el SQL Server tiene unas cuantas funciones sencillas y potentes para trabajar con datos descritos en este lenguaje.

Pues vamos a ello. En esta segunda solución la colección de parámetros vamos a pasarla como una cadena pero formateada con XML. Podemos dar el siguiente aspecto a nuestros parámetros.

<raiz>
<parametro valor="valor1"/>
<parametro valor="valor2"/>
<parametro valor="valor2"/>
</raiz>

Y los parámetros descritos con este formato serán nuestra cadena de entrada

'<raiz><parametro valor="Valor1"/><parametro valor="Valor2"/>
 <parametro valor="Valor2"/></raiz>'

El procedimiento almacenado que leerá esta cadena y nos devolverá los parámetros en una tabla (para que sea similar al anterior) es el siguiente:

CREATE PROCEDURE RecibirParametrosXML @Parametros varchar(1000)
--@Parametros es la cadena XML de entrada
AS
CREATE TABLE #Parametros (Parametro varchar(1000))
--Creamos la tabla temporal para almacenar los parámetros de salida
DECLARE @idoc INT
--Nos hace falta una variable de tipo int para referirnos al documento
--XML con el que vamos a trabajar
SET NOCOUNT ON
--Preparamos el documento con sp_xmlpreparedocument
--Podéis ver la descripcion de como funciona en los BOL
EXEC sp_xml_preparedocument @idoc OUTPUT, @Parametro
--Metemos los valores en una tabla temporal leyendolos
--del documento que hemos preparado. Para ello usamos el comando
--OpenXml. Podéis ver también como funciona en los BOL
INSERT INTO #Parametros Select * FROM
 OpenXml(@idoc,'raiz/parametro',1) with (valor Varchar(1000))
--Mostramos el resultado
SELECT * FROM #Parametros
--y liberamos la memoria ocupada
exec sp_xml_removedocument @idoc output

Y por supuesto para probarlo

EXECUTE recibirparametrosXML '<principal><parametro valor="Valor1"/>
 <parametro valor="Valor2"/><parametro valor="Valor2"/></principal>'
Parametro
------------------
Valor1
Valor2
Valor2

El resultado es el mismo que antes.

Como podemos ver la imposibilidad de pasar Arrays no es un límite si utilizamos un poco de imaginación. Ahora podemos escoger si queremos usar un método más clásico y pasar los parámetros en una cadena o nos podemos decantar por una tecnología más en boga y utilizar (de alguna manera) el lenguaje XML para pasar información a nuestro procedimiento almacenado.