sábado, noviembre 19, 2005

Procedimientos Almacenados contra consultas

Actualizado el día 03/03/2009.

Hoy toca hablar de las ventajas de usar procedimientos almacenados en Microsoft SQL 7/2000/2005/2008 contra las desventajas de hacer las mismas consultas desde el Objeto Connection o Command de Visual Basic 6.0 (aplicable a otros lenguajes de programación como .NET).

Cada vez que lanzamos una consulta contra SQLServer, éste tiene que calcular las estadísticas de ejecución para comprobar cual va a ser el mejor método y más rápido de la consulta a no ser que tenga ya unas almacenadas, y así, conseguir que los resultados aparezcan en el menor tiempo posible, por eso es vital que la consulta se igual en su semántica para no tener que recalcularlas cada vez que lanzamos la consulta. Por eso pienso y es mi opinión particular y entendida despues de algunas nociones y consejos de colegas de profesión y algún que otro SUMMIT que podríamos ahorrar un poco de tiempo al lanzar nuestras consultas si éstas las encapsulamos en procedimientos almacenados.

Como iba diciendo, SQL guarda estas estadísticas en la caché, de disco o memoria, además de los datos consultados por si se vuelve a repretir la operación. (De aquí he llegado a la conclusión de que el consumo desmesurado de la memoria caché hace que la memoria de nuestro servidor SQL esté siempre a tope y por eso me gusta contralarla en las propiedades del servidor SQL.)

Ahora bien, he recomendado usar procedimientos almacenados, ¿pasa si trabajamos con un procedimiento almacenado(SP)? Cuando creamos un SP, este genera unas estadísticas de consulta que guarda para el SP creado. En el caso que desde nuestro programa haga una consulta contra ese procedimiento ya tiene las estadísticas creadas, por lo que se ahorra un tiempo precioso para nuestro servidor, además de recursos si en vez de eso le mandamos consultas dinámicas del tipo select, creadas con nuestro programa VB6 ó NET y que tiene 20 parámetros en el where cada uno distinto al otro y con mil maneras de ordenar los resultados.

Mención aparte de que hay que tener cuidado aquí con el nombre del SP y con la llamada a ese SP, para SQL Server AAA y aaa no es lo mismo aunque luego llame al SP AAA. Es decir que si tenemos AAA como SP y llamamos a aaa, el SP se ejecutará bien, pero habrá vuelto a crear las estadísticas para este SP o no?, pues puede que sí, porque como las busque en memoria lo más probable es que no las encuentre.

Pensar que una aplicación como la que hemos desarrollado en la empresa en la que trabajo, puede hacer unas 5-10 consultas contra la bd por minuto y por puesto. La aplicación corre en despachos que van desde un puesto hasta 60, imaginar cuanto tiempo estamos ahorrando siguiendo este sistema de procedimientos almacenados y lo estructurados que nos quedan los programas llamando todos a los mismos P.A.

Por último, debemos tener en cuenta que la base de datos puede crecer y que quizás en algún momento de la vida de aplicación deberíamos revisar las estadísticas y los índices de la base de datos, para comprobar que estas trabajan realmente bien, no viene mal chequear el rendimiento de aquellos procedimientos almacenados que se han cambiado a lo largo del tiempo. ¿Imaginas que un índice para una tabla no te sirviera si la tabla crece a más de 1 millón de registros y que todas las consultas a esa table se caen en rendimiento?.

El punto anterior lo dice todo, si tuvieramos intrucciones SQL en nuestro código, tendríamos que revisar todos nuestros programitas y volvernos locos porque seguro que habría consultas de aquel que se fué hace 2 años y que no ponía comentarios en su código. Si tenemos P.A. tan sólo hay que buscar los objetos dependientes de esa tabla maldita y arreglarlos desde el mismo management studio, mucho menos tiempo que recorrer ingentes cantidades de código de programas olvidados en el recuerdo de algunos que ya no están.

