SQL Query vs Stored Procedure

Betovsky · June 15, 2009 at 08:48 PM

Bem, no outro dia estive numa discussão com um colega meu sobre as vantagens/desvantagens entre querys e stored procedures. Ele aponta várias vantagens das querys chegando ao ponto de afirmar que raros casos deve-se usar sempre querys em detrimento das SP.

Eu estive a pesquisar pela Internet e até descobri uns posts interessantes:

Certamente haverão muitos mais posts interessantes pela Internet a falar do mesmo assunto. Aliás a primeira impressão foi mesmo a enormidade de pessoas a falar do mesmo tema, querys vs sp.

Eu também acho que as querys têm vantagem sobre os SPs. Mas acho que a diferença entre eles é bastante pequena para se dizer que uma tem mesmo vantagem sobre a outra. O meu colega (e parece-me) a maioria do pessoal pela internet, afirmam que as querys têm mesmo uma enorme vantagem sobre as SPs, até ao ponto de acharem que nem devem ser usadas. Mas também já ouvi pessoal a dizer que só se deve usar SPs e o facto de se usar querys é uma terrível má ideia.

Gostava de saber as vossas opiniões e preferências sobre este assunto.

falco · June 16, 2009 at 02:10 PM

Acho que as situações em que usaria uma coisa, ou outra são tão distintas que não entendo o drama que tanta gente faz com esta questão.

Actualmente estou a trabalhar num projecto grande. A equipa é grande e multi-disciplinar. Temos especialistas para três áreas diferentes (administração de sistemas Unix com conhecimentos de telecomunicações, dbas de Mysql, e programadores de Perl, programadores de Java, etc...). E o resultado vai ser um sistema que tem componentes bastante complexas bastante estanques. Só o que vai ser feito pelos dbas, é tão complexo e isolado do resto do sistema, que é por si só uma aplicação. O que faz com que não faça sentido não usar stored procedures, triggers, events, etc...

Resumindo, acho que é relativamente fácil, na maior parte dos casos, em que identificar os casos em que é necessário utilizar stored procedures.

Quando precisamos de fazer coisas muito complexas com os dados que não têm que ser feitas pela aplicação (por requisitos funcionais desta), quando a performance que o SGBD fornece é superior ao que o resto do sistema consegue oferecer para o caso em que estamos. Faz todo o sentido utilizar stored procedures. Bem como nos casos em que aplicação é muito orientada à base de dados e não hajam requisitos funcionais que requeiram obter dados, ou fazer operações que ficariam ocultas com o stored procedure, ou em casos em que há muitas aplicações a fazer operações iguais sobre a mesma BD que façam sentido em termos de performance e gestão dos recursos ter no lado do SGDB. Outras razões também são apresentadas nos links da primeira mensagem da thread.

Claro que utilizar stored procedures faz com que se tenha que trabalhar de forma diferente (algumas das razões bem explicadas no primeiro link). E termos nós, ou alguém da nossa equipa, capacidade técnica, conhecimento e experiência, é um factor importante a levar em consideração no planeamento até porque este tipo de pessoas por vezes consegue arrancar mais performance do SGDB, mesmo que nem usemos stored procedures (optimizando querys, o schema e configurações do servidor de forma muito considerável). Fazer as coisas de uma forma, ou de outra faz com que os processos de engenharia tenham que ser diferentes e isso tem que ser tido em conta na escolha e lembrar-nos que há implicações para o Futuro.

Num dos links diz-se que há quem diga que se deve utilizar stored procedures sempre que possível. E que isso é errado. Eu também concordo. Ser possível não chega, deve ser é a melhor solução para o nosso caso e para as várias os vários cenários de evolução que vamos aceitar como os escolhidos (que podem ser menos até do que os possíveis para o negócio).

É possível calcular, e até fazer testes e medições para se decidir o que é melhor e com base nessas coisas que se deve fazer as decisões em cada caso. Se calhar não há muitas vantagens para a maior parte dos casos... Mas isso não quer dizer que seja assim para todos. Grandes empresas de base tecnológica, ou simplesmente com enormes volumes de negócios facilmente tiram muito mais proveito de utilizar stored procedures e outras funcionalidades das bases de dados.

