Este é o quarto artigo de uma série de aprendizagem da instrução CREATE VIEW SQL. Até agora, temos feito uma grande quantidade de criação e alteração de pontos de vista usando T-SQL. Nesta última parte, eu quero dar uma grande olhada em como trabalhar com vistas indexadas.
Como sempre, para acompanhar junto com a série, é altamente recomendado ler as partes anteriores primeiro e depois este., Isto é principalmente porque nós criamos nosso próprio banco de dados de amostra e objetos nele a partir do zero que nós estaremos usando nesta parte também, mas também porque será muito mais fácil de ver o quadro grande.
Aqui estão as três peças anteriores da série CREATE VIEW SQL:
- A Criação de vistas no servidor SQL
- A inserção de dados através de vistas no servidor SQL
A modificação de vistas no servidor SQL
assim, vá para cima e leia aqueles antes de continuar com este.
introdução
a primeira coisa que vamos fazer é criar uma vista indexada., Nós, naturalmente, usaremos a instrução CREATE VIEW SQL para isto como fizemos muitas vezes através da série. Mas a idéia geral, como diz O título, é ver como trabalhar com vistas indexadas, ver quais são os requisitos para adicionar um índice a uma visão, e como fazê-lo programaticamente. Além disso, para explicar os pontos de vista indexados, vamos ver os planos de execuções no servidor SQL. Eles são uma grande ferramenta para DBAs e desenvolvedores quando se trata de encontrar e consertar um gargalo na consulta de execução lenta.,
Sem mais delongas, vamos criar uma view usando o modo de EXIBIÇÃO de criação instrução SQL abaixo e veja o que ele faz:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
USAR SQLShackDB;
IR
CREATE VIEW dbo.,vcordadores de empregados
com esquemas
como
selecciona Empregados.Emprego, relações industriais e assuntos sociais(Preço * quantidade) como SaleTotal,
Saledato div> dbo.Os empregados aderem à dbo.Vendas de Empregados.Emprego = Vendas.Emprego (DBO).Produtos em vendas.Produtos = Produtos.Grupo de produtos por Empregados.Emprego, relações industriais e assuntos sociaisProdutos, vendas,Saledato;
GO
|
Notice that this view has a with SCHEMABINDING option on it. A razão pela qual ele tem esta opção ligada é porque ao criar índices sobre vistas, eles realmente são fisicamente armazenados na base de dados. Em outras palavras, qualquer coisa em que esta visão se baseie, no que diz respeito às tabelas, a estrutura não pode mudar do que estamos referenciando.
portanto, ele deve estar ligado às tabelas subjacentes de modo que não podemos modificá-las de uma forma que afetaria a definição de vista., Se tentarmos alterá-los de alguma forma, o servidor SQL irá lançar um erro dizendo que esta vista depende de algo. Então, veja isso como um requisito difícil para criar um índice em uma vista.
Uma vez que o comando é executado com sucesso, você deve ver a vista do vwempeesalesorders sob a pasta Views no Object Explorer como mostrado abaixo:
A definição da vista é uma consulta um pouco mais complexa. Temos um agregado na declaração seleta seguida pela cláusula do grupo., Lembre-se, quando temos um agregado na consulta, Ele adiciona os números juntos, então precisamos ter o grupo por cláusula.
basicamente, quando há um grupo em uma consulta, precisamos agrupar por tudo o que está na lista selecionada, exceto o agregado.,
Agora, eu já criou a vista, mas lembre-se que é sempre uma boa idéia para testar a definição do modo de exibição executando apenas a parte de CRIAR modo de EXIBIÇÃO instrução SQL para ver o que retorna:
Mover, aqui está como você pode verificar se o Esquema dependente opção é activada ou desactivada., Dirija-se ao Explorador de objectos, expanda as vistas, carregue com o botão direito na janela e seleccione as propriedades:
entre todas as outras informações na janela de Propriedades da vista, irá ver se a opção de limite do esquema é verdadeira ou falsa sob a página Geral.
criando visualizações indexadas
vamos seguir em frente e criar um índice na nossa visão., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:
|
1
2
3
4
5
6
|
USE SQLShackDB;
GO
CREATE UNIQUE CLUSTERED INDEX CAK_vEmployeesSalesOrders
ON dbo.,se carregarmos no botão Executar no SSMS, o servidor SQL irá lançar um erro dizendo que o índice não pode ser criado.a6bb7fd726″>
Aqui está a mensagem de erro completa que não pode ser vista na imagem acima: não pode criar índice na vista ‘ sqlshackdb.dbo.v employeesalesorders ‘ porque sua lista seleta não inclui um uso adequado de COUNT_BIG. Considere adicionar o COUNT_ big (*) para seleccionar a lista., precisamos de COUNT_BIG neste caso, dado o fato de que estamos usando grupo por na nossa visão. em geral, se estamos usando agregados como contagem, soma, AVG, etc. na lista de seleção do Índice, nós também temos que incluir o COUNT_BIG, a fim de criar um índice nele.é exactamente isso que vamos fazer.,usando o CREATE VIEW SQL instrução e adicionar COUNT_BIG para a escolha de lista usando o script abaixo:
Se você está se perguntando por que isso está acontecendo neste caso, a resposta é porque o SQL Server precisa de uma maneira de controlar o número do registro que estamos a ligar para o índice e isso também é um de uma série de limitações com a criação de modos de exibição indexados.até agora, tudo parece bem. Nós mudamos com sucesso a definição da nossa vista: Agora, podemos voltar a criar um índice na vista executando o script usado anteriormente mais uma vez., Desta vez, a operação será concluída sem problemas. Se formos ao Object Explorer e expandir a pasta de índices da nossa Vista, veremos o índice recentemente criado:
a partir daqui, se clicarmos com o botão direito no índice e seleccionarmos Propriedades, sob a página Geral, poderá ver o nome da vista, nome do Índice, tipo, colunas-chave, etc.,: Se vamos mudar para Armazenamento, você vai ver que tem um Grupo de arquivos, porque ele está fisicamente armazenado em um banco de dados: Além disso, se vamos mudar para a Fragmentação, ele deve dizer que a Total fragmentação é de zero por cento, porque nós só temos alguns registros em nossas tabelas: à Procura de um detalhadas, mas divertido e de fácil leitura da cartilha sobre a manutenção e monitoramento do SQL índices? Veja a manutenção do Índice SQL., apagar índicesAntes de irmos mais longe, vamos ver como podemos apagar um índice. A maneira mais fácil é clicar com o botão direito sobre o índice no Object Explorer e usar a opção Delete. Mas no caso de você precisar de deixar cair vários índices de uma vez, a declaração do Índice DROP vem a calhar. Isso é o que vamos fazer, porque, afinal de contas, esta é uma série T-SQL sobre aprender a declaração CREATE VIEW SQL., Use the script from below to drop the CAK_vEmployeesSalesOrders index:
Quando você precisa soltar vários índices, basta indicar todos os nomes separados por uma vírgula. gerando dados aleatóriosAgora, que nos livramos do Índice, vamos gerar alguns dados aleatórios em nossa tabela para que possamos olhar para o plano de execução e ver como o servidor SQL obtém dados sob o capô. Analisar o plano de execução mostrará a diferença em como o desempenho é afetado por executar a consulta com e sem um índice na vista., Usar o script abaixo para inserir 50000 aleatória de registros para a tabela de Vendas: eu não vou levá-lo através do script em detalhes, mas é basicamente um loop que irá executar 50000 vezes e inserir dados aleatórios para a tabela de Vendas., para verificar se os registros foram inseridas com êxito, execute a seguinte instrução SELECT que contará todos os registros da tabela de Vendas:
O número retornado mostra que há 50006 linhas na tabela de Vendas., Este é o número de registros que acabamos de gerar + 6 que nós inicialmente tinha: Análise de planos de execuçãoAgora que temos alguns dados na nossa tabela, podemos realmente demonstrar o uso de um índice sobre o modo de exibição. Vamos consultar a vista do V employeesalesorders e ver como o servidor SQL recupera os dados., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:
Este script retornou 23814 linhas, mas o que é mais importante, é gerado o plano de execução da consulta. Lembre-se que nós já deixamos cair o índice em nossa Vista. Por isso, neste momento, não há nenhum índice na nossa Vista. Portanto, o SQL Server irá fazer algumas verificações de tabela, como mostrado abaixo: Isto é quase a pior coisa do mundo do banco de dados, especialmente em tabelas com uma grande quantidade de dados., É bom ter varreduras de mesa com uma pequena quantidade de dados, por exemplo, o caso com nossos funcionários e tabelas de produtos, mas é ruim para a tabela de vendas, porque tem 50K+ registros. A maneira mais fácil de se livrar das varreduras de tabela é criar um índice sobre ele porque ele acelera dramaticamente as coisas. Então, o que vamos fazer para resolver este problema é Executar novamente o script para criar o único índice agrupado na vista do V employeesalesorders. agora, se apenas executarmos novamente a instrução SELECT, não haverá diferenças mesmo que tenhamos criado o índice. Porquê?, Porque eu estou usando a edição SQL Server Express para o propósito desta série, e apenas nas edições Enterprise e desenvolvedor do servidor SQL O otimizador de consulta realmente levar o índice em consideração. não se preocupe porque nós podemos realmente forçar o servidor SQL a usar um índice ao gerar planos de execução. Isto é feito usando a opção NOEXPAND., NOEXPAND aplica-se apenas aos modos de exibição indexados:
do mesmo modo, forçámos o servidor de SQL a usar o índice agrupado, o que significa basicamente não usar as tabelas subjacentes ao obter dados., Como pode ser visto abaixo, fizemos alguns progressos, pela eliminação de um número de operações: Na verdade, podemos executeboth instruções SELECT simultaneamente e comparar os resultados observando os planos de execução: se você olhar para isso? Se compararmos o custo da consulta da primeira instrução selecionada (Índice de W/O 95%) com a segunda instrução selecionada (Índice de w/ 5%), eu diria que é um enorme ganho de desempenho usando um único índice., conclusãoíndices são grandes porque eles aceleram o desempenho e com um índice em uma vista que deve realmente acelerar o desempenho, porque o índice é armazenado na base de dados. Indexar tanto visualizações quanto tabelas é uma das formas mais eficientes de melhorar o desempenho das consultas e aplicações que as utilizam. Eu gostaria de embrulhar as coisas e terminar esta série de aprendizagem a declaração criar vista SQL com este artigo. Já tratámos de tudo sobre criar e alterar vistas com o T-SQL., Começamos com a declaração CREATE VIEW SQL, criamos algumas visualizações, alterá-las, apagá-las e muito mais. espero que esta série de aprendizagem a declaração SQL CREATE VIEW tenha sido informativa para si e agradeço-lhe por a ter lido., CREATE VIEW SQL: Inserção de dados através de pontos de vista no SQL Server CREATE VIEW SQL: Trabalhar com modos de exibição indexados no SQL Server
Bojan aka “Boksi”, um AP de pós-graduação em Tecnologia de TI focada em Redes de eletrônicos e tecnologia de Copenhaga Escola de Design e Tecnologia, é um analista de software com experiência em garantia de qualidade, suporte de software, produto de evangelismo, e o envolvimento do usuário., ele tem escrito extensivamente sobre o SQL Shack e o ApexSQL Solution Center, em tópicos que vão desde tecnologias de clientes como resolução 4K e temas, manipulação de erros até estratégias de indexação e monitoramento de desempenho.Bojan trabalha na ApexSQL em Nis, Sérvia como parte integrante da equipe focando em projetar, desenvolver e testar a próxima geração de ferramentas de banco de dados, incluindo MySQL e SQL Server, e tanto ferramentas e integrações autônomas em Visual Studio, SSMS e VSCode.,
Veja mais sobre Bojan no LinkedIn Ver todos os posts por Bojan Petrovic posts mais Recentes por Bojan Petrovic (ver todas)
|