plan - La mejor forma de probar consultas SQL

optimizar inner join sql server

Question

Así que me he encontrado con un problema porque seguimos teniendo consultas SQL complejas que salen con errores.

esencialmente esto resulta en el envío de correo a los clientes incorrectos y otros ''problemas'' como ese.

¿Cuál es la experiencia de todos al crear consultas SQL de ese tipo, esencialmente estamos creando nuevas cohortes de datos cada dos semanas?

así que aquí están algunos de mis pensamientos y las limitaciones para ellos.

Crear datos de prueba: si bien esto demostraría que tenemos todos los datos correctos, no impone la exclusión de anomalías en la producción. Esos datos se considerarían erróneos hoy en día, pero pueden haber sido correctos hace 10 años, no estaban documentados y, por lo tanto, solo los conocemos una vez que se extraen los datos.

Cree diagramas de Venn y mapas de datos: esta parece ser una forma sólida de probar el diseño de una consulta, sin embargo, no garantiza que la implementación sea correcta. hace que los desarrolladores avancen y piensen en lo que está sucediendo mientras escriben.

Gracias por cualquier aporte que pueda dar a mi problema.

Answer 1

Cree una base de datos del sistema de prueba que puede volver a cargar tantas veces como desee. Cargue sus datos o cree sus datos y guárdelos. Produzca una manera fácil de volver a cargarlo. Adjunte su sistema de desarrollo a esa base de datos y valide su código antes de ir a producción. Dése un golpe cada vez que logre que un problema entre en producción. Cree un conjunto de pruebas para verificar problemas conocidos y haga crecer su conjunto de pruebas a lo largo del tiempo.

Answer 2

Es posible que desee comprobar DbUnit , por lo que puede intentar escribir pruebas unitarias para sus programas con un conjunto fijo de datos. De esta forma, debería poder escribir consultas con resultados más o menos predecibles.

La otra cosa que podría querer hacer es perfilar la pila de ejecución de su Servidor SQL y averiguar si todas las consultas son las correctas, por ejemplo, si está utilizando solo una consulta que devuelve resultados correctos e incorrectos, entonces claramente la consulta es usado está en cuestión, pero ¿qué pasa si su aplicación está enviando diferentes consultas en diferentes puntos del código?

Cualquier intento de corregir su consulta sería inútil ... las consultas fraudulentas aún podrían ser las que enciendan los resultados incorrectos de todos modos.

Answer 3

No escribirías una aplicación con funciones de 200 líneas de largo. Descompones esas funciones largas en funciones más pequeñas, cada una con una única responsabilidad claramente definida.

¿Por qué escribir tu SQL así?

Descompón tus consultas, al igual que descompones tus funciones. Esto los hace más cortos, más simples, más fáciles de comprender, más fáciles de probar , más fáciles de refactorizar. Y le permite agregar "calzas" entre ellos y "envoltorios" a su alrededor, tal como lo hace en el código de procedimiento.

¿Cómo haces esto? Al hacer cada cosa importante una consulta hace en una vista. A continuación, crea consultas más complejas a partir de estas vistas más simples, del mismo modo que crea funciones más complejas a partir de funciones más primitivas.

Y lo mejor es que, para la mayoría de las composiciones de vistas, obtendrá exactamente el mismo rendimiento de su RDBMS. (Para algunos no lo hará; ¿y qué? La optimización prematura es la raíz de todos los males. Primero codifique correctamente, luego optimice si es necesario).

Aquí hay un ejemplo del uso de varias vistas para descomponer una consulta complicada.

En el ejemplo, debido a que cada vista agrega solo una transformación, cada una se puede probar independientemente para encontrar errores, y las pruebas son simples.

Aquí está la tabla base en el ejemplo:

create table month_value( 
    eid int not null, m int, y int,  v int );

Esta tabla tiene fallas, porque usa dos columnas, mes y año, para representar un dato, un mes absoluto. Aquí está nuestra especificación para la nueva columna calculada:

Lo haremos como una transformación lineal, de modo que tenga el mismo orden que (y, m), y tal que para cualquier tupla (y, m) haya un único valor y todos los valores sean consecutivos:

create view cm_abs_month as 
select *, y * 12 + m as am from month_value;

Ahora lo que tenemos que probar es inherente a nuestra especificación, es decir, que para cualquier tupla (y, m), hay una y solo una (am), y que (am) son consecutivas. Vamos a escribir algunas pruebas.

Nuestra prueba será una consulta de select SQL, con la siguiente estructura: un nombre de prueba y una declaración de caso juntos. El nombre de la prueba es solo una cadena arbitraria. La declaración de caso es solo un case when las declaraciones de prueba then ''passed'' else ''false'' end .

Las declaraciones de prueba solo serán selecciones de SQL (subconsultas) que deben ser verdaderas para que pase la prueba.

Aquí está nuestra primera prueba:

--a select statement that catenates the test name and the case statement
select concat( 
-- the test name
''For every (y,m) there is one and only one (am): '', 
-- the case statement
   case when 
-- one or more subqueries
-- in this case, an expected value and an actual value 
-- that must be equal for the test to pass
  ( select count(distinct y, m) from month_value) 
  --expected value,
  = ( select count(distinct am) from cm_abs_month)  
  -- actual value
  -- the then and else branches of the case statement
  then ''passed'' else ''failed'' end
  -- close the concat function and terminate the query 
  ); 
  -- test result.

Ejecutar esa consulta produce este resultado: For every (y,m) there is one and only one (am): passed

Siempre que haya suficientes datos de prueba en month_value, esta prueba funciona.

También podemos agregar una prueba para suficientes datos de prueba:

select concat( ''Sufficient and sufficiently varied month_value test data: '',
   case when 
      ( select count(distinct y, m) from month_value) > 10
  and ( select count(distinct y) from month_value) > 3
  and ... more tests 
  then ''passed'' else ''failed'' end );

Ahora probemos que es consecutivo:

select concat( ''(am)s are consecutive: '',
case when ( select count(*) from cm_abs_month a join cm_abs_month b 
on (( a.m + 1 = b.m and a.y = b.y) or (a.m = 12 and b.m = 1 and a.y + 1 = b.y) )  
where a.am + 1 <> b.am ) = 0 
then ''passed'' else ''failed'' end );

Ahora pongamos nuestras pruebas, que son solo consultas, en un archivo y ejecutemos esa secuencia de comandos contra la base de datos. De hecho, si almacenamos nuestras definiciones de vista en un script (o scripts, recomiendo un archivo por vistas relacionadas) para ejecutarlo en la base de datos, podemos agregar nuestras pruebas para cada vista al mismo script, de modo que el acto de (re -) crear nuestra vista también ejecuta las pruebas de la vista. De esta forma, ambos obtenemos pruebas de regresión cuando volvemos a crear vistas y, cuando la creación de la vista se ejecuta en contra de la producción, la vista también se probará en producción.

Answer 4

Re: tpdi

case when ( select count(*) from cm_abs_month a join cm_abs_month b  
on (( a.m + 1 = b.m and a.y = b.y) or (a.m = 12 and b.m = 1 and a.y + 1 = b.y) )   
where a.am + 1 <> b.am ) = 0  

Tenga en cuenta que esto solo verifica que los valores de am para los meses consecutivos serán consecutivos, no que existan datos consecutivos (que probablemente sea lo que pretendía inicialmente). Esto siempre pasará si ninguno de sus datos de origen es consecutivo (por ejemplo, usted solo tiene meses pares), incluso si su cálculo de la mañana está totalmente desactivado.

También me estoy perdiendo algo, ¿o la segunda mitad de esa cláusula ON topa con el valor del mes equivocado? (es decir, los controles que 12/2011 viene después de 1/2010)

Lo que es peor, si mal no recuerdo, SQL Server al menos le permite menos de 10 niveles de vistas antes de que el optimizador lance sus manos virtuales al aire y comience a hacer escaneos completos de tablas en cada solicitud, así que no haga más de esto.

¡Recuerda probar el diablo con tus casos de prueba!

De lo contrario, la creación de un conjunto de datos muy amplio para abarcar la mayoría o todas las formas posibles de entradas, utilizando SqlUnit o DbUnit o cualquier otra * Unidad para automatizar la verificación de los resultados esperados con respecto a esos datos, y revisarlos, mantenerlos y actualizarlos según sea necesario generalmente parece ser el camino a seguir.