Quiero acelerar mis consultas en SQL Server!! (Continuación)

Muchos clientes se quejan de consultas lentas, para ello os voy a dar unas guías básicas como una e-pildora que ampliaré en vista de la utilidad y éxito ahora que tengo un ratito más:


Para acelerar las consultas en SQL Server y en cualquier motor de base de datos debemos usar apropiadamente los índices


Las columnas que se aconseja indexar son:


– Las que son clave primaria o ajena


– Aquellas que se usan frecuentemente en búsquedas de rangos de valores con BETWEEN


– Aquellas que se usan frecuentemente en ordenaciones con ORDER BY


– Aquellas que se usan frecuentemente en cruces de tabla o JOIN


– Aquellas que se usan frecuentemente en agrupaciones con GROUP BY


 Tampoco se trata señores de ponerse a indexar todos los campos! No es oro todo lo que reluce pues penalizará el rendimiento de la base de datos
en las inserciones , actualizaciones y borrados por la cantidad de índices a mantener. De lo que se trata es de seleccionar las consultas que más se usan en nuestra aplicación todos los días y coger las más importantes y fijarse en la parte WHERE.


Es importante indicar que no merece la pena indexar campos con pocos valores como true, false, o 1,2,3 por ejemplo porque tienen poca selectividad y
una alta densidad de valores habitualmente.  También decir que solo puede haber un índice agrupado por tabla y hasta 249 índices no agrupados.


Hay otras consideraciones que se deben tener en cuenta como consultar el Plan de Ejecución de la consulta CTRL+L en el QueryAnalizer, os comento algunas más aunque alguno se ha adelantado 😉


– Usar procedimientos almacenados


– Usar la opción FILLFACTOR al crear el indice para envitar divisiones de página en las modificaciones si nuestra base de datos sufre muchas modificaciones.


– Usar DBCC SHOWCONTIG para ver la fragmentación de un índice y ver si merece la pena desfragmentarlo con DBCC INDEXDEFRAG


– Usar la opción WITH DROP EXISTING al crear el indice para no tener que eliminarlo y volver a crearlo


Os pongo algunas sentencias de ejemplo ilustrativas sobre la archiconocida NorthWind que seguro ayudarán a más de uno:


USE Northwind
DBCC SHOWCONTIG (nombre tabla,indice)


DBCC INDEXDEFRAG(credit, member, mem_no_CL)


USE ClassNorthwind
CREATE INDEX OrderID_ind
 ON Orders(OrderID)
 WITH PAD_INDEX, FILLFACTOR=10


Para encontrar duplicados antes de crear un índice único sobre la tabla products


SELECT ProductName, COUNT(ProductName) AS ‘# of Duplicates’
FROM Northwind.dbo.Products
GROUP BY ProductName
HAVING COUNT(ProductName)>1
ORDER BY ProductName


Creación del índice no agrupado único compuesto sobre 2 campos


Use ClassNorthwind
CREATE UNIQUE NONCLUSTERED INDEX U_LastName_FirstName
ON Employees (LastName, FirstName)


Para ver la fragmentación sobre la tabla member:


DBCC SHOWCONTIG (‘member’)


Para ver los índices y restricciones y espacio ocupado por los datos y por los índices: 


USE Northwind
EXEC sp_helpindex  Customers
EXEC sp_help  Customers
EXEC sp_spaceused products


Saludos


Sergio Vázquez


http://www.pangeaes.com/mutisdotnet


 


 

