optimizar - sintaxis sql

Administrar y depurar consultas SQL en MS Access (9)

MS Access tiene capacidades limitadas para gestionar consultas SQL sin formato: el editor es bastante malo, no resalta la sintaxis, reformatea el SQL sin formato en una cadena larga y no puede insertar comentarios.

La depuración de consultas SQL complejas también es dolorosa: o bien hay que dividirla en muchas consultas más pequeñas que se vuelven difíciles de administrar cuando cambia el esquema o se termina con una consulta gigante que es una pesadilla para depurar y actualizar.

¿Cómo gestiona sus complejas consultas SQL en MS Access y cómo las depura?

Editar
Por el momento, estoy usando simplemente Notepad ++ para algunos colores de sintaxis y SQL Pretty Printer para reformatear sensiblemente el SQL sin procesar de Access.
El uso de un repositorio externo es útil, pero siempre existe el riesgo de que las dos versiones no estén sincronizadas y aún tenga que eliminar los comentarios antes de intentar la consulta en Access ...

La depuración es más un desafío. Si una sola columna está desactivada, suele ser bastante fácil de arreglar. Pero asumo que tienes tareas de depuración más complejas que debes realizar.

Cuando estoy desconcertado, normalmente empiezo a depurar con la cláusula FROM . Remonto a todas las tablas y sub-consultas que comprenden la consulta más grande, y me aseguro de que las uniones estén definidas correctamente.

Luego reviso mi cláusula WHERE . Ejecuto muchas consultas simples en las tablas, y en las consultas secundarias que ya he verificado o en las que ya confío, y me aseguro de que cuando ejecuto la consulta más grande, obtengo lo que espero con las condiciones WHERE en su lugar. JOIN condiciones de JOIN al mismo tiempo.

Compruebo las definiciones de mi columna para asegurarme de que estoy recuperando lo que realmente quiero ver, especialmente si las fórmulas involucradas son complicadas. Si tiene algo complicado como una subconsulta coordinada en una definición de columna

Luego verifico si estoy agrupando los datos correctamente, asegurándome de que " DISTINCT " y " UNION " sin UNION ALL no eliminen los duplicados necesarios.

No creo haber encontrado una consulta SQL que no pueda ser desglosada de esta manera. No siempre soy tan metódico como esto, pero es una buena manera de comenzar a romper un verdadero obstáculo.

Una cosa que podría recomendar cuando escriba sus consultas es esta: Nunca use SELECT * en el código de producción. Seleccionar todas las columnas de esta manera es una pesadilla de mantenimiento, y genera grandes problemas cuando los esquemas subyacentes cambian. Siempre debe escribir cada columna si escribe código SQL que mantendrá en el futuro. Me ahorré mucho tiempo y me preocupé simplemente por deshacerme de " SELECT * " en mis proyectos.

La desventaja de esto es que esas columnas adicionales no aparecerán automáticamente en consultas que se refieren a consultas " SELECT * ". Pero debe saber cómo sus consultas están relacionadas entre sí, de todos modos, y si necesita las columnas adicionales, puede volver y agregarlas.

Hay algunas molestias involucradas en mantener un repositorio de código, pero si tiene un software de control de versiones, la molestia vale más que eso. He oído hablar de maneras de versionar el código SQL escrito en las bases de datos de Access, pero desafortunadamente, nunca los he usado.

Para la depuración, los edito en un editor de texto separado que me permite formatearlos de manera sensata. Cuando encuentro que necesito hacer cambios, edito la versión en el editor de texto y la vuelvo a pegar en Access, nunca editando la versión en Access.

Sigue siendo un PITA importante.

Si está realizando consultas realmente complejas en MS Access, consideraría mantener un depósito de esas consultas en algún lugar fuera de la base de datos de Access ... por ejemplo, en un archivo .sql que luego puede editar en un editor como Tipo que proporcionará resaltado de sintaxis. Te requerirá que actualices las consultas en ambos lugares, pero es posible que termines encontrando práctico tener un lugar "oficial" que esté formateado y resaltado correctamente.

O, si es posible, cambie a SQL Server 2005 Express Edition, que también es gratuito y le proporcionará las características que desee a través de SQL Management Studio (también gratis).

Similar a recursivo, uso un editor externo para escribir mis consultas. Uso Notepad ++ con la extensión Light Explorer para mantener varios scripts a la vez, y Notepad2 para scripts únicos. (Soy un poco parcial con los editores basados en Scintilla).

Otra opción es usar el SQL Server Management Studio Express gratuito, que viene con SQL Server Express. (EDITAR: Lo siento, EdgarVerona , ¡no me di cuenta de que ya lo has mencionado!) Normalmente lo uso para escribir consultas SQL en lugar de usar Access, porque de todos modos utilizo ODBC para vincular a un back-end de SQL Server. Tenga en cuenta que las diferencias en la sintaxis de T-SQL, utilizadas por SQL Server, y Jet SQL, utilizadas por Access MDB''s, a veces son sustanciales.

¿Estás hablando aquí de lo que MS-Access llama ''consultas'' y llamadas de SQL ''vistas'' o acerca de las consultas ''paso de MS-Access'' que son consultas SQL? ¡Alguien podría perderse fácilmente! Mi solución es la siguiente

gratis SQL Server Management Studio Express, donde elaboraré y probaré mis consultas
una tabla de consulta en el lado del cliente, con un campo para el nombre de la consulta ( id_Query ) y otro ( queryText , tipo de memo) para la consulta misma.

Luego tengo una pequeña función getSQLQuery en mi código VBA para usar cuando necesito ejecutar una consulta (devolviendo un conjunto de registros o no):

