sql anti-patterns

¿Cuáles son los anti-patrones de SQL más comunes?



anti-patterns (30)

1) No sé que es un anti-patrón "oficial", pero no me gusta y trato de evitar los literales de cadena como valores mágicos en una columna de base de datos.

Un ejemplo de la ''imagen'' de la tabla de MediaWiki:

img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL, img_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart") NOT NULL default "unknown",

(Acabo de notar diferente carcasa, otra cosa para evitar)

Diseño casos tales como búsquedas de int en tablas ImageMediaType e ImageMajorMime con claves primarias int.

2) conversión de fecha / cadena que se basa en configuraciones NLS específicas

CONVERT(NVARCHAR, GETDATE())

sin identificador de formato

Todos los que trabajamos con bases de datos relacionales hemos aprendido (o estamos aprendiendo) que SQL es diferente. Obtener los resultados deseados, y hacerlo de manera eficiente, implica un proceso tedioso caracterizado en parte por aprender paradigmas desconocidos, y descubrir que algunos de nuestros patrones de programación más familiares no funcionan aquí. ¿Cuáles son los antipatrones comunes que has visto (o que has cometido)?

Abuso temporal de la mesa.

Específicamente este tipo de cosas:

SELECT personid, firstname, lastname, age INTO #tmpPeople FROM People WHERE lastname like ''s%'' DELETE FROM #tmpPeople WHERE firstname = ''John'' DELETE FROM #tmpPeople WHERE firstname = ''Jon'' DELETE FROM #tmpPeople WHERE age > 35 UPDATE People SET firstname = ''Fred'' WHERE personid IN (SELECT personid from #tmpPeople)

No cree una tabla temporal a partir de una consulta, solo para eliminar las filas que no necesita.

Y sí, he visto páginas de código de esta forma en los DB de producción.

Acabo de poner esto junto, basado en algunas de las respuestas SQL aquí en SO.

Es un antipatrón serio pensar que los desencadenantes son para las bases de datos como lo son los controladores de eventos para OOP. Existe la percepción de que cualquier lógica antigua se puede poner en disparadores, que se activará cuando ocurra una transacción (evento) en una mesa.

No es verdad. Una de las grandes diferencias es que los activadores son síncronos, con una venganza, porque son síncronos en una operación de conjunto, no en una operación de fila. En el lado de la POO, exactamente lo contrario: los eventos son una manera eficiente de implementar transacciones asíncronas.

Aprendiendo SQL en los primeros seis meses de su carrera y nunca aprendiendo nada más durante los próximos 10 años. En particular, no aprender o utilizar eficazmente las funciones de ventanas / analíticas de SQL. En particular el uso de over () y partición por.

Las funciones de la ventana, como las funciones agregadas, realizan una agregación en un conjunto definido (un grupo) de filas, pero en lugar de devolver un valor por grupo, las funciones de la ventana pueden devolver múltiples valores para cada grupo.

Consulte el Apéndice A del libro de recetas de O''Reilly SQL para obtener una descripción general de las funciones de ventanas.

Aquí están mis 3 mejores.

Número 1. No se ha especificado una lista de campos. (Editar: para evitar confusiones: esta es una regla de código de producción. No se aplica a los scripts de análisis únicos, a menos que yo sea el autor.)

SELECT * Insert Into blah SELECT *

debiera ser

SELECT fieldlist Insert Into blah (fieldlist) SELECT fieldlist

Número 2. Utilizando un cursor y while, cuando un bucle while con una variable de bucle funcionará.

DECLARE @LoopVar int SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable) WHILE @LoopVar is not null BEGIN -- Do Stuff with current value of @LoopVar ... --Ok, done, now get the next value SET @LoopVar = (SELECT MIN(TheKey) FROM TheTable WHERE @LoopVar < TheKey) END

Número 3. DateLogic a través de los tipos de cadena.

--Trim the time Convert(Convert(theDate, varchar(10), 121), datetime)

Debiera ser

--Trim the time DateAdd(dd, DateDiff(dd, 0, theDate), 0)

He visto un aumento reciente de "Una consulta es mejor que dos, ¿verdad?"

SELECT * FROM blah WHERE (blah.Name = @name OR @name is null) AND (blah.Purpose = @Purpose OR @Purpose is null)

Esta consulta requiere dos o tres planes de ejecución diferentes según los valores de los parámetros. Solo se genera un plan de ejecución y se guarda en la caché para este texto de SQL. Ese plan se utilizará independientemente del valor de los parámetros. Esto da lugar a un rendimiento deficiente intermitente. Es mucho mejor escribir dos consultas (una consulta por plan de ejecución previsto).

