Techrepublic’s free SQL Server newsletter, entregue todas as terças-feiras, contém dicas práticas que o ajudarão a tornar-se mais adepto deste poderoso sistema de gestão de bases de dados relacionais. Assine automaticamente hoje!
a sabedoria geralmente aceita sobre procedimentos armazenados (ou sprocs) é que porque SQL pode otimizá-los e compilá-los,eles correm mais rapidamente do que as declarações SQL equivalentes executadas a partir de QueryAnalyzer (ou talvez passado a partir de algum aplicativo front-end, como uma página Web ou VBprogram). Isto é, em certa medida, verdade.
primeiro, você precisa saber o que o servidor SQL faz com um novo sproc. Na altura da criação, verifica a sintaxe. Se ele não encontrar nenhum erro, então ele adiciona o sproc às tabelas do sistema: sysobjects,sysdepends, e syscomments (este último armazena o corpo do sproc). Por padrão, ele não compila o sproc no momento da criação.
Após a primeira execução do sproc, o servidor SQL otimiza e compila. Isto é quando o servidor SQL cria um queryplan e o armazena em seu cache de procedimentos. On subsequent invocations, SQL looksin the cache first, finds the sproc there, anddoesn’t compile it. Se o sproc não está no cache,então o servidor SQL compila-o e coloca-o no cache.
a minha experiência com a opção com RECOMPILE
um tempo atrás, eu estava a suportar uma página de pesquisa que permitia aos seus utilizadores procurar por qualquer uma das várias colunas. Em seguida, a página chamada de sproc, passando um parâmetro para indicar qual coluna procurar. Examinei o parâmetro usando um bloco de caso, e então executei uma das consultas transversais, dependendo da coluna a procurar.eu sabia que algo estava errado quando comecei a testar o meu procedimento de armazenamento alegado. Em teoria, o desempenho de cada busca deve pelo menos ser aproximadamente o mesmo, mas não foi isso que aconteceu. Quando realizei buscas múltiplas, independentemente da ordem, a primeira seria rápida, e as buscas posteriores eram muito mais lentas.
finalmente, eu percebi que a primeira vez que o procedimento foi chamado, um plano de consulta foi concebido e armazenado no cache. Enquanto pesquisasse naquela coluna, tudo funcionaria como esperado. No momento em que troquei as colunas, no entanto, o desempenho caiu. Porque é que isto aconteceu?
A primeira pesquisa que realizei criou um plano de consulta e storedit no cache. Por exemplo, digamos que estava à procura na data da coluna. Se eu mudasse a pesquisa para a coluna de Nome da empresa, o SQL iria usar cegamente o queryplan em cache, procurando o nome da empresa-alvo usando o OrderDateindex. Não admira que o desempenho caísse tão dramaticamente.
a correção é bastante simples. Executei a opção sprocsuplying The WITH RECOMPILE:
EXEC MySproc_Select '12/31/2004' WITH RECOMPILE
Isto diz ao servidor SQL para deitar fora o plano de pesquisa existente e construir outro–mas só desta vez.
pode também adicionar o com RECOMPILE directamente ao processo stored mesmo antes da palavra-chave AS. Isto diz ao servidor SQL para descartar o plano thequery em cada execução do sproc.
Há também uma terceira opção. Eu poderia ter criado um sproc separado para cada método de busca, e thendecide qual executar dentro do bloco do caso. Dessa forma,o plano de consulta associado com os sub-sprocs permanece no cache, onde o SQL pode se aproveitar deles. Uma vez que cada uma das rodas dentadas exatamente uma coluna, não há necessidade de recompilar.a capacidade do servidor de SQL para otimizar e compilar um processo de armazenamento é ótima, mas se você não tiver cuidado, ele pode morder você quando você menos esperar. Agora que você sabe como lidar com o problema, talvez haja uma nova situação em sua própria base de dados que você pode querer revisitar.