Optimizar las consultas en Visual Basic

Artículo : Q172199

Visual Basic nos permite recuperar datos de bases de datos Jet (ficheros MDB) usando Structured Query Language (SQL). Estas operaciones pueden ser más eficientes siguiendo algunas de las pautas que veremos a continuación.

Este artículo asume que estamos empleando Jet. Si usamos ODBC muchos de estos puntos pueden seguir aplicándose. Para más información consulta la las siguientes palabras en la Microsoft Knowledge Base:

   ODBC and Optimizing and Tables

Analizador de rendimientoSi tienes Access 95 o 97 puedes emplear el Analizador de Rendimiento (Herramientas|Analizar|Rendimiento) que nos aconsejará si debemos hacer algún cambio en las consultas.

Diseño de las tablasCuando definimos un campo de una tabla debemos escoger el tipo de dato que menos ocupe porque con esto incrementamos el número de registros que pueden caber en una página.Los campos que se empleen en joins deben ser del mismo tipo o de tipos compatibles.

Compactar las Bases de DatosTiene dos beneficios para el rendimiento:

Las bases de datos de Microsoft Jet usan un método de optimización basado en el coste. Cuando crece la base de datos el esquema de optimización puede dejar de ser eficiente. Compactando la base de datos se actualizan las estadísticas y se vuelven a optimizar todas las consultas.

Cuando la base de datos crese se fragmenta. Al compactar se escriben todos los datos de cada tabla en páginas contiguas del disco duro, aumentando el rendimiento de las búsquedas secuenciales.Para compactar la base de datos debemos emplear la instrucción CompactDatabase. Este ejemplo compacta la base de datos y hace un backup:

   DBEngine.CompactDatabase "C:\VB\BIBLIO.MDB", "C:\VB\BIBLIO2.MDB"   Kill "C:\VB\BIBLIO.BAK"   Name "C:\VB\BIBLIO.MDB" As "C:\VB\BIBLIO.BAK"   Name "C:\VB\BIBLIO2.MDB" As "C:\VB\BIBLIO.MDB"

Si la base de datos se modifica mucho habría que pensar en compactarla todos los días.

Evitar expresiones en el resultado de una consultaLas expresiones en los resultados de una consulta producen problemas de optimización si la consulta se usa después como entrada de otra consulta con condiciones sobre la salida calculada. En el siguiente ejemplo se usa Query1 como entrada de una segunda

SELECT:

   Dim DB As Database   Dim RS As RecordSet   Set DB = DBEngine.Workspaces(0).Opendatabase("Biblio.MDB")   DB.CreateQueryDef("Query1", _      "SELECT IIF(Au_ID=1,'Hello','Goodbye') AS X FROM Authors")   Set RS = DB.OpenRecordSet("SELECT * FROM Query1 WHERE X='Hello'")

Como la expresión IIF() no puede ser optimizada, la condición WHERE de la segunda cláusula SELECT tampoco puede optimizarse. Si puedes usa una sola instrucción SQL :

   Set RS = DB.OpenRecordSet("SELECT * FROM Authors WHERE Au_ID=1")

Para consultas más complejas extrae también los campos de la condición :

   DB.CreateQueryDef("Query1", _      "SELECT IIF(Au_ID=1,'Hello','Goodbye') AS X, Au_ID, FROM Authors")   Set RS = DB.OpenRecordSet("SELECT * FROM Query1 WHERE Au_ID=1")

Si no puedes evitar valores calculados en la salida de la consulta, ponlos en la consulta de mayor nivel y no en las de nivel inferior (subconsultas).

Saca sólo los campos que necesitesCuando crees una consulta, retorna sólo los campos que necesites. Si un campo no tiene que estar en una SELECT no lo pongas. El ejemplo anterior de obtener campos mara hacer más eficientes las consultas anidadas es una excepción.

GROUP BY, Joins, y Funciones de AgregaciónEsto es útil cuando estás haciendo un join de dos tablas. Por ejemplo, si haces join de dos tablas por el campo Customer Name y haces también un GROUP BY por el campo Customer Name, asegúrate de que tanto el campo del GROUP BY (Customer Name) y el campo de la función de agregación (Sum, Count, etc.) sean de la misma tabla.

NOTA: Esta consulta es menos eficiente porque la función SUM se hace sobre la tabla Ord y la cláusula GROUP BY sobre la tabla Cust:

   SELECT Cust.CustID,          FIRST(Cust.CustName) AS CustName,          SUM(Ord.Price) AS Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID   GROUP BY Cust.CustID

Es más eficiente hacer el GROUP BY sobre el campo Ord.CustID:

   SELECT Ord.CustID,          FIRST(Cust.CustName) AS CustName,          SUM(Ord.Price) AS Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID   GROUP BY Ord.CustID

NOTA: Las funciones First y Last no tienen la sobrecarga de otras funciones de agregación.

Pocos campos en los GROUP BYCuantos más campos haya en la cláusula GROUP BY más tarda la consulta en ejecutarse. Usa la función First para ayudar a reducir el número de campos requeridos por el GROUP BY.

Menos eficiente:

   SELECT Cust.CustID,          Cust.CustName,          Cust.Phone,          SUM(Ord.Price) AS Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID   GROUP BY Cust.CustID, Cust.CustName, Cust.Phone

Más eficiente:

   SELECT Ord.CustID,          FIRST(Cust.CustName) AS CustName,          FIRST(Cust.Phone) AS Phone,          SUM(Ord.Price) AS Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID   GROUP BY Ord.CustID

