sábado, 28 de junio de 2014

Trucos y Consejos para optimización de consultas y mejorar los tiempos (Rendimiento)

Desde hace muchos años trabajo en desarrollo de software y en el transcurso de ese tiempo he venido adquiriendo algunas costumbres a la hora de escribir las sentencias  SQL, algunas óptimas y otras no tan ortodoxas, a continuación se las pongo a disposición. Les recuerdo que esto es bajo su propia responsabilidad.

Aquí encontrarás las diferentes formas de hacer consultas con parámetros  dinámicos y la manera de optimizar y mejorar los tiempos de consulta.

1.       La primera parte es la optimización de los campos durante el diseño de la tabla. Utiliza el tipo de campo necesario para almacenar el valor. Por ejemplo si necesito almacenar una fecha “20/11/2014”, utilizo un campo Date en vez del DateTime. Si el campo va a almacenar un texto utiliza un NVarChar(n) con un “n” que corresponda a un valor ligeramente mayor al tamaño máximo esperado, procuró nunca usar el NVarChar(Max).
2.       Trato  de tener cuidado con la creación de los índices, generalmente al inicio del sistema los coloco solo en los campos necesarios, como costumbre tengo el usar identificadores únicos de registro como llave de los datos mediante el uso de “IDENTITY”, esto hace necesario la creación de un índice adicional único por la llave de los datos. Por ejemplo, si estamos hablando de cuentas de un banco la llave natural de los datos sería el tipo y el número de la cuenta, si fuesen personas la clave natural sería el tipo y el número del documento. Para mí los índices dependen más del tipo de sistema, si es para registrar información más que para consultarla, el uso de índices debe ser mínimo.
3.       La lectura (interpretación) de un código de consulta es más sencillo si uno sigue ciertos parámetros, por ejemplo: Al momento de hacer una sentencia SQL, ubico un solo campo por línea, esto facilita la lectura de la sentencia:
Así no: Select dCliente, NombreCliente, TipoIdentificacion, Identificacion ,Direccion, Correo, EsActivo, CreadoPor, FecCreacion, ModificadoPor, FecModificacion From Cliente
Es mejor así:
Select IdCliente
      ,NombreCliente
      ,TipoIdentificacion
      ,Identificacion
      ,Direccion
      ,Correo
      ,EsActivo
      ,CreadoPor
      ,FecCreacion
      ,ModificadoPor
      ,FecModificacion
From Cliente

4.       A diferencia de los ejemplos que encuentro en internet No escribo las comas al final del campo, para mi es mejor escribirlas al comienzo, esto facilita  la actualización, cuando necesito quitar un campo, basta con agregar el carácter de comentario “--”al inicio del campo.
Select IdCliente
      ,NombreCliente
      ,TipoIdentificacion
      ,Identificacion
      ,Direccion
      ,Correo
      ,EsActivo
      --,CreadoPor
      --,FecCreacion
      --,ModificadoPor
      --,FecModificacion
From Cliente

5.       Cuando inicio la elaboración de una consulta utilizo el asterisco “*”, que me trae todos los campos, posteriormente lo sustituyo por los nombres de los campos necesarios, nunca lo dejo al final, esto impactaría demasiado en el rendimiento de la BD.
Select * From Cliente --Esto no se debe hacer
Es más optimo así:
Select IdCliente
      ,NombreCliente
      ,TipoIdentificacion
      ,Identificacion
      ,Direccion
      ,Correo
      ,EsActivo
      ,CreadoPor
      ,FecCreacion
      ,ModificadoPor
      ,FecModificacion
From Cliente

6.       Utilizo  alias para los nombres de las tablas, es más rápido para escribir y si utiliza un editor de sentencias con “IntelliSense”. Es más fácil encontrar los campos.
Select CL.IdCliente
      ,CL.NombreCliente
      ,CL.TipoIdentificacion
      ,CL.Identificacion
      ,CL.Direccion
      ,CL.Correo
      ,CL.EsActivo
      ,CL.CreadoPor
      ,CL.FecCreacion
      ,CL.ModificadoPor
      ,CL.FecModificacion
From Cliente CL

7.       Cuando comienzo a escribir las validaciones de un Where, siempre comienzo con la línea 1=1, su resultado es siembre True. Esto facilita el agregar nuevas líneas y la depuración de los comandos. Si en el momento de estar elaborando un Select complejo, necesitas quitar alguno de los parámetros es más fácil comentar la línea del parámetro a quitar.
Select CL.IdCliente
      ,CL.NombreCliente
      ,CL.TipoIdentificacion
      ,CL.Identificacion
      ,CL.Direccion
      ,CL.Correo
      ,CL.EsActivo
      ,CL.CreadoPor
      ,CL.FecCreacion
      ,CL.ModificadoPor
      ,CL.FecModificacion