Unas nomenclaturas fáciles para los P.A podrían ser las siguientes para una tabla de CLIENTES:

Clientes_Leer_Uno
Clientes_Leer_Lista
Clientes_Insertar
Clientes_Actualizar
Clientes_Borrar
Clientes_Listar_Por_Apellidos
Clientes_Listar_Pendientes

etc....

No es parece más fácil?, además tanto reporting como crystal soportan procedimientos almacenados, así que en un mantenimiento no previsto dónde un resultado de listado fuera complejo, se podría estructurar de una manera fácil dónde nuestro gestor de informes no sufriera un cambio radical o ninguno por el cambio de nombre de la fuente de datos ¿no os ha pasado que una vez que utilizas una vista compleja en un par de listados, resulta que un día uno de los listados ya no nos sirve y nos toca hacer una vista nueva que crystal reports omite porque no se llama igual que la anterior y toca rehacer el listado?.

Pues eso: Report_Pendientes_de_Cobro lee del SP Contabilidad_Pendienes_De_Cobro siempre, usemos una vista u otra.

12 comentarios:

Lucy dijo...

Hola me parece muy interesante tu articulo ya que es precisamente lo que estoy buscando, necesito saber ¿Cuál es la ventaja o desventaja de tener mas de 300 Procedimientos en un sistema? ¿Qué podria pasar con la base de datos? ¿Es recomendable o es mejor mandar las sentencias desde el código?

Muchas Gracias por tu respuesta de antemano.

Ma. Lucia Araujo.

OscarMontesinos dijo...

Hola Lucia, ventaja muchas, piensa que tendrías aislado en el lado del servidor toda la lógica de acceso a la base de datos, lo que te permitiría un mayor control sobre ese código, una fácil localización y un fácil mentenimiento. Tengo que aclarar una cosa, realmente no se compila el procedimiento, si no que genera unos planes de ejecución que se graban con procedimiento y que se usan siempre y cuando se necesiten. A esto lo llamamos compilación porque se puede provocar con la claúsula WITH RECOMPILE, que te puede servir cuando modificas la lógica o las tablas de una consulta dentro de un proc. Pero como he dicho en el artículo, podrás cambiar eso con DROP Y CREATE en vez de con ALTER.
Por otro lado 300 procedimientos son pocos, no es mucho, si sigues una buenas reglas de nomenclatura no tendrás problemas de mantimiento. Piensa que en la caché se almacenan los procs y planes más usados y que eso totalmente trasparente a la ejecución de las consultas o los Sp.
Puedes probar el hacer un SELECT INSERT Y UPDATE de una tabla pesada con procedimientos y hacer lo mismo desde código desde 2 puestos distintos. Cálcula el tiempo de respuesta del servidor para ambos y luego consulta las vistas del sistema para ver la información sobre los planes cacheados, etc, encontrarás más información en los libros en pantalla. También te aconsejo que busques información de Eladio Rincón que es uno de los mejores en cuanto a optimización y rendimiento en SQL.

Lucy dijo...

Muchas gracias por la respuesta es de mucha utilidad, viendo en tu blog dice que trabajas con Visual .Net, y tengo una pregunta para ti acerca de este tema, quiero saber como se puede deshabilitar una ventana completa, no se si me entiendas pero te voy a explicar a continuacion que es lo que quiero hacer, necesito que cuando le den click a un botón por ejemplo Guardar y mientras se ejecuta el procedimiento quiero que se deshabilite la ventana completa incluyendo el botón para que el usuario no este oprimiendo otros botónes mientras se ejecuta este, y se vuelva activar cuando termine de ejecutar el procedimiento, si sabes como te lo agradeceria mucho el que me pudieras ayudar.

Muchas gracias de antemano, lucy

OscarMontesinos dijo...

Hola Lucy, en un formulario de 2.0 puedes hacer Me.Enabled=False.

Anónimo dijo...