Desarrolladores que escriben consultas sin tener una buena idea de lo que hace que las aplicaciones SQL (tanto las consultas individuales como los sistemas multiusuario) sean rápidas o lentas. Esto incluye la ignorancia sobre:

  • Estrategias de minimización de E / S físicas, dado que la mayoría de las consultas ''cuello de botella'' es I / O no CPU
  • Impacto de diferentes tipos de acceso al almacenamiento físico (por ejemplo, muchas E / S secuenciales serán más rápidas que muchas I / O aleatorias pequeñas, ¡pero menos si su almacenamiento físico es un SSD!)
  • cómo ajustar manualmente una consulta si el DBMS produce un plan de consulta deficiente
  • cómo diagnosticar el bajo rendimiento de la base de datos, cómo "depurar" una consulta lenta y cómo leer un plan de consulta (o EXPLICAR, según el DBMS de su elección)
  • Estrategias de bloqueo para optimizar el rendimiento y evitar puntos muertos en aplicaciones multiusuario
  • La importancia de los procesos por lotes y otros trucos para manejar el procesamiento de conjuntos de datos.
  • diseño de tablas e índices para equilibrar mejor el espacio y el rendimiento (por ejemplo, cubrir índices, mantener índices pequeños siempre que sea posible, reducir los tipos de datos al tamaño mínimo necesario, etc.)

Estoy constantemente decepcionado por la tendencia de la mayoría de los programadores de mezclar su lógica de interfaz de usuario en la capa de acceso a datos:

SELECT FirstName + '' '' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User''s Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff(''d'', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + '' '' + AddrLine2 + '' '' + AddrLine3 + '' '' + City + '', '' + State + '' '' + Zip as "Address", ''XXX-XX-'' + Substring( Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users

Normalmente, los programadores hacen esto porque pretenden enlazar su conjunto de datos directamente a una cuadrícula, y es conveniente tener el lado del servidor del formato SQL Server en lugar del formato en el cliente.

Las consultas como la que se muestra arriba son extremadamente frágiles porque acoplan estrechamente la capa de datos a la capa UI. Además de eso, este estilo de programación evita que los procedimientos almacenados sean reutilizables.

Los dos que encuentro más y que pueden tener un costo significativo en términos de rendimiento son:

  • Usar cursores en lugar de una expresión basada en conjuntos. Supongo que esto ocurre con frecuencia cuando el programador piensa de manera procesal.

  • Usando subconsultas correlacionadas, cuando una unión a una tabla derivada puede hacer el trabajo.

Los que más me disgustan son

  1. Uso de espacios al crear tablas, símbolos, etc. Estoy bien con CamelCase o under_scores y singular o plurals y MAYÚSCULAS o minúsculas, pero tengo que referirme a una tabla o columna [con espacios], especialmente si [está espaciado de manera extraña] (sí, Me he encontrado con esto) realmente me irrita.

  2. Datos desnormalizados. Una tabla no tiene que estar perfectamente normalizada, pero cuando me encuentro con una tabla de empleados que tiene información sobre su puntaje de evaluación actual o sobre cualquier cosa primaria, me dice que probablemente deba hacer una tabla separada en algún momento y Luego trata de mantenerlos sincronizados. Primero normalizaré los datos y luego, si veo un lugar donde la desnormalización ayuda, lo consideraré.

  3. Uso excesivo de vistas o cursores. Las vistas tienen un propósito, pero cuando cada tabla está envuelta en una vista es demasiado. He tenido que usar los cursores unas cuantas veces, pero generalmente puedes usar otros mecanismos para esto.

  4. Acceso. ¿Puede un programa ser un anti-patrón? Tenemos SQL Server en mi trabajo, pero varias personas usan el acceso debido a su disponibilidad, "facilidad de uso" y "amabilidad" para los usuarios no técnicos. Hay demasiado aquí para entrar, pero si has estado en un entorno similar, lo sabes.

Mis errores son las tablas de acceso de 450 columnas que han sido creadas por el hijo de 8 años del peluquero de perros mejor amigo del director general y la tabla de búsqueda poco fiable que solo existe porque alguien no sabe cómo normalizar una estructura de datos correctamente.

Normalmente, esta tabla de búsqueda se ve así:

ID INT, Name NVARCHAR(132), IntValue1 INT, IntValue2 INT, CharValue1 NVARCHAR(255), CharValue2 NVARCHAR(255), Date1 DATETIME, Date2 DATETIME

He perdido la cuenta de la cantidad de clientes que he visto que tienen sistemas que dependen de abominaciones como esta.

Necesito poner mi propio favorito actual aquí, solo para completar la lista. Mi antipattern favorito no es probar tus consultas .

Esto se aplica cuando:

  1. Su consulta implica más de una tabla.
  2. Crees que tienes un diseño óptimo para una consulta, pero no te molestes en probar tus suposiciones.
  3. Aceptas la primera consulta que funciona, sin la menor idea de si está incluso cerca de optimizarse.

Y cualquier prueba ejecutada contra datos atípicos o insuficientes no cuenta. Si se trata de un procedimiento almacenado, ponga la declaración de prueba en un comentario y guárdelo con los resultados. De lo contrario, póngalo en un comentario en el código con los resultados.

No tiene que profundizar en ello: no usar declaraciones preparadas.

Para almacenar valores de tiempo, solo se debe usar la zona horaria UTC. La hora local no debe ser utilizada.

Poner cosas en tablas temporales, especialmente las personas que cambian de SQL Server a Oracle tienen el hábito de usar en exceso las tablas temporales. Solo usa declaraciones selectas anidadas.

Procedimientos almacenados o funciones sin comentarios ...

Reutilizar un campo ''muerto'' para algo para lo que no estaba destinado (p. Ej., Almacenar datos de usuario en un campo ''Fax''), aunque es muy tentador como solución rápida.

Subconsultas idénticas en una consulta.

Usando alias de tablas sin sentido:

from employee t1, department t2, job t3, ...

Hace que la lectura de una declaración SQL grande sea mucho más difícil de lo que debe ser

Uso excesivo de tablas temporales y cursores.

Visión contraria: obsesión excesiva con la normalización.

La mayoría de los sistemas de SQL / RBDB ofrecen una gran cantidad de características (transacciones, replicación) que son bastante útiles, incluso con datos no normalizados. El espacio en disco es barato y, a veces, puede ser más simple (código más fácil, tiempo de desarrollo más rápido) manipular / filtrar / buscar datos, que escribir un esquema de 1NF, y lidiar con todas las molestias en él (combinaciones complejas, subselecciones desagradables). , etc).

He descubierto que los sistemas sobre-normalizados son a menudo una optimización prematura, especialmente durante las primeras etapas de desarrollo.

(más pensamientos sobre él ... http://writeonly.wordpress.com/2008/12/05/simple-object-db-using-json-and-python-sqlite/ )

use SP como el prefijo del nombre del procedimiento de la tienda porque primero buscará en la ubicación de los procedimientos del sistema en lugar de los personalizados.

utilizando @@ IDENTITY en lugar de SCOPE_IDENTITY ()

Citado de esta respuesta :

  • @@IDENTITY devuelve el último valor de identidad generado para cualquier tabla en la sesión actual, en todos los ámbitos. Tienes que tener cuidado aquí, ya que es a través de ámbitos. Podría obtener un valor de un activador, en lugar de su declaración actual.
  • SCOPE_IDENTITY devuelve el último valor de identidad generado para cualquier tabla en la sesión actual y el alcance actual. Generalmente lo que quieres usar.
  • IDENT_CURRENT devuelve el último valor de identidad generado para una tabla específica en cualquier sesión y cualquier ámbito. Esto le permite especificar de qué tabla desea el valor, en caso de que las dos anteriores no sean exactamente lo que necesita (muy raro). Puede usar esto si desea obtener el valor de IDENTIDAD actual para una tabla en la que no ha insertado un registro.

Uso de claves primarias como sustituto para las direcciones de registro y uso de claves externas como sustituto de los punteros incrustados en los registros.

Uso de SQL como un paquete glorificado ISAM (Método de acceso secuencial indexado). En particular, anide los cursores en lugar de combinar las sentencias de SQL en una sola sentencia, aunque más grande. Esto también cuenta como ''abuso del optimizador'' ya que, de hecho, no hay mucho que pueda hacer el optimizador. Esto se puede combinar con declaraciones no preparadas para la máxima ineficiencia:

DECLARE c1 CURSOR FOR SELECT Col1, Col2, Col3 FROM Table1 FOREACH c1 INTO a.col1, a.col2, a.col3 DECLARE c2 CURSOR FOR SELECT Item1, Item2, Item3 FROM Table2 WHERE Table2.Item1 = a.col2 FOREACH c2 INTO b.item1, b.item2, b.item3 ...process data from records a and b... END FOREACH END FOREACH

La solución correcta (casi siempre) es combinar las dos declaraciones SELECT en una:

DECLARE c1 CURSOR FOR SELECT Col1, Col2, Col3, Item1, Item2, Item3 FROM Table1, Table2 WHERE Table2.Item1 = Table1.Col2 -- ORDER BY Table1.Col1, Table2.Item1 FOREACH c1 INTO a.col1, a.col2, a.col3, b.item1, b.item2, b.item3 ...process data from records a and b... END FOREACH

La única ventaja de la versión de doble bucle es que puede detectar fácilmente las rupturas entre los valores en la Tabla 1 porque el bucle interno termina. Esto puede ser un factor en los informes de interrupción de control.

Además, la clasificación en la aplicación suele ser un no-no.

SELECT FirstName + '' '' + LastName as "Full Name", case UserRole when 2 then "Admin" when 1 then "Moderator" else "User" end as "User''s Role", case SignedIn when 0 then "Logged in" else "Logged out" end as "User signed in?", Convert(varchar(100), LastSignOn, 101) as "Last Sign On", DateDiff(''d'', LastSignOn, getDate()) as "Days since last sign on", AddrLine1 + '' '' + AddrLine2 + '' '' + AddrLine3 + '' '' + City + '', '' + State + '' '' + Zip as "Address", ''XXX-XX-'' + Substring(Convert(varchar(9), SSN), 6, 4) as "Social Security #" FROM Users

O, abarrotar todo en una sola línea.

select some_column, ... from some_table group by some_column

y asumiendo que el resultado será ordenado por some_column. He visto esto un poco con Sybase, donde se sostiene la suposición (por ahora).

  • La sintaxis de FROM TableA, TableB WHERE para JOINS en lugar de FROM TableA INNER JOIN TableB ON

  • Hacer suposiciones de que una consulta se devolverá clasificada de cierta manera sin colocar una cláusula ORDER BY, solo porque así se mostró durante la prueba en la herramienta de consulta.

  • La vista alterada: una vista que se modifica con demasiada frecuencia y sin aviso ni razón. El cambio se notará en el momento más inadecuado o, peor aún, estará equivocado y nunca se notará. Tal vez su aplicación se rompa porque alguien pensó en un nombre mejor para esa columna. Como regla general, las vistas deberían ampliar la utilidad de las tablas base mientras se mantiene un contrato con los consumidores. Solucione problemas pero no agregue funciones o peor comportamiento de cambio, para eso cree una nueva vista. Para mitigar, no comparta las vistas con otros proyectos y use CTEs cuando las plataformas lo permitan. Si su tienda tiene un DBA, probablemente no pueda cambiar las vistas, pero todas sus vistas estarán desactualizadas o serán inútiles en ese caso.

  • El! Paramed - ¿Puede una consulta tener más de un propósito? Probablemente, pero la siguiente persona que lo lea no lo sabrá hasta la meditación profunda. Incluso si no los necesita en este momento, es probable que lo haga, incluso si es "solo" depurar. Agregar parámetros reduce el tiempo de mantenimiento y mantiene las cosas en SECO. Si tiene una cláusula where debería tener parámetros.

  • El caso para ningún caso -

    SELECT CASE @problem WHEN ''Need to replace column A with this medium to large collection of strings hanging out in my code.'' THEN ''Create a table for lookup and add to your from clause.'' WHEN ''Scrubbing values in the result set based on some business rules.'' THEN ''Fix the data in the database'' WHEN ''Formating dates or numbers.'' THEN ''Apply formating in the presentation layer.'' WHEN ''Createing a cross tab'' THEN ''Good, but in reporting you should probably be using cross tab, matrix or pivot templates'' ELSE ''You probably found another case for no CASE but now I have to edit my code instead of enriching the data...'' END

  • Campos de contraseña legibles por humanos , egad. Autoexplicativo.

  • Usando LIKE contra columnas indexadas , estoy casi tentado de decir LIKE en general.

  • Reciclaje de valores PK generados por SQL.

  • Sorpresa nadie mencionó aún la mesa divina . Nada dice "orgánico" como 100 columnas de banderas de bits, cadenas grandes y enteros.

  • Luego está el patrón "Echo de menos los archivos .ini" : almacenar CSV, cadenas delimitadas por tuberías u otros datos necesarios de análisis en campos de texto grandes.

  • Y para el servidor MS SQL el uso de cursores en absoluto . Hay una mejor manera de hacer cualquier tarea del cursor dado.

Editado porque hay tantos!