From Cliente CL
Where 1=1
      --And CL.EsActivo = 1
      And CL.CreadoPor = 'Administrador'

8.       Hace algún tiempo necesitaba leer la estructura de una tabla sin la necesidad de traer datos, para esto comencé a utilizar la sentencia: Select * From DigitalTP.Cliente Where IdCliente=-1, Esto permite tener la estructura de columnas y sus tipos de datos, pero hacia un pique al índice de la llave (consulta al índice), encontré que es más eficiente usar: Select * From DigitalTP.Cliente Where 1=2

9.       Cuando empiezo la elaboración de un procedimiento almacenado, declaro los parámetros y le asigno valores predeterminados mientras elaboro la sentencia SQL. Si la consulta es a una tabla con demasiados registros, al ejecutarla las primeras veces esta puede demorarse, para evitar esto  le escribo un TOP al principio de la consulta. Una vez finalizado el procedimiento, quito los valores predeterminados y el TOP.  Para el momento de la implementación coloco al inicio del Script una validación para saber si el procedimiento ya existe, eliminarlo y volver a crearlo, esto facilita las actualizaciones a los servidores de producción.

Mientras elaboro y evaluó la consulta:

Declare @CreadoPor NVarchar(50) = 'Administrador'
Declare @EsActivo Int = 1

Select Top 1000 CL.IdCliente
      ,CL.NombreCliente
      ,CL.TipoIdentificacion
      ,CL.Identificacion
      ,CL.Direccion
      ,CL.Correo
      ,CL.EsActivo
      ,CL.CreadoPor
      ,CL.FecCreacion
      ,CL.ModificadoPor
      ,CL.FecModificacion
From Cliente CL
Where 1=1
      And CL.EsActivo = @EsActivo
   And CL.CreadoPor = @CreadoPor

Cuando ya tengo listo el procedimiento para enviar a producción.

If (select Count(*) from sysobjects where type='P' and name='[uspCliente_Consultar')>0
BEGIN
      DROP PROCEDURE dbo.[uspCliente_Consultar];
END
GO

CREATE PROCEDURE [dbo].[uspCliente_Consultar] 
                         @CreadoPor NVarchar(50)
                        ,@EsActivo Int
AS 
BEGIN 

Select CL.IdCliente
      ,CL.NombreCliente
      ,CL.TipoIdentificacion
      ,CL.Identificacion
      ,CL.Direccion
      ,CL.Correo
      ,CL.EsActivo
      ,CL.CreadoPor
      ,CL.FecCreacion
      ,CL.ModificadoPor
      ,CL.FecModificacion
From Cliente CL
Where 1=1
      And CL.EsActivo = @EsActivo
      And CL.CreadoPor = @CreadoPor
End


10.   Cuando la base de datos es muy grande, en ocasiones  elaboro procedimientos almacenados genéricos para algunas consultas, los cuales pueden responder a múltiples necesidades, estas reciben múltiples parámetros y filtran los datos por los parámetros que traen valores, lo anterior hace que el uso de los parámetros del Where  sea más delicado. Para esto hay varias maneras de configurar las consultas, aquí describiré a continuación las que conozco:

Una manera rápida, es adicionar una validación al parámetro de tal manera que si viene con un valor nulo no se tenga en cuenta para la búsqueda de registros, Esto es rápido de hacer aunque degrada el desempeño de la base de datos, su uso debiese restringir a tablas de poco tamaño Ejemplo:
Where 1=1
      And (@EsActivo Is Null Or CL.EsActivo = @EsActivo)
   And (@CreadoPor Is Null Or CL.CreadoPor = @CreadoPor)
Otra manera es mediante el uso de la instrucción  COALESCE
Where 1=1
      And CL.EsActivo=COALESCE(@EsActivo, EsActivo)
      And CL.CreadoPor=COALESCE(@CreadoPor, CL.CreadoPor)
11.   Existe otra manera que consiste en la elaboración de una consulta con las sentencias del Where  dinámicas, que filtren la información de los parámetros que traen valores y ejecutar la consulta mediante el comando Exec. Hacer la consulta así, es rápido, pero evita que el motor de la BD pueda armar planes de ejecución y abre una puerta a inyecciones de SQL.