Hola Oscar, y dime que nos puedes decir hacerca de las vistas como herramienta para consultas de seleccion frente a store procedures y consultas

Gino

OscarMontesinos dijo...

Hola Gino, una vista no es más que una consulta a una tabla. Me explico: cuando creamos una vista, estamos creando una tabla o una estructura de una tabla virtual, que no existe en nuestra base de datos pero que realmente sí existe en la base de datos temp en SQL Server. Por lo que volvemos a lo mismo, estamos haciendo una consulta y como comento en este post vuelvo a decir que es más cómodo y seguro un procedimiento almacenado. Hace un par de meses estuvimos probando el nuevo crystal Reports 2008 en la oficina contra procedimientos almacenados que llaman a otros procedimientos almecanados. El resultado fué muy satisfactorio en cuanto a rendimiento en los que nos afecta a nosotros, y vuelvo a decir, para mí es mucho más cómodo mantener el código que usa contra una bd desde los propios procedimientos.

Eduardo dijo...

Hola, genial tu artículo Oscar, pero como acabas de mencionar SQL con Crystal Report y en estos momentos me estoy quebrando la cabeza con eso, me surgió una duda: ¿Qué es mejor (y con mejor me refiero a más rápido y más seguro) filtrar los datos a mostrar en el reporte dentro del Procedimiento Almacenado o dentro del reporte?

Si me dices que es mejor por Procedimiento Almacenado estaré en graves problemas, ya que no he encontrado como pasarle los parámetros al procedimiento desde el código.

Felicidades y Gracias por tu ayuda.

Juan Eduardo Bucarey V.

OscarMontesinos dijo...

Utiliza Procedimientos almacenados, son más fáciles de mantener que las consultas y más manejables que las consultas o vistas, puedes consultar este artículo: http://msdn.microsoft.com/en-us/magazine/cc301570.aspx.

Percy dijo...

Buenas, se k es algo tarde pero bueno, mi pregunta si estos principios se comparten con MySQL y PHP?.

Muy buen articulo, una cosa mas k me recomiendas en general Visual o C# . Net.

muchas gracias

OscarMontesinos dijo...

No completamente, cada gestor tiene sus propias características que debería estudiar antes de utilizarlas para no tener que hacer una vuelta atrás. En cuanto al lenguage da casi lo mismo, usa con el que más te encuentres cómodo.

Elviskrit dijo...

hola puedes decirme el codigo de coneccion de vb.net a el procedimiento almacenado de sql server de update y eliminar es q solo tengo el de insert y listar

OscarMontesinos dijo...

Hola, el código de conexión es el mismo para todas las operaciones, en lo único en que se diferencia es en el nombre del procedimiento al que llamas y los parámetros que pasas que por lo que veo los tienes ya en el insert. Si no es así pega el código que tienes y te echo una mano.

Etiquetas

