Stored Proc vs Adhoc Queries against SQL Server 2012

[Carlos A. Osoria]

Hola Grupo 

Tuve un argumento con el departamento de Administracion de Bases de Datos sobre el viejo dema de Stored Procs contra Queries hechas desde C# con ADO.NET 

Por aca tenemos una structura impuesta por el departamento de bases de datos (DBA) que es mas o menos asi:

Metodologia:

- Si se va a hacer "select, update, insert o delete a una o mas tablas" entonces se debe crear un stored proc llamado "base stored proc" 

- Si se va a hacer una operacion que resulta en logica mas compleja "IF, CASE ..." entonces de debe crear un stored proc llamado "control stored proc" que contiene la logica "IF" y llama a los "base procs" para hacer operaciones CRUD. 

Existen algunos casos (al menos que he visto) donde se tienen como 5 stored procs en cadenas y los stored procs casi ni tienen codigo solo llamadas a otros stored procs. 

Los DBA dicen que esta estructura es necesaria para la "optimizacion" y los "execution plan" siempre sean los mismo y otro monton de razones de optimizaciones.

Yo tenia entendido que la maquinaria de optimizacion SQL Server 2012 es tan buena que casi no existe mucha diferencia entre Queries Adhoc y Stored Procs. 

Me pregunto si es necesaria toda esta complicacion, no creo que se pueda cambiar esto por aca pero me gustaria saber si alguien a visto algo semejante o tiene experiencia con SQL Server para explicar porque esta estructura es mas ventajosa.

Saludos

Carlos

--

Eng. Carlos A. Osoria.
carlos...@gmail.com

[Erlis Vidal]

hmmm, nunca habia escuchado eso que comentas Carlis.

Lo que si se es que con stored procedures puedes fine tune mejor la seguridad. Por ejemplo puedes crear diferentes schemas y particionar un poco la app usando schemas y puedes manejar un poco mejor los permisos con eso. Tambien es cierto que al tener stored procedures vs ad hoc queries puedes manejar un poco mejor los execution plans de cada stored proc.

Lo que si no sabia es que de un proc llamar a otro optimiza el execution plan, eso no lo habia escuchado, no digo que no sea, pero tengo un socio que si le sabe al tema, esta en la lista pero nunca escribe, deja ver si se apiada de nosotros:

Kakkkiiiiiiiiiiiiiiiiiiiiiiiii??!!!!!!!!!

un abrazo carlitos!!! Vamos a ver si el maestro responde algo.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.
Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.
Para acceder a más opciones, visita https://groups.google.com/d/optout.

[Elier Delgado]

Algunas pistas en este artículo:

https://martinfowler.com/articles/dblogic.html

Saludos

[Erlis Vidal]

Carlis,

Me acabo de acordar de un problema que tuve una vez en un stored proc y fue por causa de un IF. Y el problema se soluciono haciendo precisamente lo que describiste en el email inicial.

Este es el problema.

Supon que tienes un stored procedure sp1 que dentro tiene un IF condition. El problema del IF es que va a crear un branch en código que genera dos posibles caminos de ejecución. Cuando el SQL engine genera el execution plan para sp1, va a usar el mejor plan posible pero el problema es el branch que genera el IF, por lo tanto el plan de ejecución de sp1 va a ser el mejor para uno de esos branches y lo que ocurre aqui es que vas a tener que sp1 corre rapido a veces pero otras semora muchismo (va a depender de la condicion del IF y si en runtime se ejecuto el camino para el cual se hizo el execution plan o no)

Si extraes el código de cada branch del IF en dos stores procedures, entonces tendremos tres execution planes, el de sp1, y el de los dos branches, ahora no importa por cual branch se vaya la ejecución, cada branch tendrá el óptimo y así la ejecución de sp1 sera consistente.

Esto me mordió una vez, asi que ahi tienes, lo que describes no esta tan loco, aunque lo que en lo personal no me gusta es que se hagan reglas que pueden introducir patrones no tan deseables, es necesario hacer esto siempre??? No creo, yo creo que habria que pensar en cosas asi cuando la lógica dentro del IF/ELSE es bien complicada pero siempre que hay un if extraer? Me parece demasiado.

Un abrazo Carlis, si me acuerdo de algo mas te dejo saber.

Salu2

Erlis

[Israel García]

Interesante el impacto del IF en el execution plan que habla Erlis. No se me habia ocurrido. 

En lo personal, no haria stored procedures para operaciones Ad-Hoc, ni queries simples, de hecho ahora estamos simplificando mucho el proyecto en el que estabamos en el que TODO se hacia en stored procedures y complicado de mantener, en muchas ocasiones se repetian stored procedures que hacian las mismas cosas con diferente nombre, o solo para deveolver un campo mas. 

Le doy mucho valor a los micro-ORMs y confio en que SQL Server sera en cada version mas inteligente. Voy a intentar hacer pruebas en cuanto a correr un stored procedure con un query (2 joins) y mandar el text query directo a ver si es mucha diferencia.

Saludos.

Israel.

[Letser Herrera]

Hola Bolain,

Sorry por la demora en escribir, entre los ultimos dias en el trabajo y preparando el viaje a Nicaragua he estado con poco tiempo libre.

Veo dos temas distintos en la pregunta:

1- Los stored proedures anidados vs IF statements.

2- Rendimiento de Ad-hoc queries vs stored procedures.

Sobre el tema 1:

El problema al que se refieren es mas general que solo el caso de code branching, de echo puede ocurrir con el mas simple de los queries:

