Banco de Dados - Aula 10 - 04 de Outubro

SISTEMAS DE INFORMAÇÃO
Sistemas de Bancos de Dados I
2º Semestre – 2010
Pedro Antonio Galvão Junior
E-mail: [email protected] Fone: 9531-7555
[email protected]
Versão 1.10.10 – Out/2010.
QUERY PROCESSOR
(PROCESSADOR DE QUERY)
INTRODUÇÃO
•
Escolher o melhor caminho para chegar a determinado lugar pode ser considerado para
muitos uma arte, isso porque sempre existem vários caminhos que levam ao mesmo
destino. Executar uma tarefa da forma mais eficiente possível requer que o caminho
percorrido seja o melhor dentre as centenas de variáveis que podem influenciar na
escolha do melhor percurso.
•
No SQL Server o responsável por calcular a maneira mais eficiente de acesso aos dados é
chamado de Query Processor, ele é dividido em duas partes, o Query Optimizer e o
Query Execution Engine. Entender como funciona e como interpretar o trabalho do
Query Optimizer é uma das melhores maneiras de aprominorar seus conhecimentos no
SQL Server.
QUERY OPTIMIZER
INTRODUÇÃO
• Quando um comando T-SQL é executado no SQL Server, o Query
Processor(Processador de Querys) entra em ação para gerar um Plano de
Execução(Execution Plan).
• Este plano dirá qual é a melhor maneira de acessar os dados gastando menos
recursos e com o desempenho mais eficiente.
• Podemos observar na a seguir a ação do Query Optimizer (em vermelho) e uma
série de passos para compilar e executar um comando T-SQL.
Fluxograma – Plano de Execução
PREPARANDO O
AMBIENTE
Preparando o Ambiente
•
Para melhor entendimento dos exemplos, criaremos uma tabela, com alguns dados e
uma visão, que servirão como base para os testes apresentados posteriormente.
•
Criando a tabela Funcinarios:
Create Table Funcionarios
(ID Int Identity(1,1) Primary Key,
Nome Varchar(30),
Salario Numeric(18,2),
Telefone VarChar(15),
Cidade Varchar(80))
Preparando o Ambiente
•
Inserindo dados na Tabela Funcinarios:
Insert Into Funcionarios(Nome, Salario, Telefone, Cidade)
Values (‘José’, 850.30, ‘11-55960015’, ‘São Paulo’)
Insert Into Funcionarios(Nome, Salario, Telefone, Cidade)
Values (‘Antonio’, 950, ‘11-81115544’, ‘São Paulo’)
Insert Into Funcionarios(Nome, Salario, Telefone, Cidade)
Values (‘João’, 1200, ‘11-44123321’, ‘São Paulo’)
Preparando o Ambiente
•
Criando a Visão para consulta de dados:
Create View vw_Funcionarios
As
Select Nome, Salario From Funcionarios
Where Salario > 900
Entendendo o Plano de
Execução
(Execution Plan)
Entendendo o Plano de Execução
• Supondo que um SELECT simples, por exemplo, SELECT * FROM Funcionarios,
seja enviado ao servidor, a primeira tarefa que o Query Processor fará com o
comando é verificarf se o mesmo está no Cache Plan(Cache do Plano de
Execução). Caso ele não esteja em cache, o Query Processor enviará o
comando para os processos de Parse e Bind.
• O Parse/Bind executa um processo conhecido como Algebrizer. Durante este
processo o SQL tenta encontrar possíveis erros de escrita na sintaxe e lógica do
comando. O Algebrizer também expande as definições de comando, isso
significa que ele troca “select *” por “Select col1, col2, col3…”.
Entendendo o Plano de Execução
• Quando estamos realizando um “Select” sobre uma View, o SQL acessa a tabela
de origem para ler os dados que serão exibidos sobre as colunas e tabelas
utilizadas pela View.
• Após estas análises o Parse/Bind retorna um binário chamado Query Processor
Tree, que é uma representação lógica dos passos necessários para a execução
do comando SQL. O Query Processor Tree é enviado para o próximo passo da
execução da consulta, que é a análise do Query Optimizer.
• Alguns comandos como por exemplo, Create Table não necessitam ser
analisados pelo Query Optimizer, pois possuem somente uma forma de
execução.
Entendendo o Plano de Execução
• Quando o Query Optimizer recebe o Query Processor Tree, ele dará inicio a
uma série de análise a fim de encontrar qual é a maneira mais eficiente de
acessar os dados desejados. O Query Optimizer trabalha baseado no custo de
cada operador de acesso a dados, ou seja, ele tenta encontrar a maneira que
gastará menos recursos para retornar os dados. Durante esta fase de análise o
Query Optimizer realiza algumas tarefas:
– Identificação de todos os possíveis argumentos de pesquisa que podem ser
especificados na cláusula Where;
– Verificar se existem Joins entre tabelas que devem ser otimizadas.
Entendendo o Plano de Execução
• Na análise do Query Optimizer também existem um processo chamado
“Simplification”, que é executado logo no inicio da otimização. Uma das tarefas
do “Simplification” é conhecida como “Predicate PushDown”, onde ele tenta
reescrever sua consulta jogando argumentos de pesquisa para a cláusula
Where.
• Query criada pelo Usuário:
SELECT F.* FROM FUNCIONARIOS F INNER JOIN FUNCIONARIOS AS F2
ON F.ID = F2.ID
AND F.NOME = 'ANTONIO'
WHERE F.ID > 1
Entendendo o Plano de Execução
• Query modificada pelo Query Optimizer:
SELECT F.*
FROM FUNCIONARIOS F INNER JOIN FUNCIONARIOS AS F2
ON F.ID = F2.ID
WHERE F.ID > 1
AND F.NOME = ‘ANTONIO’
Entendendo o Plano de Execução
• Com base nesta simples representação gráfica podemos visualizar como o
Query Optimizer funciona. Como podemos observar, o resultado chamado pelo
Query Optimizer após sua análise será definido como Query Plan, ou Plano de
Execução de Consultas.
Trabalhando com o
Plano de Execução
(Execution Plan)
Ordem de Execução
• O Plano de Execução realiza a seguinte sequência de passos internos:
– (1) Todas as tabelas no FROM são unidas montando um plano cartesiano;
– (2) Os filtros baseados na cláusula ON são executados;
– (3) Os tipos de JOINs (LEFT, INNER, RIGHT, etc) são avaliados, para verificar que linhas devem
ou não permanecer;
– (4) Os filtros da cláusula WHERE são aplicados;
– (5) As colunas na cláusula GROUP BY são aplicadas montando os grupos;
– (6) A cláusula WITH CUBE ou WITH ROLLUP é aplicada;
– (7) A cláusula HAVING é aplicada fazendo os filtros nos grupos;
– (8) A cláusula SELECT é montada;
– (9) As repetições são filtradas pelo DISTINCT ;
– (10) A ordenação é feita pelo ORDER BY; e
– (11) Os N primeiros registros são retornados pelo TOP.
Visualizando o
Plano de Execução
Modo Gráfico, Modo Texto e XML.
Modos de Visualização
• Existem três maneiras de visualizar o plano de execução:
– Modo gráfico;
– Modo texto; e
– Formato e estrutura XML.
• Comandos para visualizar o Plano de Execução:
– SET SHOWPLAN_TEXT ON;
– SET SHOWPLAN_ALL ON;
– SET STATISTICS PROFILE ON;
– SET SHOWPLAN_XML ON;
– SET STATISTICS XML ON.