.net (1) ALGORITMO (4) algoritmos (1) Android (1) angular (2) aplicaciones WEB (1) ARGENTINA (1) ASP (4) asp .NET (1) ASP .NET Identity (1) attach (1) Azure (1) base de datos (1) BINDING (1) BioInformática (2) bootstrap (1) C (2) C# (27) castellano (1) catalán (1) Charla (1) CheckedListBox (1) Clase (1) clausura transitiva (2) CLIENTES (1) Closing (1) Combobox (1) Comisiones (1) CONCURSO (1) condones (1) consultas (1) corrupta (1) CORS OWIN (1) CORS WEB API 2 (1) Criptografía (1) CUENTA NARANJA (1) Custom Control (1) Daniel Seara (1) DATAGRIDVIEW (1) David Salgado (2) dendrograma (6) depurar (1) desarrollo (1) Deserializar (1) DEVCAMP (2) durex (1) e-DNI (1) el Guille (2) Eladio Rincón (1) elGamal (1) encriptación (1) encuesta (1) Entidad (1) ERROR (2) error 3624 (1) España (1) España es nido de víboras. (1) Euclides (1) Euclides extendido (1) evento (1) eventos. (1) factorización (1) factorizar (1) física (1) Framework 2.0 (1) Framework 3.5 (5) Framework 4 (2) Francisco González (1) Francisco Ruiz (1) GO (1) google (1) gossip (1) Grupos de usuarios (1) Guadalajara (1) Guille Comunity Tour (2) Gusenet (9) GUSENET. (1) Huelga informáticos (1) IBEX 35 (1) Idioma (1) IFT (1) IISExpress (1) Indice de Fuerza Tecnica (1) informática (1) Informix (1) ING DIRECT (1) INGDIRECT (1) Ingeniería Informática (2) Inteligencia artificial (5) Intro (1) IOS (1) IPad (1) IPhone (1) IV Aniversario (1) JavaScript (2) JPA (2) keybd_event (1) LA CAIXA (1) La Pineda (1) ladroentidad (1) Lector RSS (2) LINQ (5) LINQ2XML (1) ListBox (1) Live Distributed Objects (1) llenar (1) LOG (1) Madrid .NET (1) MainMenu (1) MAP (1) MAP 2011 (1) MAR DEL PLATA (1) matemáticas (1) Matriculación (1) MDIList (1) MdiWindowsListItem (1) MenuItem (1) MenuStrip (1) Microsoft (18) Microsoft Active Professional (1) Miguel Egea (2) MSDE 2 (1) MSDE 2000 (1) MVVM Light (2) mysql (1) NAMESPACE (1) Needleman (2) NET (1) NS (1) Oferta empleo (1) Office 14 (1) Office 2007 (1) Office 2009 (1) ON ERROR (1) OOB (1) Oscar Montesinos (1) OWIN (1) OWIN Security middleware (1) Paginación (1) PDC (1) Pedro Hurtado (1) PINTARTECA (1) PrinterSettings (1) Process (1) ProcessStartInfo (1) Programador (1) Relacional (1) robo (1) RSS (1) Rubén Garrigós (1) Salvador Ramos (1) Santa Pola (1) scrape (1) scraping (1) ScrollBar (1) Secretaría (1) SendInput (1) SENDKEYS (1) Sergio Vázquez (1) Serializar (1) Silverlight 2.0 (2) Silverlight 4 (1) Silverlight 5 (3) Silverlight. (1) Sindicado (1) Sistema Personal de Trading (3) Sistemas Distribuidos (1) SOLID SUMMIT 2009 (1) SPT (2) SQL Server (3) SQL Server 2000 (2) SQL Server 2005 (2) SQL Server 2008 (3) SQL Server Denali (1) SQL Server Information Services. (1) SQLU (1) SSIS (1) SUMMIT SQL (1) Suspect (1) Syndication (1) TAB (1) Tablas (1) Tarragona (1) Token bearer (1) ToolStripMenuItem (1) ToString() (1) Traductor (1) transacciones (1) treeview (1) truco (1) TRY CATCH (1) UCI (1) Unience (2) Universad (1) Universidad Oberta Catalunya (2) UOC (8) Usuarios. (1) VB (1) VB6 (1) VB9 (1) VIAJAR (1) Vila-seca (1) VISUAL BASIC (3) Visual Basic. (1) visual studio (1) Visual Studio 2005 (1) Visual Studio 2008 (16) Visual Studio 2010 (2) Visual Studio 2013 (1) VS2010 (1) VSTA (1) VSTO (1) WCF (1) WEB API (1) web.config (1) WebClient (1) WF (1) windows (1) Windows 7 (3) Windows 8 (2) Windows Azure (1) Windows pone (1) Windows Server 2008 (1) Windows Vista (1) woff (1) WP7 (1) WPF (1) Wunsch (2) XAML (4) XDocument (1) XElement (1) XML (3) XNA (1) Yield (2)