Ajuste del rendimiento de las consultas (SQL Server Compact)
Puede mejorar el rendimiento de la aplicación SQL Server Compact 4.0 optimizando las consultas que utiliza. En las siguientes secciones se describen las técnicas que pueden aplicarse para optimizar el rendimiento de las consultas.
Mejorar los índices
La creación de índices útiles es uno de los métodos más importantes para lograr un mejor rendimiento de las consultas. Los índices útiles ayudan a encontrar los datos con menos operaciones de E/S de disco y un menor uso de los recursos del sistema.
Para crear índices útiles, debe comprender cómo se utilizan los datos, los tipos y las frecuencias de ejecución de las consultas y cómo el procesador de consultas puede utilizar los índices para encontrar los datos con rapidez.
Una vez elegidos los índices que creará, examine las consultas más importantes, cuyo rendimiento es el factor que más afecta a la experiencia del usuario. Cree los índices específicamente para ayudar a estas consultas. Después de agregar un índice, vuelva a ejecutar la consulta para comprobar si el rendimiento ha mejorado. En caso negativo, quite el índice.
Al igual que en la mayoría de las técnicas de optimización del rendimiento, existen ventajas e inconvenientes. Por ejemplo, con más índices, es probable que las consultas SELECT se ejecuten con mayor rapidez. Sin embargo, las operaciones DML (INSERT, UPDATE y DELETE) reducirán su velocidad porque se deben mantener más índices con cada operación. Por consiguiente, si las consultas son principalmente instrucciones SELECT, el uso de más índices puede ser positivo. Si su aplicación lleva a cabo muchas operaciones DML, el número de índices que cree debería ser más moderado.
SQL Server Compact incluye compatibilidad para planes de presentación, que permiten evaluar y optimizar las consultas. SQL Server Compact utiliza el mismo esquema de planes de presentación que SQL Server 2008 R2, con la excepción de que SQL Server Compact utiliza un subconjunto de los operadores. Para obtener más información, vea el esquema de planes de presentaciones de Microsoft en https://schemas.microsoft.com/sqlserver/2004/07/showplan/.
En las siguientes secciones encontrará información adicional sobre la creación de índices útiles.
Crear índices muy selectivos
Los índices en las columnas utilizadas en la cláusula WHERE de las consultas importantes normalmente mejoran el rendimiento. Sin embargo, esto depende del grado de selectividad del índice. La selectividad es la proporción de filas resultantes respecto al total de filas. Si la proporción es baja, significa que el índice es muy selectivo, ya que puede deshacerse de la mayoría de las filas y reducir en gran medida el tamaño del conjunto de resultados. Por consiguiente, se trata de un índice muy útil. En cambio, un índice que no es selectivo no es tan útil.
Los índices únicos son los más selectivos. Solo puede coincidir una fila, lo que es realmente útil para las consultas que pretenden exactamente devolver una fila. Por ejemplo, un índice en una sola columna de id. servirá de ayuda para encontrar con rapidez una fila concreta.
Para evaluar la selectividad de un índice, ejecute los procedimientos almacenados sp_show_statistics en las tablas de SQL Server Compact. Por ejemplo, si está evaluando la selectividad de dos columnas, "Customer ID" y "Ship Via", puede ejecutar los siguientes procedimientos almacenados:
sp_show_statistics_steps 'orders', 'customer id';
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
------------------------------------------------------------
ALFKI 0 7 0
ANATR 0 4 0
ANTON 0 13 0
AROUT 0 14 0
BERGS 0 23 0
BLAUS 0 8 0
BLONP 0 14 0
BOLID 0 7 0
BONAP 0 19 0
BOTTM 0 20 0
BSBEV 0 12 0
CACTU 0 6 0
CENTC 0 3 0
CHOPS 0 12 0
COMMI 0 5 0
CONSH 0 4 0
DRACD 0 9 0
DUMON 0 8 0
EASTC 0 13 0
ERNSH 0 33 0
(90 rows affected)
Y
sp_show_statistics_steps 'orders', 'reference3';
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS
------------------------------------------------------------
1 0 320 0
2 0 425 0
3 0 333 0
(3 rows affected)
Los resultados indican que la columna "Customer ID" tiene un grado de duplicación mucho menor. Esto significa que un índice en esta columna será mucho más selectivo que un índice de la columna "Ship Via".
Para obtener más información acerca del uso de estos procedimientos almacenados, vea sp_show_statistics (SQL Server Compact 3.5), sp_show_statistics_steps (SQL Server Compact 3.5) y sp_show_statistics_columns (SQL Server Compact).
Crear índices de varias columnas
Los índices de varias columnas son extensiones naturales de los índices de una sola columna. Los índices de varias columnas son útiles para evaluar expresiones de filtro que coinciden con un conjunto de prefijos de columnas de clave. Por ejemplo, el índice compuesto CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) ayuda a evaluar las siguientes consultas:
... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'
Sin embargo, no será útil para esta consulta:
- ... WHERE "First Name" = 'John'
Al crear un índice de varias columnas, las columnas más selectivas deberían ubicarse en la parte izquierda de la clave. De este modo, el índice es más selectivo cuando coincide con varias expresiones.
Evitar el indizado de tablas pequeñas
Una tabla pequeña es aquella cuyo contenido cabe en una o pocas páginas de datos. Evite indizar tablas muy pequeñas porque normalmente es más eficaz realizar un examen de tablas. De este modo, se evita tener que cargar y procesar las páginas de índice. Si no crea un índice en las tablas muy pequeñas, está eliminando la posibilidad de que el optimizador seleccione una.
SQL Server Compact almacena datos en páginas de 4 KB. El recuento de páginas puede calcularse aproximadamente mediante la siguiente fórmula, aunque el recuento real puede ser un poco mayor debido a la sobrecarga del motor de almacenamiento.
<suma del tamaño de las columnas en bytes> * <número de filas>
<número de páginas> = -----------------------------------------------------------------
4096
Por ejemplo, supongamos que una tabla tiene el siguiente esquema:
Nombre de columna |
Tipo (tamaño) |
|---|---|
Order ID |
INTEGER (4 bytes) |
Product ID |
INTEGER (4 bytes) |
Unit Price |
MONEY (8 bytes) |
Quantity |
SMALLINT (2 bytes) |
Discount |
REAL (4 bytes) |
La tabla tiene 2820 filas. De acuerdo con la fórmula, serán necesarias 16 páginas para almacenar los datos:
<número de páginas> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 páginas
Elegir el contenido que se indiza
Es recomendable que siempre cree índices en las claves principales. También suele ser muy útil crear índices en claves externas, puesto que tanto las claves principales como las externas se utilizan con frecuencia para combinar tablas. Los índices de estas claves permiten al optimizador calcular los algoritmos de combinación de índices más eficaces. Si la consulta combina tablas utilizando otras columnas, a menudo es útil crear índices en esas columnas por la misma razón.
Cuando se crean las restricciones de claves principales y externas, SQL Server Compact crea automáticamente índices para ellas y las utiliza para optimizar las consultas. Recuerde que es aconsejable crear claves principales y externas lo más pequeñas posible, ya que las combinaciones son más rápidas.
Utilizar índices con cláusulas de filtro
Los índices pueden utilizarse para acelerar la evaluación de ciertos tipos de cláusulas de filtro. Si bien todas las cláusulas de filtro reducen el conjunto de resultados final de una consulta, algunas de ellas también ayudan a reducir la cantidad de datos que se deben examinar.
Un argumento de búsqueda (SARG) limita una búsqueda porque especifica la coincidencia exacta, un intervalo de valores o una conjunción de dos o más elementos combinados con AND. Presenta uno de los siguientes formatos:
- Columna operador <constante o variable>
- <constante o variable> operador Columna
Entre los operadores SARG se incluyen =, >, <, >=, <=, IN, BETWEEN y, en ocasiones, LIKE (en casos de coincidencia de prefijos, tales como LIKE 'John%'). Un argumento SARG puede incluir varias condiciones combinadas con un AND. Los argumentos SARG pueden ser consultas que coinciden con un valor específico, por ejemplo:
"Customer ID" = 'ANTON'
'Doe' = "Last Name"
Un argumento SARG también pueden ser una consulta que coincide con un intervalo de valores, por ejemplo:
"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')
Una expresión que no utilice operadores SARG no mejorará el rendimiento porque el procesador de consultas de SQL Server Compact debe evaluar cada fila para determinar si cumple la cláusula de filtro. Por consiguiente, un índice no es de utilidad en expresiones que no utilizan operadores SARG. Entre los operadores que no son SARG se incluyen NOT, <>, NOT EXISTS, NOT IN, NOT LIKE y funciones intrínsecas.
Utilizar el optimizador de consultas
Al determinar los métodos de acceso a las tablas base, el optimizador de SQL Server Compact determina si existe un índice para una cláusula SARG. Si existe un índice, el optimizador lo evalúa calculando cuántas filas se devuelven. A continuación, calcula el costo necesario para buscar las filas correspondientes utilizando el índice. Elegirá un acceso con índices si el costo es menor que el examen de la tabla. Un índice es potencialmente útil si se utiliza su primera columna o conjunto de prefijos de columnas en el argumento SARG y éste establece un límite inferior, superior o ambos, para limitar la búsqueda.
Tiempo de respuesta frente a tiempo total
El tiempo de respuesta es el tiempo necesario para que una consulta devuelva el primer registro. El tiempo total es el tiempo necesario para que la consulta devuelva todos los registros. Para una aplicación interactiva, el tiempo de respuesta es importante porque es el tiempo que el usuario tarda en recibir información visual de que una consulta se está procesando. Para una aplicación por lotes, el tiempo total refleja el rendimiento general. Debe determinar cuáles son los criterios de rendimiento de la aplicación y las consultas y, a continuación, realizar un diseño consecuente.
Por ejemplo, supongamos que la consulta devuelve 100 registros y se utiliza para rellenar una lista con los cinco primeros registros. En este caso, el tiempo que se tardará en devolver los 100 registros no supone ningún problema. Sin embargo, desea que la consulta devuelva los primeros registros con rapidez para poder rellenar la lista.
Pueden realizarse muchas operaciones sin necesidad de tener que almacenar resultados intermedios. Este tipo de operaciones se conocen como operaciones en canalización. Algunos ejemplos de operaciones en canalización son las proyecciones, las selecciones y las combinaciones. Las consultas implementadas con estas operaciones pueden devolver resultados inmediatamente. Otras operaciones, como SORT y GROUP-BY, necesitan todas sus entradas antes de devolver resultados a sus operaciones primarias. Este tipo de operaciones se conocen como operaciones que requieren materialización. Las consultas implementadas con estas operaciones suelen sufrir un retraso inicial debido a la materialización. Después de este retraso inicial, los registros se devuelven con mucha rapidez.
Las consultas con requisitos de tiempo de respuesta deberían evitar la materialización. Por ejemplo, el uso de un índice para implementar ORDER-BY ofrece un mejor tiempo de respuesta que la ordenación. La siguiente sección describe este proceso con más detalle.
Crear índices en las columnas ORDER-BY/GROUP-BY/DISTINCT para obtener un mejor tiempo de respuesta
Las operaciones ORDER-BY, GROUP-BY y DISTINCT son todas tipos de ordenación. El procesador de consultas de SQL Server Compact implementa la ordenación de dos modos distintos. Si los registros ya están ordenados por un índice, el procesador solo tiene que usar el índice. De lo contrario, el procesador debe utilizar una tabla de trabajo temporal para ordenar primero los registros. Esta ordenación preliminar puede provocar retrasos iniciales considerables en dispositivos con una CPU lenta y memoria limitada, y debería evitarse si el tiempo de respuesta es importante.
En el contexto de índice con varias columnas, para que ORDER-BY o GROUP-BY tengan en cuenta un índice concreto, las columnas ORDER-BY o GROUP-BY deben coincidir con el conjunto de prefijos de columnas de índice en el orden exacto. Por ejemplo, el índice CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) puede ayudar a optimizar las siguientes consultas:
... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...
No ayudará a optimizar:
... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...
Para que una operación DISTINCT tenga en cuenta un índice de varias columnas, la lista de proyección debe coincidir con todas las columnas de índice, aunque no es necesario que estén en el orden exacto. El índice anterior puede ayudar a optimizar las siguientes consultas:
... DISTINCT "Last Name", "First Name" ...
... DISTINCT "First Name", "Last Name" ...
No ayudará a optimizar:
... DISTINCT "First Name" ...
... DISTINCT "Last Name" ...
Nota
Si la consulta siempre devuelve filas únicas, no especifique la palabra clave DISTINCT, ya que solo aumenta la sobrecarga.
Volver a escribir subconsultas para usar operadores JOIN
En ocasiones puede volver a escribir una subconsulta para que utilice operadores JOIN y mejorar el rendimiento. La ventaja de crear un operador JOIN consiste en que puede evaluar tablas en un orden distinto del definido por la consulta. La ventaja de usar una subconsulta es que normalmente no es necesario examinar todas las filas de la subconsulta para evaluar la expresión de ésta. Por ejemplo, una subconsulta EXISTS puede devolver TRUE al ver la primera fila que coincida.
Nota
El procesador de consultas de SQL Server Compact siempre vuelve a escribir la subconsulta IN para que utilice un operador JOIN. No es necesario que aplique este sistema con las consultas que no contienen la cláusula de subconsulta IN.
Por ejemplo, para determinar todos los pedidos que como mínimo contienen un elemento con un descuento del 25% o más, puede utilizar la siguiente subconsulta EXISTS:
SELECT "Order ID" FROM Orders O
WHERE EXISTS (SELECT "Order ID"
FROM "Order Details" OD
WHERE O."Order ID" = OD."Order ID"
AND Discount >= 0.25)
También puede escribirla utilizando un operador JOIN:
SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"
OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25
Limitar mediante combinaciones externas JOIN
Los operadores OUTER JOIN se diferencian de los operadores INNER JOIN en que el optimizador no intenta cambiar el orden de combinación de las tablas OUTER JOIN como en las tablas INNER JOIN. Primero se obtiene acceso a la tabla externa (la tabla izquierda en el operador LEFT OUTER JOIN y la tabla derecha en el operador RIGHT OUTER JOIN) y después a la tabla interna. El orden de combinación fijo puede derivar en planes de ejecución que no son óptimos.
Para obtener más información acerca de una consulta que contenga INNER JOIN, vea Microsoft Knowledge Base.
Usar consultas parametrizadas
Si su aplicación ejecuta una serie de consultas que solamente difieren en algunas constantes, puede mejorar el rendimiento utilizando una consulta parametrizada. Por ejemplo, para devolver pedidos por clientes distintos, puede ejecutar la siguiente consulta:
SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?
El rendimiento de las consultas parametrizadas mejora si se compila la consulta solo una vez y se ejecuta el plan compilado varias veces. En la programación, debe conservar el objeto del comando que contiene el plan de consulta en la caché. Si se destruye el objeto del comando anterior y se crea uno de nuevo, se destruye el plan en la caché. Si esto sucede, la consulta debe recompilarse. Si debe ejecutar varias consultas parametrizadas de forma intercalada, puede crear varios objetos del comando, cada uno de los cuales guarda en la caché el plan de ejecución de una consulta parametrizada. De este modo no es necesario recompilarlas todas.
Realizar consultas solo cuando sea necesario
El procesador de consultas de SQL Server Compact es una herramienta eficaz que permite consultar los datos almacenados en la base de datos relacional. Sin embargo, todos los procesadores de consultas conllevan intrínsecamente un costo asociado. Antes de llevar a cabo el plan, debe compilar, optimizar y generar el plan de ejecución. Esto es especialmente necesario en las consultas simples que finalizan rápidamente. Por consiguiente, la implementación de una consulta puede mejorar enormemente el rendimiento. Si cada milésima de segundo cuenta en su componente fundamental, es aconsejable que considere la posibilidad de implementar las consultas simples por sí mismo. Para las consultas grandes y complejas, es mejor que este trabajo lo realice el procesador de consultas.
Por ejemplo, supongamos que desea consultar el id. de cliente de una serie de pedidos ordenados por sus id. de pedido. Hay dos formas de hacerlo. Puede realizar los pasos siguientes para cada consulta:
Abra la tabla base Orders.
Busque la fila utilizando el "Order ID" específico.
Recupere el "Customer ID".
O puede ejecutar la siguiente consulta para cada búsqueda:
SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>
La solución basada en consultas es más simple pero también más lenta que la solución manual, porque el procesador de consultas de SQL Server Compact traduce la instrucción SQL declarativa a las tres mismas operaciones que podrían implementarse manualmente. Estos tres pasos se ejecutan después en secuencia. El método que elija dependerá de si en su aplicación es más importante la simplicidad o el rendimiento.