Declare @IdCliente int=Null
Declare @FK_TipoIdentificacion int=Null
Declare @Identificacion nvarchar(128)=Null
Declare @NombreCliente nvarchar(256)='Gustavo Molina'
Declare @Contacto nvarchar(128)=Null
Declare @CargoContacto nvarchar(128)=Null
Declare @Ciudad nvarchar(50)=Null
Declare @Direccion nvarchar(256)=Null
Declare @Correo nvarchar(256)=Null
Declare @Movil nvarchar(128)=Null
Declare @Telefono nvarchar(128)=Null
Declare @EsActivo int=Null
Declare @CreadoPor nvarchar(50)=Null
Declare @FecCreacion date=Null
Declare @ModificadoPor nvarchar(50)=Null
Declare @FecModificacion date=Null


Declare @Comilla NVarchar(1) = Char(39)
Declare @SqlExec as NVarchar(Max)

       Set @SqlExec = '
       Select   CL.IdCliente
                    ,CL.FK_TipoIdentificacion
                    ,CL.Identificacion
                    ,CL.NombreCliente
                    ,CL.Contacto
                    ,CL.CargoContacto
                    ,CL.Ciudad
                    ,CL.Direccion
                    ,CL.Correo
                    ,CL.Movil
                    ,CL.Telefono
                    ,CL.EsActivo
                    ,CL.CreadoPor
                    ,CL.FecCreacion
                    ,CL.ModificadoPor
                    ,CL.FecModificacion
       From DigitalTP.Cliente CL
       Where 1=1 '

       If(@IdCliente Is Not Null)
             Set @SqlExec = @SqlExec + ' And IdCliente = @IdCliente'
       If(@FK_TipoIdentificacion Is Not Null)
             Set @SqlExec = @SqlExec + ' And FK_TipoIdentificacion = @FK_TipoIdentificacion'
       If(@Identificacion Is Not Null)
             Set @SqlExec = @SqlExec + ' And Identificacion = ' + @Comilla + @Identificacion + @Comilla
       If(@NombreCliente Is Not Null)
             Set @SqlExec = @SqlExec + ' And NombreCliente = ' + @Comilla + @NombreCliente + @Comilla
       If(@Contacto Is Not Null)
             Set @SqlExec = @SqlExec + ' And Contacto = ' + @Comilla + @Contacto + @Comilla
       If(@CargoContacto Is Not Null)
             Set @SqlExec = @SqlExec + ' And CargoContacto = ' + @Comilla + @CargoContacto + @Comilla
       If(@Ciudad Is Not Null)
             Set @SqlExec = @SqlExec + ' And Ciudad = ' + @Comilla + @Ciudad + @Comilla
       If(@Direccion Is Not Null)
             Set @SqlExec = @SqlExec + ' And Direccion = ' + @Comilla + @Direccion + @Comilla
       If(@Correo Is Not Null)
             Set @SqlExec = @SqlExec + ' And Correo = ' + @Comilla + @Correo + @Comilla
       If(@Movil Is Not Null)
             Set @SqlExec = @SqlExec + ' And Movil =' + @Comilla + @Movil + @Comilla
       If(@Telefono Is Not Null)
             Set @SqlExec = @SqlExec + ' And Telefono = ' + @Comilla + @Telefono + @Comilla
       If(@EsActivo Is Not Null)
             Set @SqlExec = @SqlExec + ' And EsActivo = ' + @Comilla + @EsActivo + @Comilla
       If(@CreadoPor Is Not Null)
             Set @SqlExec = @SqlExec + ' And CreadoPor = ' + @Comilla + @CreadoPor + @Comilla
       If(@FecCreacion Is Not Null)
             Set @SqlExec = @SqlExec + ' And FecCreacion = ' + @Comilla + Cast(@FecCreacion as nVarchar) + @Comilla
       If(@ModificadoPor Is Not Null)
             Set @SqlExec = @SqlExec + ' And ModificadoPor ' + @Comilla + @ModificadoPor + @Comilla
       If(@FecModificacion Is Not Null)
             Set @SqlExec = @SqlExec + ' And FecModificacion ='+ @Comilla + Cast(@FecModificacion as NVarchar) + @Comilla

       Exec (@SqlExec)
12.   La última que conozco y tal vez la más versátil es la de utilizar el comando “Exec sp_executesql”, este comando permite la ejecución de un SELECT dinámico que acepta parámetros y adicionalmente arma planes de ejecución.   A diferencia del Exec, evita las inyecciones de SQL y es más óptimo para el motor de la BD. A continuación les dejo un ejemplo con uso de parámetros múltiples.
CREATE PROCEDURE [dbo].[uspCliente_Consultar] 
                     @IdCliente int=Null
                    ,@FK_TipoIdentificacion int=Null
                    ,@Identificacion nvarchar(128)=Null
                    ,@NombreCliente nvarchar(256)=Null
                    ,@Contacto nvarchar(128)=Null
                    ,@CargoContacto nvarchar(128)=Null
                    ,@Ciudad nvarchar(50)=Null
                    ,@Direccion nvarchar(256)=Null
                    ,@Correo nvarchar(256)=Null
                    ,@Movil nvarchar(128)=Null
                    ,@Telefono nvarchar(128)=Null
                    ,@EsActivo int=Null
                    ,@CreadoPor nvarchar(50)=Null
                    ,@FecCreacion date=Null
                    ,@ModificadoPor nvarchar(50)=Null
                    ,@FecModificacion date=Null