Dim myQuery as string, _ rsADO as ADODB.recorset rsADO = new ADODB.recordset myQuery = getSQLQuery(myId_Query) ''if my query retunrs a recordset'' set rsADO = myADOConnection.Execute myQuery ''or, if no recordset is to be returned'' myADOConnection.Execute myQuery

Para las vistas, incluso es posible mantenerlas del lado del servidor y referirse a ellas desde el lado del cliente

set rsADO = myADOConnection.execute "dbo.myViewName"

Supongo que no escribo SQL complejo, porque la mayoría de las veces no tengo ningún problema con el editor de Access SQL. Esto se debe a que, en su mayor parte, utilizo QBE para escribir el SQL y solo sumergirme en la vista de SQL para hacer las cosas que el QBE no admite (como combinaciones no equitativas, algunas formas de subconsultas, UNIÓN, etc. .). Esto no quiere decir que no tenga ningún SQL con el que sea muy difícil trabajar, pero eso se debe principalmente a que ES LO MALO ESCRITO, y es culpa mía , no culpa de Access. Tengo un horrible QueryDef guardado en una aplicación que ha estado en producción desde 1997 y que tiene un SQL de 11.934 caracteres. Y, sí, es horrible resolver problemas. Y casi cualquier edición que hago rompe algo. Pero eso es porque es MALO SQL.

Por qué alguien querría escribir su SQL a mano como una regla general, no puedo decirlo. Para cualquier cosa menos el SQL más trivial, me parece que tiene más problemas de lo que vale.

Este tipo de cosas me parece como otro caso de personas que se resisten a la forma predeterminada de acceso de hacer las cosas. Casi siempre, esto ocurre con usuarios experimentados en otros entornos de programación que son demasiado impacientes para probar las cosas como lo hace Access de manera predeterminada. El resultado final suele ser infeliz para todos.

Ampliando esta sugerencia de Smandoli:

NO: DoCmd.RunSQL ("SELECT ...") YES: strSQL = "SELECT ..." DoCmd.RunSQL (strSQL)

Si desea mantener el código SQL en un archivo externo, para editarlo con su editor de texto favorito (con coloreado de sintaxis y todo eso), podría hacer algo como este pseudo-código:

// On initialization: global strSQL f = open("strSQL.sql") strSQL = read_all(f) close(f) // To to the select: DoCmd.RunSQL(strSQL)

Esto puede ser un poco torpe, tal vez un poco torpe, pero evita los problemas de consistencia de editar, copiar y pegar.

Obviamente, esto no aborda directamente la depuración de SQL, pero la administración del código de una manera legible es una parte del problema.

Escribí Access SQL Editor , un complemento para Microsoft Access, porque escribo bastantes consultas de paso, y SQL más complejo dentro de Access. Este complemento tiene la ventaja de poder almacenar SQL formateado (¡con comentarios!) Dentro de su aplicación de Access. Cuando las consultas se copian a una nueva aplicación de Access, se conserva el formato. Cuando el editor incorporado anula su formateo, la herramienta mostrará su consulta original y le notificará la diferencia.

Actualmente no depura; si hubiera suficiente interés, buscaría esto, pero por el momento el conjunto de características se mantiene intencionalmente pequeño.

No es gratis por el momento, pero comprar una licencia es muy barato. Si no puede pagarlo, puede contactarme . Hay una prueba gratuita de 14 días aquí .

Una vez que esté instalado, puede acceder a él a través de su menú de Complementos (en Access 2010 es Herramientas de Base de Datos-> Agregar Ins).

Tengo algunos consejos que son específicos de SQL en VBA.

Pon tu código SQL con una variable de cadena. Solía hacer esto:

DoCmd.RunSQL "SELECT ..."

Eso es difícil de manejar Haz esto en su lugar:

strSQL = "SELECT ..." DoCmd.RunSQL strSQL

A menudo no puedes arreglar una consulta a menos que veas lo que se está ejecutando. Para hacerlo, descargue su SQL a la ventana Inmediato justo antes de la ejecución:

strSQL = "SELECT ..." Debug.Print strSQL Stop DoCmd.RunSQL strSQL

Pegue el resultado en el generador de consultas estándar de Access (debe usar la vista SQL ). Ahora puede probar la versión final, incluidas las variables controladas por código.

Cuando estás preparando una consulta larga como una cadena, divide tu código:

strSQL = "SELECT wazzle FROM bamsploot" _ & vbCrLf & "WHERE plumsnooker = 0"

Primero aprendí a usar vbCrLf cuando quería embellecer mensajes largos para el usuario. Más tarde descubrí que hace SQL más legible durante la codificación, y mejora la salida de Debug.Print . (Otro pequeño beneficio: no se necesita espacio al final de cada línea. La nueva sintaxis de la línea lo incorpora).

(NOTA: Puede pensar que esto le permitirá añadir comentarios a la derecha de las líneas de SQL. Prepárese para la decepción).

Como se dijo en otro lugar aquí, los viajes a un editor de texto son un ahorro de tiempo. Algunos editores de texto proporcionan un mejor resaltado de sintaxis que el editor oficial de VBA. (Diablos, lo hace mejor). También es eficiente para eliminar acceso cruft como referencias de tablas superfluas y montones de paréntesis en la cláusula WHERE.

Flujo de trabajo para la resolución de problemas graves:

VBA Debug.Print > (capture query during code operation) query builder > (testing lab to find issues) Notepad++ > (text editor for clean-up and review) query builder > (checking, troubleshooting) VBA

Por supuesto, la resolución de problemas suele ser una cuestión de reducir la complejidad de una consulta hasta que pueda aislar el problema (¡o al menos hacerlo desaparecer!). Luego puedes construirlo de vuelta a la obra maestra que deseas. Debido a que puede llevar varios ciclos resolver un problema persistente, es probable que use este flujo de trabajo repetidamente.