Anidar la cláusula GROUP BY antes de hacer un JoinSi haces join de dos tablas y sólo agrupas por campos de una de ellas es mejor partir la SELECT en dos consultas haciendo la SELECT con GROUP BY anidada dentro de la que tiene el JOIN.

Menos eficiente:

   SELECT Ord.CustID,          FIRST(Cust.CustName) AS CustName,          FIRST(Cust.Phone) AS Phone,          SUM(Ord.Price) AS Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID   GROUP BY Ord.CustID

Más eficiente:

   Query1:   SELECT CustID, SUM(Price) AS Total   FROM Ord   GROUP BY CustID

   Query2:   SELECT Query1.CustID, Cust.CustName, Cust.Phone, Query1.Total   FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID

Indexar los campos usados en un JoinCuando haces join de tablas intenta indexar los dos campos de la join. Esto acelera la ejecución de la consulta porque permite al optimizador de consultas emplear una estrategia interna más sofisticada.En cambio, si sabes que una tabla va a permanecer relativamente pequeña (ocupando uno o dos páginas de dos Kbytes) puede ser más eficiente eliminar los índices porque esas páginas pueden estar en memoria.

Añadir íncides para acelerar las búsquedas y ordenacionesAñade un índice por todos los campos que se usen en una join o en una condición. Con el uso de la tecnología Rushmore el Microsoft Jet 2.0 o superior puede sacar ventaja de múltiples índices en una tabla, lo que hace ventajoso índices de múltiples campos.

Evita condiciones sobre campos calculados o columnas no indexadas cuando sea posible.

Emplea las ordenaciones juiciosamente, especialmente con campos calculados o no indexados.

Emplea expresiones optimizablesIntenta construir las consultas de manera que puedan ser optimizadas con la tecnología Rushmore. Rushmore es una tecnología de acceso a datos que permite hacer las consultas más eficientemente. Con Rushmore, cuando empleas ciertos tipos de expresiones en las condiciones de una consulta ésta se ejecutará mucho más rápido. Rushmore no acelera automáticamente las consultas. Debes construirlas de una cierta manera para que Rushmore sea capaz de acelerarlas.

Consulta la sección de REFERENCIAS que hay al final de este artículo para encontrar información más específica.

Usa COUNT(*) en vez de COUNT([Campo])Microsoft Jet está especialmente optimizado para permitir que COUNT(*) se ejecute mucho más rápido que COUNT([Campo]).

NOTA: Estas dos operaciones tienen también significativas diferencias:

Count(*) cuenta el número de filas devueltas.Count([Campo]) cuenta las filas donde [Campo] no es NULL.

Evita el uso de LIKE con ParámetrosDebido a que el valor de los parámetros es desconocido en el momento de la compilación de la consulta, no se usarán los índices. Puedes mejorar el rendimiento concatenando el valor de los parámetros como un literal en la sentencia SQL.

Consulta la sección REFERENCIAS al final de este artículo para encontrar información más específica.

Evita el uso de LIKE con comodines al principioSi usas el operador LIKE con un carácter comodín para una búsqueda aproximada, emplea sólo un asterisco al final de la cadena de caracteres para asegurarte de que se puedan aprovechar los índices. Por ejemplo, esta condición emplearía índices:

   Like "Smith"   Like "Sm*"

Esta condición no emplearía índices:

   Like "*sen"   Like "*sen*"

Si usas condiciones para retringir los valores en un campo que empleas para una join, prueba a poner las condiciones para el campo de cada una de las tablas que usas en la join porque puede haber grandes diferencias de rendimiento.

Usa tablas intermediasUsa SELECT INTO para crear tablas temporales, especialmente si los resultados de la consulta se emplearán en varias otras consultas.

Evita subconsultas con Avoid NOT INEl uso de subconsultas con NOT IN está pobremente optimizado. Es más eficiente convertir la consultas anidadas o OUTER JOINs. El siguiente ejemplo busca vendedores sin pedidos:

Menos eficiente:

      SELECT Customers.*      FROM Customers      WHERE Customers.[Customer ID]            NOT IN (SELECT [Customer ID] FROM Orders);

Más eficiente:

      SELECT Customers.*      FROM Customers LEFT JOIN Orders           ON Customers.[Customer ID] = Orders.[Customer ID]      WHERE ((Orders.[Customer ID] Is Null));

REFERENCIASPara más información acerca de cómo optimizar las consultas con la tecnología Rushmore:

En la ayuda del Microsoft Visual Basic 4.0, busca "Rushmore technology", then:

  "Optimizing Queries with Rushmore Technology"

In Visual Basic 5.0 Books Online, search for "Rushmore Technology", then:

  "Optimizing Queries"

In Microsoft Access 2.0 Help, search for "Rushmore Technology", then:

  "Optimizing Queries with Rushmore Technology"  "Combining Optimizable Expressions for Rushmore"

In Microsoft Access 95 Help, search for "Rushmore Technology." In Microsoft Access 97 Help, search for "Rushmore Queries."

The Microsoft Jet Database Engine Programmer's Guide.

For more information about creating queries in code, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q117544   TITLE : INF: Query by Form (QBF) Using Dynamic QueryDef (2.0)

   ARTICLE-ID: Q136062   TITLE : INF: Query by Form (QBF) Using Dynamic QueryDef (7.0/97)