AS 
BEGIN 

       Declare @SqlExec as NVarchar(Max)
       Declare @SqlParametros as NVarchar(3000)=','

Set @SqlParametros = '@IdCliente int,@FK_TipoIdentificacion int,@Identificacion nvarchar(128),@NombreCliente nvarchar(256),@Contacto nvarchar(128),@CargoContacto nvarchar(128),@Ciudad nvarchar(50),@Direccion nvarchar(256),@Correo nvarchar(256),@Movil nvarchar(128),@Telefono nvarchar(128),@EsActivo int,@CreadoPor nvarchar(50),@FecCreacion date,@ModificadoPor nvarchar(50),@FecModificacion date'

       Set @SqlExec = '
       Select   CL.IdCliente
                    ,CL.FK_TipoIdentificacion
                    ,CL.Identificacion
                    ,CL.NombreCliente
                    ,CL.Contacto
                    ,CL.CargoContacto
                    ,CL.Ciudad
                    ,CL.Direccion
                    ,CL.Correo
                    ,CL.Movil
                    ,CL.Telefono
                    ,CL.EsActivo
                    ,CL.CreadoPor
                    ,CL.FecCreacion
                    ,CL.ModificadoPor
                    ,CL.FecModificacion
       From DigitalTP.Cliente CL
       Where 1=1 '
If(@IdCliente Is Not Null)
   Set @SqlExec = @SqlExec + ' And IdCliente = @IdCliente'
If(@FK_TipoIdentificacion Is Not Null)
   Set @SqlExec=@SqlExec + ' And FK_TipoIdentificacion=FK_TipoIdentificacion'
If(@Identificacion Is Not Null)
   Set @SqlExec = @SqlExec + ' And Identificacion = @Identificacion'
If(@NombreCliente Is Not Null)
   Set @SqlExec = @SqlExec + ' And NombreCliente = @NombreCliente'
If(@Contacto Is Not Null)
   Set @SqlExec = @SqlExec + ' And Contacto = @Contacto'
If(@CargoContacto Is Not Null)
   Set @SqlExec = @SqlExec + ' And CargoContacto = @CargoContacto'
If(@Ciudad Is Not Null)
   Set @SqlExec = @SqlExec + ' And Ciudad = @Ciudad'
If(@Direccion Is Not Null)
   Set @SqlExec = @SqlExec + ' And Direccion = @Direccion'
If(@Correo Is Not Null)
   Set @SqlExec = @SqlExec + ' And Correo = @Correo'
If(@Movil Is Not Null)
   Set @SqlExec = @SqlExec + ' And Movil = @Movil'
If(@Telefono Is Not Null)
   Set @SqlExec = @SqlExec + ' And Telefono = @Telefono'
If(@EsActivo Is Not Null)
   Set @SqlExec = @SqlExec + ' And EsActivo = @EsActivo'
If(@CreadoPor Is Not Null)
   Set @SqlExec = @SqlExec + ' And CreadoPor = @CreadoPor'
If(@FecCreacion Is Not Null)
   Set @SqlExec = @SqlExec + ' And FecCreacion = @FecCreacion'
If(@ModificadoPor Is Not Null)
   Set @SqlExec = @SqlExec + ' And ModificadoPor = @ModificadoPor'
If(@FecModificacion Is Not Null)
   Set @SqlExec = @SqlExec + ' And FecModificacion = @FecModificacion'

Exec sp_executesql  @SqlExec
                    ,@SqlParametros
,@IdCliente
,@FK_TipoIdentificacion
,@Identificacion
,@NombreCliente
,@Contacto
,@CargoContacto
,@Ciudad
,@Direccion
,@Correo
,@Movil
,@Telefono
,@EsActivo
,@CreadoPor
,@FecCreacion
,@ModificadoPor
,@FecModificacion
End


Si las opiniones aquí expresadas de fueron de tu interés o no te parecen adecuadas, por favor deja un comentario o dale un ( G+1 ) si no tienes mucho tiempo.

Entrada destacada

Arquitectura de N-Capas con Visual Studio .NET y AngularJS

Este artículo es el primero de una serie de artículos en que quiero explicar mi visión de la programación por capas. La Arquitectura ...