Há muita teoria/argumentação a favor dos stored procedures que passa/é compreendida de forma errada a respeito deste tema e alguns dos links colocados no início da thread são exemplo disso (o segundo em particular).

Também há argumentação que é utilizada de forma errada e normalmente é utilizada e fomentada por fabricantes de SGBD, que têm o objectivo de vender SGDBs e serviços relacionados com eles e não têm os objectivos do cliente. O SGDB deve ser uma componente do sistema ao serviço do sistema de informação e temos de ter cuidado para que o sistema de informação não passe a ser o SGDB, porque apesar de ser tecnicamente possível, não é certamente a melhor prática.

jpaulino · June 16, 2009 at 04:26 PM

Eu gosto e uso bastante Stored Procedures, e embora não use e abuse deles, uso sempre que acho necessário. Para mim, considero necessário usá-los em grandes pesquisas a bases de dados e na inserção/actualização de dados.

Isto porque na inserção/actualização de dados posso sempre indicar parâmetros por defeito, fazer o debug do código SQL e encontrar mais facilmente erros que podem vir do código, organizar mais facilmente os meus comandos T-SQL e efectuar pequenas alterações em produção, sem ter de alterar/distribuir o código fonte.

Já nas consultas de grande dimensão, é apenas porque os Stored Procedures são compilados no servidor e optimiza o tempo de execução. Tenho um amigo que esteve a rever um grande projecto de produção, em que a memória do servidor subia dia a dia, e todas as semanas tinha de reinicia-lo. A única alteração que ele fez, foi passar de uma query para um Stored Procedure e o problema ficou resolvido.

É também verdade que eu pessoalmente nunca vi isto acontecer.

Acho que cada qual deve decidir para cada situação o que utilizar, mas em todos os casos, usar sempre querys parametrizáveis para evitar problemas de SQL Injections.

falco · June 16, 2009 at 04:43 PM

Eu dou um exemplo de um caso em que tenho que usar um Stored Procedure:

Quero obter um único result set, correspondem a 7 create tables, 7 drop tables, 6 selects e 9 inserts.

Em 3 selects há subquerys, em todos os selects há operações matemáticas (necessárias para obter o result set pretendido, porque quero dados diferentes dos que estão armazenados).

Infelizmente por causa de restrições de performance não posso utilizar storage engines que me permitam utilizar uma union, e não posso referenciar mais que uma vez a mesma tabela. Que são coisas simplificariam muito o código.

Se isto fosse feito fora do SGDB, seria muito mais lento, ocuparia muitos mais recursos e faria com que a aplicação tivesse que conhecer limitações específicas do SGDB em vez de conhecer os dados que quer obter quando a única relação entre a aplicação e a BD é este result set...

Betovsky · June 18, 2009 at 09:35 PM

Muito bom posts.

Parece que dão vantagem às SPs. Vou por-me um pouco na pele do diabo por um bocado.

Sim, em sistemas enormes em que envolve múltiplas equipas, SPs dão vantagem. Mas na realidade isso é que? Será que chega a 1% do software produzido? Em contrapartida as SPs tem um grande defeito, quebram a arquitectura nTier. Já que a lógica irá ficar dividida. Outro grande problema é quebrar a portabilidade.

Acho que as SPs foram mais importantes do que na actualidade. Os motores DB têm evoluído bastante e em termos de performance estão equiparáveis, e em alguns casos as querys até são mais rápidas que os SPs. Em contrapartida a linguagem das SPs não tem conseguido evoluir ao mesmo ritmo. SQL é velhote e isso nota-se. Comparemos T-SQL ou PL/SQL com C# por exemplo e facilmente vemos qual o mais vantajoso. Há umas décadas atrás realmente tinham uma grande vantagem, sendo linguagens da 4ª geração, mas neste momento não.

Penso que T-SQL permite evocar código .NET mas nunca experimentei nem vi em funcionamento e muito menos como se comporta.

edsousa · June 18, 2009 at 10:00 PM

Não há melhores nem piores. A discussão não faz sentido. Existem SP para umas tarefas, existem query's para outras.

As SP servem para efectuar operações sobre os dados: consolidação, criar views, i.e., as coisas que são completamente alheias às regras de negócio (o produto só pode ser comprado na loja física: a BD bem desenhada deve ser alheia a isso. Agora se pedirem dá-me a factura no 0000, a BD sabe o que isso é). Acrescentar a "business logic" à BD é overkill, além de dar cabo do desempenho das máquinas ao obrigar os servers de BD a fazerem os cálculos todos.

Querys devem se limitar a um SELECT com umas condiçõeszinhas, INSERT's, um DELETE de vez em quando e pouco mais..

Isto nestes termos para mim faz muito mais sentido.

falco · June 19, 2009 at 10:27 AM

Parece que dão vantagem às SPs. Vou por-me um pouco na pele do diabo por um bocado.

Acho que concluíste mal!

Acho que o que tivemos a dizer é que cada caso é um caso, e alguns casos faz sentido usar Stored Procedures e em outros não. O que é preciso é realizar um processo de análise do problema e dos meios que temos ao dispor, ou que poderemos necessitar nas várias soluções possíveis. Para podermos utilizar a melhor solução para esse caso.

Sim, em sistemas enormes em que envolve múltiplas equipas, SPs dão vantagem. Mas na realidade isso é que? Será que chega a 1% do software produzido? Em contrapartida as SPs tem um grande defeito, quebram a arquitectura nTier. Já que a lógica irá ficar dividida. Outro grande problema é quebrar a portabilidade.

Não sei exactamente qual é a percentagem, mas duvido que seja assim tão pequena. Por exemplo nas empresas de telecomunicações e as empresas de distribuição isso é o mais comum. E essas empresas desenvolvem muito software (quer pelas suas próprias equipas, quer comprando serviços de desenvolvimento de software), software esse que normalmente lida com bases de dados complexas de grandes dimensões (é frequente terem BD que engordam dezenas de GB por dia).

Tal como disso cada caso é um caso, e o que faz sentido num caso não tem que fazer em outro. A questão não faz sentido de uma forma generalista, apenas caso a caso.

Os motores DB têm evoluído bastante e em termos de performance estão equiparáveis, e em alguns casos as querys até são mais rápidas que os SPs.

Sim mas uma query não faz tudo o que um Stored Procedure pode fazer em termos de processamento dos dados. E há casos em que os SGDB podem ser bem mais rápidos que qualquer código que tenhas fora da BD e nesses casos tens de ver se na lógica da tua arquitectura faz, ou não faz sentido utilizar o um Stored Procedure para fazer esse processamento, ou se preferes que o código que está fora da BD faça isso apesar de ser mais lento. Trata-se de um trade-off que deve se analisado em cada projecto.

Não há melhores nem piores. A discussão não faz sentido. Existem SP para umas tarefas, existem query's para outras.

Essencialmente é isso!

As SP servem para efectuar operações sobre os dados: consolidação, criar views, i.e., as coisas que são completamente alheias às regras de negócio

Tal como as views, os Stored Procedures devem ser utilizados essencialmente para ocultar a complexidade da BD. Por exemplo podes ter uma BD com os dados armazenados de formas completamente inúteis para o negócio, por teres restrições sobre a forma como eles são introduzidos, ou por razões de eficiência do ponto de vista da performance e controlo de custos da BD. Os stored procedures ajudam-te a poder não ter de lidar com essa a complexidade e tirar o maior partido do SGDB.

Tenho bases de dados em que tenho operações de consulta que não só exigiriam processamento pesado fora da BD para se poder ter os dados que fazem sentido para o negócio, essas operações de consulta, implicam a utilização de diversas operações de leitura escrita, a chamada de funções do SGBD, etc... Não utilizar um Stored Procedure nestes casos forçava a meter fora da BD, muito processamento pesado e consumo de memória desnecessário, e as operações sobre os dados não seriam mais rápidas. Em casos como este o que faz sentido é utilizar um stored procedure... Perde-se em outras coisas? Claro, mas a vantagem é nestes casos superior.

Betovsky · June 20, 2009 at 12:43 PM

Não há melhores nem piores. A discussão não faz sentido. Existem SP para umas tarefas, existem query's para outras.

Sim, ambas são igualmente válidas, acho que isso nunca esteve em questão e só os extremistas é que pensam que só existe uma opção. O que está-se a discutir é o meio termo. O mundo não é a preto e branco, as SPs e Querys são ambas válidas na maior parte das situações e é essa área cinzenta que gostava de discutir. Acho que é óbvio para todos que para fazer um select, uma SP é overkill.

As SP servem para efectuar operações sobre os dados: consolidação, criar views, i.e., as coisas que são completamente alheias às regras de negócio (o produto só pode ser comprado na loja física: a BD bem desenhada deve ser alheia a isso. Agora se pedirem dá-me a factura no 0000, a BD sabe o que isso é).

Não percebi a analogia entre a loja física e a BD. Mas em relação à factura, será que isso não pode ser considerado como lógica de negócio?

Querys devem se limitar a um SELECT com umas condiçõeszinhas, INSERT's, um DELETE de vez em quando e pouco mais..

Yep. Mas e tipo, se for necessário validações pelo meio de vários selects, inserts e updates. Farias com um SP, ou usavas multiplas querys. Por exemplo, imagina que tens certos dados e queres inserir numa tabela, depois queres fazer 1 ou mais outros selects e dependendo do resultado fazer mais inserts/updates noutras tabelas, que podem estar ou não relacionadas com a primeira. Fazer um SP é perfeitamente válido, mas multi-querys também o é.

Acho que concluíste mal!

Acho que o que tivemos a dizer é que cada caso é um caso, e alguns casos faz sentido usar Stored Procedures e em outros não. O que é preciso é realizar um processo de análise do problema e dos meios que temos ao dispor, ou que poderemos necessitar nas várias soluções possíveis. Para podermos utilizar a melhor solução para esse caso.

Sim, se calhar concluí mal já que só vos vi a dizer vantagens dos SPs. A ideia era discutir as vantagens/desvantagens que os SPs tem em relação às querys e vice-versa.

Não sei exactamente qual é a percentagem, mas duvido que seja assim tão pequena. Por exemplo nas empresas de telecomunicações e as empresas de distribuição isso é o mais comum. E essas empresas desenvolvem muito software (quer pelas suas próprias equipas, quer comprando serviços de desenvolvimento de software), software esse que normalmente lida com bases de dados complexas de grandes dimensões (é frequente terem BD que engordam dezenas de GB por dia).

Sim mas para cada empresa de telecomunicações existe centenas de pequenas/médias software houses.

Sim mas uma query não faz tudo o que um Stored Procedure pode fazer em termos de processamento dos dados. E há casos em que os SGDB podem ser bem mais rápidos que qualquer código que tenhas fora da BD e nesses casos tens de ver se na lógica da tua arquitectura faz, ou não faz sentido utilizar o um Stored Procedure para fazer esse processamento, ou se preferes que o código que está fora da BD faça isso apesar de ser mais lento. Trata-se de um trade-off que deve se analisado em cada projecto.

Depende. A ideia não é uma query substituir um SP. Caso for isso então o SP é desnecessário. A ideia é em vez de teres a lógica do lado do SP em PL/SQL (caso Oracle) teres no código em C ou Java e usares múltiplas-querys. Código fora da BD não significa que seja mais lento, nos motores de hoje em dia a performance é similar.

Tal como as views, os Stored Procedures devem ser utilizados essencialmente para ocultar a complexidade da BD. Por exemplo podes ter uma BD com os dados armazenados de formas completamente inúteis para o negócio, por teres restrições sobre a forma como eles são introduzidos, ou por razões de eficiência do ponto de vista da performance e controlo de custos da BD. Os stored procedures ajudam-te a poder não ter de lidar com essa a complexidade e tirar o maior partido do SGDB.

Tenho bases de dados em que tenho operações de consulta que não só exigiriam processamento pesado fora da BD para se poder ter os dados que fazem sentido para o negócio, essas operações de consulta, implicam a utilização de diversas operações de leitura escrita, a chamada de funções do SGBD, etc... Não utilizar um Stored Procedure nestes casos forçava a meter fora da BD, muito processamento pesado e consumo de memória desnecessário, e as operações sobre os dados não seriam mais rápidas. Em casos como este o que faz sentido é utilizar um stored procedure... Perde-se em outras coisas? Claro, mas a vantagem é nestes casos superior.

Depende, se não me engano o Facebook nem os joins faz no lado da BD. Mas acho que o motivo é que para eles é mais fácil paralelizar a camada dos serviços, onde é feito todo esse processamento, que na camada do MySQL.

falco · June 20, 2009 at 05:24 PM

Sim, ambas são igualmente válidas, acho que isso nunca esteve em questão e só os extremistas é que pensam que só existe uma opção. O que está-se a discutir é o meio termo. O mundo não é a preto e branco, as SPs e Querys são ambas válidas na maior parte das situações e é essa área cinzenta que gostava de discutir. Acho que é óbvio para todos que para fazer um select, uma SP é overkill.

Concordo que nem sempre é inicialmente óbvio o que usar num caso. Mas quando há dúvidas, isso acontece é porque, ou não estamos a considerar todas as hipóteses de solução, ou os parâmetros de avaliação necessários.

Há muita coisa a ter em conta, como a infra-estrutura que já existe a orientação do sistema a ser desenvolvido, etc... Todas as coisas devem ser levadas em conta e é a ponderação alargada de todas as questões que deve ser feita, acho que não devemos resuzir a x, ou y e que devemos tratar cada caso de forma diferente, de acordo com as suas especificidades.

Sim mas para cada empresa de telecomunicações existe centenas de pequenas/médias software houses.

Nah!

Em primeiro lugar, quase não há software houses, existem é muitas empresas de consultoria de outsourcing que produzem software, mas normalmente fazem-no para grandes empresas, ou para o estado, como um serviço.

A ideia é em vez de teres a lógica do lado do SP em PL/SQL (caso Oracle) teres no código em C ou Java e usares múltiplas-querys. Código fora da BD não significa que seja mais lento, nos motores de hoje em dia a performance é similar.

Lógica de quê? Do negócio?

Quanto à performance eu discordo. Há casos em que diferença é significativa.

Depende, se não me engano o Facebook nem os joins faz no lado da BD. Mas acho que o motivo é que para eles é mais fácil paralelizar a camada dos serviços, onde é feito todo esse processamento, que na camada do MySQL.

É uma opção de arquitectura... Como a desconheço, não me pronuncio sobre ela.

No entanto demonstra o que eu tenho tentado dizer. Cada caso é um caso, e o que faz sentido num caso não é o que tem que fazer em outro.

Betovsky · June 20, 2009 at 08:31 PM

É uma opção de arquitectura... Como a desconheço, não me pronuncio sobre ela.

No entanto demonstra o que eu tenho tentado dizer. Cada caso é um caso, e o que faz sentido num caso não é o que tem que fazer em outro.

Era um artigo a falar de uma plataforma de Haskell em que se discutia várias aspectos sobre a escalabilidade dessa plataforma. E pelo meio falava do Facebook e tinha um link para uma palestra de um dos gajos do Facebook a falar a arquitectura deles. Bastante interessante e não foi ainda a muito tempo, se tiveres interessado diz que vou procurar no histórico.

falco · June 21, 2009 at 03:50 PM

Coloca aí, pode ser que mais gente queira ver...

Betovsky · June 21, 2009 at 07:32 PM

Deu um pouco de trabalho, mas aqui está.

O artigo: http://groups.google.com/group/HAppS/browse_thread/thread/8663c04f3b104133

Que contem o link para o video do facebook: http://www.infoq.com/presentations/Facebook-Software-Stack

Sign In

SQL Query vs Stored Procedure

Recommended Posts

Betovsky

falco

jpaulino

falco

Betovsky

edsousa

falco

Betovsky

falco

Betovsky

falco

Betovsky

Create an account or sign in to comment

Create an account

Sign in

Home

Activity

Revista PROGRAMAR

Important Information