select * from dbo.Test where CategoryID = @p1;

El problema es conocido en el mundo de las bases de datos como "parameter sniffing". Esto puede ocurrir tanto en SPs como en Ad-hoc queries que sean parametrizado, y si los Ad-hoc que usan no son parametrizados tienen otros muchos peores problemas como "cache bloat" y "CPU splikes" (pero estos son otros temas)

Basicamente lo que ocurre cuando hay queries parametrizados es que el DB engine intenta reusar el mismo plan de ejecucion para todos los queries y puede darse el caso de que un buen plan de ejecucion para un valor del parametro sea muy malo para otro valor, esto debido a la distribucion de los datos.

Ha varias maneras de lidiar con este problema si se presenta, los mas comunes son:

1. with recompile (a nivel de SP)
   
2. option recompile (a nivel de query)
   
3. option optimize for (a nivel de query)
   
4. query hints 
   
5. plan guides
   

Ninguna de las soluciones es perfecta (puede hacer mucho mayor uso del CPU), pero te pueden tirar un buen cabo cuando hay problemas en el sistema. Si el query o SP que tiene problemas se ejecuta pocas veces el option recompile o with recompile son la mejor opcion. Si, por el contrario se ejecuta muchas veces el option optimize for para algun valor en especifico o usando la clausula unknown pude darte un plan de ejecucion que sea lo suficientemente bueno para todos los casos. Las dos ultimas opciones son solo recomendadas para resolver un problema puntual en produccion (stop the bleeding) pero a largo plazo siempre dan problemas y no son recomendadas.

Es un tema super interesante y hay que ir caso por caso, en general la recomendacion es no tocar nada de eso excepto en el caso que tengas problemas, una excepcion a esto son SPs o queries que se ejecuten en un ambiente de reporting/datawarehouse que el costo de parsing y recompilacion es despreciable comparado al costo de ejecucion y es relativamente normal usar las opciones 1 o 2 mencionadas. 

Sobre el tema 2:

Este es un temas mucho mas "caliente", desde el punto de vista del rendimiento la diferencia es minima a favor de los SPs (asumiendo que los queries son parametrizados) y es causada por el parsing de los queries y el network traffic .

Ahora bien existen otros muchos beneficios de usar SPs sobre Ad-hoc queries:

• Operational flexibility: Hay muchas razones por las que puede ser necesario tocar un SP, un ejemplo: Imaginen que de pronto la aplicacion en produccion se pone super lenta, puede ser un plan de ejecucion malo que esta cached. Si, el codigo esta en el SP uno puede simplemente ejecutar un sp_recompile a ese SP y la proxima vez que se ejecute ya se creara un nuevo plan de ejecucion o se puede usar alguna de las tecnicas mencionadas arriba with recompile/option recompile. Esto lo puede hacer el DBA en unos segundos, si por el contrario el codigo esta en la aplicacion:
• Es mucho mas dificil identificarlo, 
• Se puede borrar el plan cache pero es un poco mas complicado
• Si hay necesidad de agregar option recompile al query, depende de donde este almacenado el ad-hoc query (en la aplicacion, en un resource file, en la propia BD, alguna otro mecanismo), en general es mas complicado.
• Monitoring: A nivel de SPs es sumamente facil y hay muchas herramientas para monitorear y almacenar estadisticas de rendimiento de los SPs y no es solamente duration, sino tambien CPU usage, IO stats and memory usage. Esta informacion es invaluable para optimizar la aplicacion a nivel de queries no es imposible pero es muchisimo mas complicado.
• Security: El bolao ya menciono esta anteriormente.
  
• Code maintenance (esta trae bastante debate): Los SPs son codigo y cuando se tienen en algun IDE de base de datos (junto con el esquema) puedes hacer varias cosas similares a las que estamos acostumbrados en C# o Java, por ejemplo buscar dependencias o build el proyecto para ver si no hay errores o warnings. Imaginen que estan haciendo un refactor qu incluye borrar una columna, es mucho mas facil y conveniente compilar un database project y ver los SPs que fallan a tener que buscar en el codigo o resource files donde se usa esa columna. 

En mi opinion se debe encontrar un balance donde las operaciones mas sencillas se hagan sin SPs y las mas complejas o que uno sospeche que puedan causar problemas en SPs. El reto es encontrar un mecanismo para que "sencillo" signifique mas o menos lo mismo para todos los developeres y ademas que los DBAs tambien esten contentos con el plan.

Saludos,

[Erlis Vidal]

Carlis,

Ahi esta la respuesta del Kaki :)

Gracias Kaki por tan buena respuesta!!

[Yoel de la Noval]

Martes a primera hora, tarea para el Kaki: escribir sobre un tema cosas así :)

Thanks, espero nos podamos ver la próxima vez que este por allá

Un abrazon

Yoel.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-c...@googlegroups.com.

[Carlos A. Osoria]

Hola a todos 

Muchas gracias por las respuestas, ahora comprendo mucho mejor. Por aca no te dan respuesta tan claras y completas. Especialmente por la division que existe entre los teams. 

Nunca imagine que un simple IF podria potencialmente ser la causa de pobre redimiento. 

Gracias al Mostril, al Kaki y Elier por la explicacion tan completa.

Saludos y abrazos

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

---
Has recibido este mensaje porque estás suscrito al grupo "Improve your code" de Grupos de Google.

Para cancelar la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a improve-your-code+unsubscribe@googlegroups.com.

Para acceder a más opciones, visita https://groups.google.com/d/optout.

--