13 comentarios en “Quiero acelerar mis consultas en SQL Server!! (Continuación)”

  1. Hola,

    La verdad que este tema es muy complejo y bastante amplio. Para mí el primer punto que siempre debemos cuestionarnos siempre es si estamos creando correctamente las sentencias SQL.

    En la mayoría de los casos se gana más mejorando las sentencias que con índices. Esto no quita que haya que tener unos buenos índices 🙂

    Ibon.

  2. Hola Sergio:

    Un punto importante es que a la hora de hacer un “SELECT” es importante especificar los campo de la BD.

    Por ejemplo: “SELECT Codigo, Nombre FROM….”, ya que usar “*”, es decir seleccionar todo es una mala practica, ya que retarda la respuesta en la consulta.

    Saludos.

  3. Muy buenas!

    No sé si en versiones anteriores del motor del SQL Server (o en otros RDBMS) esto funcionaría así, pero en SQL Server 2005 hacer un: “AND EXISTS (SELECT IDTABLA FROM TABLA WHERE…)” es exactamente lo mismo que hacer un: “AND EXISTS (SELECT TOP 1 IDTABLA…)”. Comprobado con el Plan de Ejecucion.

    Hombre yo supongo que los que se dedican a diseñar y optimizar los ‘parsers’ o intérpretes de las consultas, estas cosas ya las tienen en cuenta… ;P

    Ah otra cosa… si queréis acelerar bastante los tiempos de las consultas, no hay nada mejor que añadir la cláusula “WITH(NOLOCK)” al SELECT. Así:

    SELECT CAMPO1, CAMPO2, CAMPOn FROM TABLA T WITH(NOLOCK) WHERE …

    Se ahorran muchos valiosos milisegundos de proceso, al no tener que bloquear el motor la fila que se está leyendo en ese momento.

    Saludos

  4. Sergi, me encantó el artículo porque basicamente lo que propone es aplicar un poco de sentido comun, yo creo que simplemente teniendo en cuenta estos conceptos, ya nos ahorraremos muchisimos dolores de cabeza.

    Saludos

  5. Hola Pablo,

    Con “WITH(NOLOCK)” lo que haces es decirle que ignore los bloqueos que pueda haber sobre la base de datos y obtenga los datos. La consulta va más rápida, pero cuidado!!! puede que no estés leyendo la información correcta. Por ejemplo, si hay una transacción que se está ejecutando y bloquea la tabla y todavía no se ha hecho el commit, cuando haces la select es como si no si no la transicción no existiese.

    Alguna recomendación más..
    > Evaluar la desnormalización del E-R para mejorar las sentencias.
    > Eliminar los índices que no se usan!!
    > Usar procedimientos almacenados.
    > Elegir correctamente entre índices agrupados y no agrupados..
    > ….

  6. Alguna recomendación más..

    > Evaluar la desnormalización del E-R para mejorar las sentencias.

    > Eliminar los índices que no se usan!!

    > Usar procedimientos almacenados.

    > Elegir correctamente entre índices agrupados y no agrupados..

    > ….

    No me gustan la primera solución que aportas, aunque estoy de acuerdo con el resto.

    Hay una cosa que está clarísima: si lo que queremos es mejorar el desempeño de una base de datos a la hora de realizar consultas (hablamos de consultas y no de inserciones, etc. porque éstas últimas involucran a pocos datos, por lo general), lo que hay que hacer es optimizar la base de datos. Esto se consigue mediante dos frentes:

    – > Optimizar el diseño físico de la base de datos. Utilizar índices, árboles, tablas hash… lo que más interese, dependiendo el tipo de consultas que se hacen: fijas, por patrones… etc.

    – > Optimizar las propias consultas, sobre todo los útiles y malditos JOIN, de los cuales existen tres alternativas (al menos en Oracle): MERGE, HASH, NESTED. El administrador de la base de datos puede jugar un poco con ésto.

    ¡¡Ale, ya tenéis material para investigar!!

  7. Hola , he leido los comentarios y me paracen muy buena alternativas sobre todo eso de revisar la estructura del ER , pienso que es el diseño de la BD de donde parte todas las soluciones , adicionalmente puedo agregar algo que a mi me sirvio mucho , por ejemplo en las consultas con join normalmente se hacen a una tabla pero tambien se pueden meter aqui querys (complejos)que evitan crear vistas y/o otros querys antes del select principal , esto agiliza mucho el proceso

    select codigo, apellidos , nombres
    from empleados
    inner join
    ( select id_tipo_empleado
    from tipo_empleado
    where id_tipo_empleado= ‘CONTABILIDAD’
    ) as tipo on tipo.id_tipo_empleado= empleados.id_tipo_empleado

    Saludos

  8. Hola:

    He estado trabajando mucho en la optimización de consultas de mis aplicaciones y mis recomendaciones son las siguientes:

    No hacer join entre más de 3 tablas, si es necesario recomiendo la creación de vistas.

    No usar subquerys, esto son muy costosos de resolver por el motor de SQL.

    Evitar el uso de ‘OR’ en los filtros, lo mismo para la clausula ‘campo in (campolista1, campolista2, campolista3, etc)’

    al usar campo que no son llaves o usar funciones para comparar campos, también agrega costo a la consulta, por ejemplo:

    …Where cast(codigo as varchar(4)) = ‘hola’
    o
    …where campo1 + campo2 = ‘200901’

    Saludos

  9. Gracias por los consejos de optimizacion, aqui les dejo un ejemplo SQL server 2005 que sirve para buscar las coincidencias de nombres o apellidos cuando estos estan en dos campos diferentes y cuando hay mas de un nombre o mas de un apellido;

    declare @data as nvarchar(255),
    @records as int
    set @data = ‘carlos javier’
    set @records = 20

    SET CONCAT_NULL_YIELDS_NULL OFF
    SELECT top (@records) CHARINDEX(@data , ‘ ‘ + nombre + ‘ ‘ + apellidos) ,nombre + ‘ ‘ + apellidos
    as result, id_usuario as id FROM [dbo].[nombreTabla]
    where CHARINDEX(‘ ‘ + @data , ‘ ‘ + nombre + ‘ ‘ + apellidos) > 0
    order by nombre

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *