¿Por qué ejecutar una consulta en SQL Azure es mucho más lento?

SmarterAsp una count de testing en Azure e implementé mi database desde SmarterAsp .

Cuando ejecuto una consulta dinámica en SmarterAsp\MyDatabase , los resultados aparecen en 2 segundos .

Sin embargo, ejecutar la misma consulta en Azure\MyDatabase tomó 94 segundos .

Uso el SQL Server 2014 Management Studio (testing) para conectarme a los serveres y ejecutar la consulta.

¿Es esta diferencia de velocidad porque mi count es una count de testing?

Alguna información relacionada a mi pregunta

la consulta es:

 ALTER procedure [dbo].[Pivot_Per_Day] @iyear int, @imonth int, @iddepartment int as declare @columnName Nvarchar(max) = '' declare @sql Nvarchar(max) ='' select @columnName += quotename(iDay) + ',' from ( Select day(idate) as iDay from kpivalues where year(idate)=@iyear and month(idate)=@imonth group by idate )x set @columnName=left(@columnName,len(@columnName)-1) set @sql =' Select * from ( select kpiname, target, ivalues, convert(decimal(18,2),day(idate)) as iDay from kpi inner join kpivalues on kpivalues.idkpi=kpi.idkpi inner join kpitarget on kpitarget.idkpi=kpi.idkpi inner join departmentbscs on departmentbscs.idkpi=kpi.idkpi where iddepartment='+convert(nvarchar(max),@iddepartment)+' group by kpiname,target, ivalues,idate)x pivot ( avg(ivalues) for iDay in (' + @columnName + ') ) p' execute sp_executesql @sql 

Ejecutar esta consulta en 3 serveres diferentes me dio resultados diferentes en términos de time transcurrido hasta que mi tabla dinámica aparece en la pantalla:

Azure: time transcurrido = 100.165 segundos

Smarterasp.net – Tiempo transcurrido = 2.449 sec

LocalServer – Tiempo transcurrido = 1.716 segundos

En cuanto a mi count de testing en Azure, lo hice con el objective principal de verificar si tendré una mejor velocidad que Smarter al ejecutar un procedimiento almacenado como el anterior. Elijo para mi database Nivel de service: básico, nivel de performance -Básico (5DTU) y máx. Tamaño 2GB.

Mi database tiene 16 tablas, 1 tabla tiene 145284 filas y el tamaño de la database es 11mb. Es una database de testing para mi aplicación.

Mis preguntas son:

  1. ¿Qué puedo hacer para optimizar esta consulta (sp)?
  2. ¿Se recomienda Azure para bases de datos pequeñas (100mb-1Gb)? ¡Me refiero a performance versus costo!

Conclusiones basadas en tus inputs:

  • Realicé cambios sugeridos en la consulta y el performance mejoró con más del 50%. Gracias, Remus.
  • Probé mi consulta en Azure S2 y el time transcurrido para la consulta actualizada fue de 11 segundos.
  • Probé nuevamente mi consulta en P1 y el time transcurrido fue de 0.5 segundos 🙂

  • la misma consulta actualizada en SmarterASP tenía un time transcurrido de 0.8 segundos.

Ahora está claro para mí cuáles son los niveles en Azure y cuán importante es tener una muy buena consulta (incluso entendí qué es un índice y su ventaja / desventaja)

Gracias a todos, Lucian

Esto es ante todo una cuestión de performance. Está tratando con un código de bajo performance de su parte y debe identificar el cuello de botella y abordarlo. Estoy hablando del mal performance de 2 segundos ahora. Siga las instrucciones en Cómo analizar el performance de SQL Server . Una vez que obtenga esta consulta para que se ejecute localmente aceptable para una aplicación web (less de 5 ms), entonces puede hacer la pregunta de portarla a Azure SQL DB. En este momento, su count de testing solo está resaltando las ineficiencias existentes.

Después de la actualización

 ... @iddepartment int ... iddepartment='+convert(nvarchar(max),@iddepartment)+' ... 

¿así que qué es lo? ¿Es la columna iddepartment un int o un nvarchar ? ¿Y por qué usar (max) ?

Esto es lo que deberías hacer:

  • parametrizar @iddepartment en el SQL dynamic interno
  • deje de hacer la conversión nvarchar(max) . Haga que los types iddepartment y @iddertment coincidan
  • garantizar índices en iddepartment y todos los idkpi s

Aquí es cómo parametrizar el SQL interno:

 set @sql =N' Select * from ( select kpiname, target, ivalues, convert(decimal(18,2),day(idate)) as iDay from kpi inner join kpivalues on kpivalues.idkpi=kpi.idkpi inner join kpitarget on kpitarget.idkpi=kpi.idkpi inner join departmentbscs on departmentbscs.idkpi=kpi.idkpi where iddepartment=@iddepartment group by kpiname,target, ivalues,idate)x pivot ( avg(ivalues) for iDay in (' +@columnName + N') ) p' execute sp_executesql @sql, N'@iddepartment INT', @iddepartment; 

Los índices de cobertura son, por lejos, la solución más importante. Eso, obviamente, requiere más información de la que está aquí presente. Lea Diseñando Índices incluyendo todos los subcapítulos.

Como un comentario más general: este tipo de consultas corresponden a columnas almacenes más que rowstore, aunque creo que el tamaño de los datos es, básicamente, muy pequeño. La database SQL de Azure admite índices de almacén de columnas clúster actualizables, puede experimentar con ella en anticipación de un tamaño de datos serio. Requieren Enterprise / Development en el cuadro local, cierto.

( Actualización : la pregunta original se ha cambiado para preguntar también cómo optimizar la consulta, lo cual también es una buena pregunta. La pregunta original era por qué la diferencia de la que trata esta respuesta).

El performance de las consultas individuales se ve muy afectado por los niveles de performance. Sé que la documentation implica que los niveles son sobre carga, eso no es estrictamente cierto.

Volvería a ejecutar su testing con una database S2 como punto de partida e iré desde allí.

Estar en una suscripción de testing no afecta el performance en sí mismo, pero con la count gratuita probablemente estés usando un nivel B que no es realmente útil para nada real, ciertamente no para una consulta que tarda 2 segundos en ejecutarse localmente.

Incluso moverse entre, digamos, S1 y S2 mostrará una diferencia notable en el performance de una consulta individual. Si desea experimentar, recuerde que se le cobra un día por "cualquier parte del día", que probablemente sea correcto para el nivel S, pero tenga cuidado al probar el nivel P.

Para el background; cuando Azure presentó los nuevos niveles el año pasado, cambiaron el model de hosting para SQL. Solía ​​ser que muchas bases de datos se ejecutarían en un sqlserver.exe compartido. En el nuevo model, cada database obtiene efectivamente su propio sqlserver.exe que se ejecuta en un entorno limitado de resources limitados. Así es como controlan el "uso de DTU" pero también afecta el performance general.

No tiene nada que ver con el hecho de que su count sea de testing, se debe al menor nivel de performance que haya seleccionado.

En otro service (SmarterAsp) y ejecutando una instancia local, probablemente no tenga restricciones de performance en lugar de restricciones de tamaño.

En este punto, es imposible armar lo que realmente significa DTU / qué tipo de número de DTU está asociado con un server Sql instalado en su máquina local o en cualquier otro proveedor de alojamiento.

Sin embargo, hay algunos buenos análisis ( https://cbailiss.wordpress.com/2014/09/16/performance-in-new-azure-sql-database-performance-tiers/ ) realizados con respecto a esto, pero nada oficial.