El post que buscas se encuentra eliminado, pero este también te puede interesar

Agilizar Consultas a Bases de Datos (SQL, MySQL y Oracle)

Anuncios

Teoría de la optimización de Bases de Datos

Hay muchas maneras de optimizar las bases de datos y las consultas. El siguiente método siempre me ha funcionado y es el siguiente.

1. Analizar el esquema de la base de datos y ver si tiene sentido.
Muy a menudo, las bases de datos tienen diseños malos y no están normalizadas. Esto puede afectar considerablemente la velocidad de su base de datos. Como caso general, aprender las 3 Formas Normales y aplíquelas en todo momento. Las Formas Normales por encima de la 3 ª Forma Normal son llamadas a menudo Formas De-Normalizadoras, pero lo que esto significa es que rompen algunas reglas para hacer la base de datos más rápida.

Lo que yo sugiero es apegarse a la 3º Forma Normal, excepto si usted es un DBA (lo que significa que usted sabe las formas posteriores y sabe lo que está haciendo). La normalización después de la FN 3º se hace a menudo en un momento posterior, no durante el diseño.

2. Únicamente consultar lo que es realmente necesario
Filtrar tanto como sea posible
La cláusula WHERE es la parte más importante para la optimización.
Seleccione sólo los campos que necesite
Nunca use "Select *" - Especifique sólo los campos que necesite, será más rápido y utilizará menos ancho de banda.
Tenga cuidado con las uniones
Las uniones son costosas en términos de tiempo. Asegúrese de que utiliza todas las claves que relacionan las dos tablas y no se una a tablas sin usar - siempre intente unir sobre campos indexados. El tipo de unión es también importante (INNER, OUTER, ...).
Optimizar las consultas y procedimientos almacenados (La mayoría se ejecutan primero)
¡OJO!
Las consultas son muy rápidas. En general, se puede recuperar muchos registros en menos de un segundo, incluso con uniones, clasificación y cálculos. Como regla general, si su consulta toma más de un segundo, es probable que pueda optimizar.
Comience con las consultas que más se utilizan, así como las consultas que tardan más tiempo en ejecutarse.

3. Agregar, eliminar o modificar índices
Si la consulta hace barridos completos de tabla, los índices y el filtrado adecuado pueden resolver lo que normalmente es un proceso muy lento. Todas las llaves primarias necesitan índices, ya que hacen que las uniones sean más rápidas. Esto también significa que todas las tablas necesitan una llave principal. También puede agregar índices en campos que son utilizados a menudo para filtrar las cláusulas WHERE.
Usted probablemente deseará, especialmente, usar índices en enteros, booleanos, y números en general. Por otro lado, es probable que no desee utilizar los índices de Blobs, VARCHAR y cadenas tipo Long.
Tenga cuidado con la adición de los índices, ya que es necesario que a estos les sea dado mantenimiento por la base de datos. Si hace muchos cambios en ese campo, el mantenimiento de los índices puede tardar más tiempo de lo que ahorra.
En el mundo de Internet, las tablas de sólo lectura son muy comunes. Cuando una tabla es de sólo lectura, puede agregar índices con menor impacto negativo, porque no es necesario que los índices se les de mantenimiento (o sólo rara vez necesitan mantenimiento).

4. Cambie Consultas a Procedimientos Almacenados (SP)
Los procedimientos almacenados son por lo general mejores y más rápidos que las consultas por las siguientes razones:
1. Los Procedimientos Almacenados se compilan (código SQL no lo es), por lo que más rápido que el código SQL.
2. SP no utilizan tanto ancho de banda, porque puedes hacer muchas consultas en un SP. También, los SP están en el servidor hasta que los resultados finales son devueltos.
3. Los procedimientos almacenados se ejecutan en el servidor, que suele ser más rápido.
4. Los cálculos en el código (Visual Basic, Java, C + +, C#, etc.) no son tan rápidos como SP en la mayoría de los casos.
5. Mantienen el código de acceso a la DB separado de la capa de presentación, lo que hace más fácil de mantener (modelo 3 niveles).
5. Retire Vistas innecesarias
Las vistas son un tipo especial de consulta - no son tablas. Estas son lógicas y no físicas de manera que cada vez que ejecutas SELECT FROM MiVista, ejecutas la consulta que hace la vista y la consulta en la vista.
Si siempre necesitan la misma información, las vistas pueden ser buenas.
Si usted tiene que filtrar la vista, es como ejecutar una consulta en una consulta - que es más lento.
6. Optimizar la configuración de DB
Puede optimizar la base de datos de muchas maneras. Actualizando las estadísticas utilizadas por el optimizador, ejecutando las opciones de optimización, hacer la base de datos de sólo lectura, etc .. Para eso se necesita un conocimiento más amplio de la base de datos y es ejecutada principalmente por el Administrador de Base de Datos (DBA).
7. Uso de Analizadores de consulta
En muchas bases de datos, hay una herramienta para ejecutar y optimizar consultas. SQL Server tiene una herramienta llamada el analizador de consultas, lo cual es muy útil para optimizar. Puede escribir consultas, ejecutarlas y, más importante, ver el plan de ejecución. La ejecuci{on puede ser utilizada para entender lo que SQL Server hace con la consulta.
Optimización en la práctica
Ejemplo 1:
Quiero recuperar el nombre y el sueldo de los empleados del departamento de Investigación y Desarrollo.
Original:
Consulta: SELECT * FROM Empleados
En el programa: Agregar un filtro de Departamento o utilizar el comando: IF = Departamento de InvyD -
Corregido:
SELECT Nombre, Salario FROM Empleados WHERE Dept = InvyD -
En la versión corregida, la base de datos filtra los datos, ya que filtra más rápido que el programa.
Además, sólo se necesita el nombre y salario, por lo que sólo pedimos eso.
Los datos que viajan en la red será mucho menores y por lo tanto su rendimiento mejorará.
Ejemplo 2 (clasificación):
Original:
SELECT Nombre, Salario
FROM Empleados
WHERE Dept = “InvyD "
ORDER BY Sueldo
¿Es necesaria la cláusula ORDER BY? A menudo, la gente usa ORDER BY en el desarrollo para asegurarse de que los datos devueltos sean correctos, elimínelo si no lo necesita.
Si necesita ordenar los datos, hágalo en la consulta, no en el programa.
Ejemplo 3:
Original:
FOR i = 1 TO 2000
Consulta de llamada: SELECT Salario FROM Empleados WHERE EmpID = Parameter (i)
Corregido:
SELECT Salario FROM Empleados WHERE EmpID> = 1 AND EmpID <= 2000
La consulta original implica el uso de un montón de ancho de banda y hará lento todo el sistema.
Usted debe hacer tanto como sea posible en la consulta o en el Procedimiento Almacenado (SP).
Aunque este ejemplo parece sencillo, hay ejemplos más complejos sobre este tema.
A veces, procesarlo es tan grande que pensamos que es mejor hacerlo en el código, pero probablemente no lo es.
A veces, el Procedimiento Almacenado sería mejor si creáramos una tabla temporal, insertar datos en ella y devolverla a ir y venir de 10.000 veces. Es posible que tenga una consulta más lento que ahorra tiempo en un mayor número de registros o que ahorra ancho de banda.
Ejemplo 4 (Uniones Débiles):
Usted tiene dos Tablas de pedidos y de clientes. Los clientes pueden tener muchos pedidos.
Original:
SELECT O.PrecioItem, C.Nombre
FROM Pedidos O, clientes C
Corregido:
SELECT O.PrecioItem, C.Nombre
FROM Pedidos O, clientes C
WHERE O.ClienteID = C.ClienteID
En ese caso, la unión no existía en absoluto o no estaba en todas las llaves. Eso devolvería tantos registros que su consulta podría tardar horas. Es un error común de los principiantes.
Corregido 2:
Dependiendo de la base de datos que utilice, tendrá que especificar el tipo de Unión que desee de diferentes maneras.
En SQL Server, la consulta tendría que ser corregida a:
SELECT O.PrecioItem, C.Nombre
FROM Pedidos O INNER JOIN Clientes ON O.Cliente C = C.ClienteID
Elija el tipo de Unión correcta (INNER, OUTER, LEFT, ...).
Tenga en cuenta que en SQL Server, Microsoft sugiere utilizar las Uniones (Joins) como en Corregida 2 en lugar de las Uniones en la cláusula WHERE, ya que será más optimizada.
Ejemplo 5 (Filtros débiles):
Este es un ejemplo más complicado, pero ilustra el filtrado en su mejor momento.
Tenemos dos tablas: Productos (ProductID, DescID, Precio) y Descripcion(DescID, LenguajID, Texto). Hay 100,000 productos y desafortunadamente los necesitamos a todos.
Hay 100 idiomas (LENGID = 1 = Español). Sólo queremos las descripciones en español de los productos.
Estamos a la espera de 100 000 productos (ProductName, Precio).
Primer intento:
SELECT D.Text AS ProductName, P.Precio
FROM Productos P INNER JOIN Descripcion D IN P.DescID = D.DescID
WHERE D.LengID = 1
Eso funciona, pero va a ser muy lento debido a que la base de datos debe coincidir con 100.000 registros, con 10.000.000 de registros y luego filtrar WHERE LengID = 1.
La solución es filtrar LengID = 1 antes de unir las tablas.
Corregido:
SELECT D.Text AS ProductName, P.Precio
FROM(SELECT DescID, Texto FROM Desde Descripcion WHERE D.LengID = 1) D
INNER JOIN Productos P = ON D.DescID P.DescID
Ahora será mucho más rápido. También debr{ia hacer de esa consulta un procedimiento almacenado para hacerlo más rápido.
Ejemplo 6 (Vistas):
CREATE VIEW v_Empleados AS
SELECT * FROM Empleados
SELECT * FROM v_ Empleados
Esto es igual que ejecutar SELECT * FROM Empleados dos veces.
Usted no debería usar la vista en este caso.
Si usted fuera a utilizar siempre los datos de los empleados de InvyD y no quisiera dar los derechos a todos para esa Tabla porque los salarios son confidenciales, se puede utilizar una vista así:
CREATE VIEW v_InvyDEmpleados AS
Seleccione Nombre, Salario FROM Empleados WHERE Dept = 1
(Dpto. 1 es InvyD).
Cedería entonces los derechos para ver v_R y DEmployees a algunas personas y restringe los derechos a la tabla Empleados para ser vistos por el DBA solamente.
Eso sería un buen uso de vistas.
SIEMPRE RECORDAR
1. Tratar con conjuntos pequeños
Mientras más pequeño sea el número de filas con las que se está trabajando, más rápido se encontrará lo que se está buscando.
Si el optimizador de consultas encuentra que el número de filas en la tabla es lo suficientemente pequeño, no se utilizarán índices en dicha tabla. El optimizador se dará cuenta de que es más rápido escanear las filas de la tabla para satisfacer la petición en lugar de atravesar una estructura de índice para encontrar las filas.
2. Limite el número de columnas devueltas
Al devolver los datos en sus aplicaciones, a menor cantidad de datos retornados, es más rápida la transmisión de información sobre la red. - esto es válido para la cantidad de filas devueltas, así como para el número de columnas devueltas. Es por eso que estoy en contra de utilizar la instrucción SELECT *, especialmente en un entorno de producción. En mi experiencia con la administración de bases de datos y el desarrollo, he visto muy pocas veces que han sido justificadas mediante una instrucción SELECT *. La razón de esto es doble. No tiene sentido traer de vuelta las columnas que no se van a utilizar. La segunda razón (que creo que es más importante) es que el uso de SELECT * puede romper el código existente. Consideremos el siguiente ejemplo.
Tengo una instrucción INSERT en mi entorno de producción. Puedo utilizar una instrucción SELECT * como fuente de datos en mi declaración INSERT. Esto no es gran cosa porque mi TablaFuente tiene el mismo número de columnas en ella como la TablaDestino.
SELECT INTO TablaDestino
(Fld1, Fld2, Fld3)
SELECT *
FROM TablaFuente.
En una situación de negocios se presenta en que tengo que agregar un campo a la tabla de TablaFuente.
ALTER TABLE TablaFuente
ADD Campo4 INT
La adición de este nuevo campo romperá mi declaración INSERT, lo que provocará problemas en mi entorno de producción.
3. La búsqueda de filas
La manera en que las filas se buscan en una tabla de base de datos será siempre una de las implementaciones más vitales en el entorno de su base de datos. El optimizador de consultas de SQL Server funciona de manera más eficiente para algunos cuando las declaraciones WHERE son comparadas con otros, cuando las declaraciones WHERE están basadas en la forma en que la declaración está escrita, incluso si el resultado de las declaraciones es el mismo.
En el ejemplo siguiente se utiliza la declaración IN() para especificar una serie de valores que se estarán buscadno. Para este ejemplo, supongamos que la columna OrderID como un índice no agrupado.
SELECT * FROM VentaProduct
WHERE OrderID IN(4494, 4495, 4496)
Esta afirmación es exactamente lo mismo que usar un operador OR para especificar los tres valores que se buscaban. Cualquier declaración provocará que SQL Server no utilice el índice en el campo y para recorrer las filas de la tabla en busca de los valores. Dado que los valores utilizados en el ejemplo son contiguos, puedo usar el operador BETWEEN en su lugar. Esto permitirá que el optimizador de consultas utilice eficazmente el índice.
SELECT * FROM ProductSales
WHERE OrderID BETWEEN 4494 AND 4496
En general, la mayoría de los tipos de declaraciones de exclusión en la cláusula WHERE hará que SQL Server no utilice un índice. Los siguientes son algunos ejemplos:
<>, !, OR, NOT IN, NOT EXISTS
La manera en que las declaraciones se utilizan también hace una diferencia. Si se es capaz de especificar el carácter de principio (s) de la declaración que está buscando, usted tendrá una mejor oportunidad de su declaración mediante un índice. Si se especifica un carácter comodín (%) antes de cualquier tipo de cadena de búsqueda, el optimizador no será capaz de utilizar un índice.
SELECT * FROM Estudiantes WHERE Apellido LIKE '%TR%'
4. Búsqueda de Fechas
Las búsquedas de Fecha son a veces un poco difíciles de realizar en la base de datos, por ejemplo, he visto numerosas situaciones donde las funciones de fecha se utilizan para las búsquedas de la fecha. En el siguiente ejemplo, los registros de ventas se recuperan de la tabla HistorialVentas el 15 de agosto de 2005:
SELECT IDVenta
FROM HistorialVentas
WHERE
MONTH(FechaVenta)= 8 AND
YEAR(FechaVenta) = 2005 AND
DAY(FechaVenta) = 15

Las funciones en la cláusula WHERE harán que SQL Server realice la función en cada fila que se busca, lo que significa que el índice no se usará. Es por eso que se desalienta que los valores de rendimientos de las funciones se utilicen como criterios en las consultas. El siguiente código muestra cómo se puede reescribir la instrucción para que un índice sea utilizado, y los resultados son devueltos de una manera mucho más rápida.
SELECT IDVenta
FROM HistorialVentas

WHERE
FechaVenta >= '8/15/2005' AND
FechaVenta < ‘8/16/2005'
Conclusión
Espero que esto le ayude a hacer las consultas más rápido y sus bases de datos estén más optimizadas. Esto debería hacer que su programa se vea mejor y, posiblemente, puede significar dinero, especialmente para aplicaciones de alta carga en la web en las que esto significa que el programa puede atender más transacciones por hora ya que a menudo se paga por la transacción.
Mientras que usted puede poner los ejemplos anteriores para practicar en la base de datos de su elección, los consejos anteriores son especialmente ciertos para las principales bases de datos como Oracle o SQL Server.
Código para medir rendimiento de Consultas SQL
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go
--Declaracion de la variable que nos permitira medir el tiempo
DECLARE @TiempoInicial DATETIME SET @TiempoInicial = GETDATE()
DECLARE @return_value int
-- ------------------------------- SENTENCIAS -------------------------------
EXEC @return_value = [dbo].[BuscarBecasPorId]
@Id_Becas = N'CEM004'
-- ------------------------------- SENTENCIAS -------------------------------
-- Realizar la diferencia entre el tiempo inicial y final de la ejecución de las sentencias
select 'Operacion completada en: ' + RTRIM(CAST(DATEDIFF(ms,@TiempoInicial, GETDATE())
AS VARCHAR(10))) + ' milisegundos'
go

Anuncios

1 comentario - Agilizar Consultas a Bases de Datos (SQL, MySQL y Oracle)