Exemplos de otimização de consultas

Este artigo fornece alguns cenários de exemplo para os três resultados possíveis de dobragem de consultas. Inclui também algumas sugestões sobre como tirar o máximo proveito do mecanismo de query folding e o efeito que ele pode ter nas suas consultas.

O cenário

Imagine um cenário em que, usando o banco de dados Wide World Importers para o banco de dados SQL do Azure Synapse Analytics, você é encarregado de criar uma consulta no Power Query que se conecta fact_Sale à tabela e recupera as últimas 10 vendas com apenas os seguintes campos:

• Chave de venda
• Chave do Cliente
• Chave de data da fatura
• Description
• Quantidade

Observação

Para fins de demonstração, este artigo usa o banco de dados descrito no tutorial sobre como carregar o banco de dados Wide World Importers no Azure Synapse Analytics. A principal diferença neste artigo é que a fact_Sale tabela contém apenas dados para o ano 2000, com um total de 3.644.356 linhas.

Embora os resultados possam não corresponder exatamente aos resultados obtidos seguindo o tutorial da documentação do Azure Synapse Analytics, o objetivo deste artigo é mostrar os principais conceitos e o impacto que a dobragem de consultas pode ter em suas consultas.

Este artigo mostra três maneiras de obter a mesma saída com diferentes níveis de compactação de consulta.

• Sem dobragem de consulta
• Dobragem parcial da consulta
• Dobragem completa da consulta

Nenhum exemplo de dobragem de consulta

Importante

As consultas que dependem exclusivamente de fontes de dados não estruturadas ou que não têm um mecanismo de computação, como arquivos CSV ou Excel, não têm recursos de dobragem de consulta. Isto significa que o Power Query avalia todas as transformações de dados necessárias utilizando o motor do Power Query.

Depois de se conectar ao banco de dados e navegar até a fact_Sale tabela, selecione a transformação Manter linhas inferiores localizada dentro do grupo Reduzir linhas da guia Página Inicial .

Depois de selecionar essa transformação, uma nova caixa de diálogo será exibida. Nesta nova caixa de diálogo, pode introduzir o número de linhas que pretende manter. Nesse caso, insira o valor 10 e selecione OK.

Sugestão

Para este caso, a realização desta operação produz o resultado das últimas 10 vendas. Na maioria dos cenários, recomendamos que você forneça uma lógica mais explícita que defina quais linhas são consideradas por último aplicando uma operação de classificação na tabela.

Em seguida, selecione a transformação Escolher colunas localizada dentro do grupo Gerenciar colunas da guia Página Inicial . Em seguida, você pode selecionar as colunas que deseja manter da tabela e remover o restante.

Por fim, dentro da caixa de diálogo Escolher colunas, selecione as Sale Keycolunas , Customer Key, Invoice Date KeyDescription, e selecione QuantityOK.

O exemplo de código a seguir é o script M completo para a consulta que você criou:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Kept bottom rows" = Table.LastN(Navigation, 10),
    #"Choose columns" = Table.SelectColumns(
        #"Kept bottom rows",
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    )
in
    #"Choose columns""

Sem dobragem de consulta: Noções básicas sobre a avaliação da consulta

Em Etapas aplicadas no editor do Power Query, observe que os indicadores de folding de consulta para Manter linhas inferiores e Escolher colunas são etapas marcadas como avaliadas fora da fonte de dados, ou seja, pelo mecanismo do Power Query.

Você pode clicar com o botão direito do mouse na última etapa da consulta, a chamada Escolher colunas, e selecionar a opção que lê Exibir plano de consulta. O objetivo do plano de consulta é fornecer uma exibição detalhada de como sua consulta é executada. Para saber mais sobre esse recurso, vá para Plano de consulta.

Cada caixa na imagem anterior é chamada de nó. Um nó representa o detalhamento da operação para satisfazer esta consulta. Os nós que representam fontes de dados, como o SQL Server no exemplo anterior e o nó Value.NativeQuery, representam qual parte da consulta é delegada à fonte de dados. Os restantes nós, neste caso Table.LastN e Table.SelectColumns destacados no retângulo na imagem anterior, são avaliados pelo motor do Power Query. Esses dois nós representam as duas transformações que você adicionou, Manter linhas inferiores e Selecionar colunas. O restante dos nós representa operações que acontecem no nível da fonte de dados.

Para ver a solicitação exata enviada à sua fonte de dados, selecione Ver detalhes no nó Value.NativeQuery.

Essa solicitação de fonte de dados está no idioma nativo da sua fonte de dados. Nesse caso, essa linguagem é SQL e essa instrução representa uma solicitação para todas as linhas e campos da fact_Sale tabela.

Consultar essa solicitação de fonte de dados pode ajudá-lo a entender melhor a história que o plano de consulta tenta transmitir:

• Sql.Database: Este nó representa o acesso à origem dos dados. Conecta-se ao banco de dados e envia solicitações de metadados para entender seus recursos.
• Value.NativeQuery: Representa a solicitação que foi gerada pelo Power Query para atender à consulta. O Power Query envia as solicitações de dados em uma instrução SQL nativa para a fonte de dados. Nesse caso, isso representa todos os registros e campos (colunas) da fact_Sale tabela. Para este cenário, este caso é indesejável, pois a tabela contém milhões de linhas e os juros estão apenas nos últimos 10.
• Table.LastN: Assim que o Power Query recebe todos os registos da fact_Sale tabela, utiliza o motor do Power Query para filtrar a tabela e manter apenas as últimas 10 linhas.
• Table.SelectColumns: O Power Query usa a saída do nó Table.LastN e aplica uma nova transformação chamada Table.SelectColumns, que seleciona as colunas específicas que pretende manter de uma tabela.

Para a sua avaliação, esta consulta teve de descarregar todas as linhas e campos da fact_Sale tabela. Essa consulta levou em média 6 minutos e 1 segundo para ser processada em uma instância padrão de fluxos de dados do Power BI (que contabiliza a avaliação e o carregamento de dados para fluxos de dados).

Exemplo de dobragem parcial de consulta

Depois de se conectar ao banco de dados e navegar até a fact_Sale tabela, você começa selecionando as colunas que deseja manter da tabela. Selecione a transformação Escolher colunas localizada dentro do grupo Gerenciar colunas na guia Página Inicial . Essa transformação ajuda você a selecionar explicitamente as colunas que deseja manter da tabela e remover o restante.

Dentro da caixa de diálogo Escolher colunas, selecione as Sale Keycolunas , Customer Key, Invoice Date KeyDescription, e selecione QuantityOK.

Agora você cria uma lógica que classifica a tabela para ter as últimas vendas na parte inferior da tabela. Selecione a Sale Key coluna, que é a chave primária e a sequência incremental ou índice da tabela. Classifique a tabela usando apenas este campo em ordem crescente no menu de contexto da coluna.

Em seguida, selecione o menu contextual da tabela e escolha a transformação Manter linhas inferiores .

Em Manter linhas inferiores, insira o valor 10 e selecione OK.

O exemplo de código a seguir é o script M completo para a consulta que você criou:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Ascending}}),
    #"Kept bottom rows" = Table.LastN(#"Sorted rows", 10)
in
    #"Kept bottom rows"

Exemplo de dobragem parcial de consulta: Noções básicas sobre a avaliação da consulta

Ao verificar o painel de etapas aplicadas, você percebe que os indicadores de dobragem de consulta estão mostrando que a última transformação adicionada, Kept bottom rows, está marcada como uma etapa avaliada fora da fonte de dados ou, em outras palavras, pelo mecanismo do Power Query.

Você pode clicar com o botão direito do mouse na última etapa da consulta, a chamada Kept bottom rows, e selecionar a opção Plano de consulta para entender melhor como a consulta pode ser avaliada.

Cada caixa na imagem anterior é chamada de nó. Um nó representa todos os processos que precisam acontecer (da esquerda para a direita) para que sua consulta seja avaliada. Alguns desses nós podem ser avaliados na sua fonte de dados, enquanto outros, como o nó para Table.LastN, representado pelo passo Linhas Inferiores Mantidas, são avaliados usando o mecanismo do Power Query.

Para ver a solicitação exata enviada à sua fonte de dados, selecione Ver detalhes no nó Value.NativeQuery.

Essa solicitação está no idioma nativo da sua fonte de dados. Nesse caso, essa linguagem é SQL e essa instrução representa uma solicitação para todas as linhas, com apenas os campos solicitados da fact_Sale tabela ordenados pelo Sale Key campo.

Consultar essa solicitação de fonte de dados pode ajudá-lo a entender melhor a história que o plano de consulta completo tenta transmitir. A ordem dos nós é um processo sequencial que começa solicitando os dados da sua fonte de dados:

• Sql.Database: Conecta-se ao banco de dados e envia solicitações de metadados para entender seus recursos.
• Value.NativeQuery: Representa a solicitação gerada pelo Power Query para atender à consulta. O Power Query envia as solicitações de dados em uma instrução SQL nativa para a fonte de dados. Nesse caso, isso representa todos os registros, com apenas os campos solicitados da fact_Sale tabela no banco de dados classificados em ordem crescente pelo Sales Key campo.
• Table.LastN: Assim que o Power Query recebe todos os registos da fact_Sale tabela, utiliza o motor do Power Query para filtrar a tabela e manter apenas as últimas 10 linhas.

Para sua avaliação, esta consulta teve que baixar todas as linhas e apenas os campos obrigatórios da fact_Sale tabela. Demorou em média 3 minutos e 4 segundos para ser processado em uma instância padrão de fluxos de dados do Power BI (que responde pela avaliação e carregamento de dados para fluxos de dados).

Exemplo de dobragem de consulta completa

Depois de se conectar ao banco de dados e navegar até a fact_Sale tabela, comece selecionando as colunas que deseja manter da tabela. Selecione a transformação Escolher colunas localizada dentro do grupo Gerenciar colunas na guia Página Inicial . Essa transformação ajuda você a selecionar explicitamente as colunas que deseja manter da tabela e remover o restante.

Em Escolher colunas, selecione as Sale Keycolunas , Customer Key, Invoice Date Key, Descriptione eQuantity, em seguida, selecione OK.

Agora você cria uma lógica que classifica a tabela para ter as últimas vendas na parte superior da tabela. Selecione a Sale Key coluna, que é a chave primária e a sequência incremental ou índice da tabela. Classifique a tabela somente usando esse campo em ordem decrescente no menu de contexto da coluna.

Em seguida, selecione o menu contextual da tabela e escolha a transformação Manter linhas superiores .

Em Manter linhas superiores, introduza o valor 10 e, em seguida, selecione OK.

O exemplo de código a seguir é o script M completo para a consulta que você criou:

let
    Source = Sql.Database(ServerName, DatabaseName),
    Navigation = Source{[Schema = "wwi", Item = "fact_Sale"]}[Data],
    #"Choose columns" = Table.SelectColumns(
        Navigation, 
        {"Sale Key", "Customer Key", "Invoice Date Key", "Description", "Quantity"}
    ),
    #"Sorted rows" = Table.Sort(#"Choose columns", {{"Sale Key", Order.Descending}}),
    #"Kept top rows" = Table.FirstN(#"Sorted rows", 10)
in
    #"Kept top rows"

Exemplo de dobragem de consulta completa: Noções básicas sobre a avaliação da consulta

Ao verificar o painel de etapas aplicadas, observe que os indicadores de execução de consulta mostram que as transformações que adicionou: Escolher colunas, Linhas classificadas e Linhas superiores mantidas, estão marcadas como etapas avaliadas na fonte de dados.

Você pode clicar com o botão direito do mouse na última etapa da consulta, aquela chamada Linhas superiores mantidas, e selecionar a opção que lê Plano de consulta.

Captura de tela do plano de consulta com a instrução SQL encontrada dentro de 'Value.NativeQuery' que representa uma solicitação dos 10 principais registros da tabela 'fact_Sale' classificados usando o campo 'Chave de venda' e com apenas os campos 'Chave de venda', 'Chave do cliente', 'Chave de data da fatura', 'Descrição' e 'Quantidade'.

Essa solicitação está no idioma nativo da sua fonte de dados. Nesse caso, essa linguagem é SQL e essa instrução representa uma solicitação para todas as linhas e campos da fact_Sale tabela.

Consultar esta consulta de fonte de dados pode ajudá-lo a entender melhor a história que o plano de consulta completo tenta transmitir:

• Sql.Database: Conecta-se ao banco de dados e envia solicitações de metadados para entender seus recursos.
• Value.NativeQuery: Representa a solicitação gerada pelo Power Query para atender à consulta. O Power Query envia as solicitações de dados em uma instrução SQL nativa para a fonte de dados. Para este caso, isso representa uma solicitação apenas para os 10 principais registos da tabela fact_Sale, com apenas os campos obrigatórios, classificados em ordem decrescente usando o campo Sale Key.

Observação

Embora não haja nenhuma cláusula que possa ser usada para SELECIONAR as linhas inferiores de uma tabela na linguagem T-SQL, há uma cláusula TOP que recupera as linhas superiores de uma tabela.

Para sua avaliação, essa consulta baixa apenas 10 linhas, com apenas os campos que você solicitou da fact_Sale tabela. Essa consulta levou em média 31 segundos para ser processada em uma instância padrão de fluxos de dados do Power BI (que contabiliza a avaliação e o carregamento de dados para fluxos de dados).

Comparação de desempenho

Para entender melhor o efeito que o dobramento de consultas tem nessas consultas, você pode atualizá-las, registrar o tempo necessário para atualizar completamente cada consulta e compará-las. Para simplificar, este artigo fornece os tempos médios de atualização capturados usando a mecânica de atualização de fluxos de dados do Power BI ao se conectar a um ambiente dedicado do Azure Synapse Analytics com DW2000c como nível de serviço.

O tempo de atualização para cada consulta foi o seguinte:

Example	Etiqueta	Tempo em segundos
Sem dobragem de consulta	Nenhum	361
Dobragem parcial da consulta	Parcial	184
Dobragem completa da consulta	Completo	31

Muitas vezes, uma consulta que se reverte totalmente para a fonte de dados supera as consultas semelhantes que não se revertem completamente para a fonte de dados. Pode haver muitas razões para que assim seja. Esses motivos vão desde a complexidade das transformações que sua consulta executa até as otimizações de consulta implementadas em sua fonte de dados, como índices e computação dedicada e recursos de rede. Ainda assim, há dois processos-chave específicos que a dobragem de consultas tenta utilizar para minimizar o efeito que estes processos têm quando usados com o Power Query.

• Dados em trânsito
• Transformações executadas pelo motor do Power Query

As seções a seguir explicam o efeito que esses dois processos têm nas consultas mencionadas anteriormente.

Dados em trânsito

Quando uma consulta é executada, ela tenta buscar os dados da fonte de dados como uma de suas primeiras etapas. Os dados obtidos na fonte de dados são definidos pelo mecanismo de dobragem de consulta. Esse mecanismo identifica as etapas da consulta que podem ser descarregadas para a fonte de dados.

A tabela a seguir lista o número de linhas solicitadas da fact_Sale tabela do banco de dados. A tabela também inclui uma breve descrição da instrução SQL enviada para solicitar esses dados da fonte de dados.

Example	Etiqueta	Linhas solicitadas	Description
Sem dobragem de consulta	Nenhum	3644356	Solicitar todos os campos e todos os registros da fact_Sale tabela
Dobragem parcial da consulta	Parcial	3644356	Solicitar todos os registros, mas somente campos obrigatórios da fact_Sale tabela depois que ela foi classificada pelo Sale Key campo
Dobragem completa da consulta	Completo	10	Solicite apenas os campos obrigatórios e os 10 principais registros da tabela fact_Sale classificada em ordem decrescente pelo campo Sale Key

Quando você solicita dados de uma fonte de dados, a fonte de dados precisa calcular os resultados da solicitação e, em seguida, enviar os dados para o solicitante. Embora os recursos de computação já tenham sido mencionados, os recursos de rede de mover os dados da fonte de dados para o Power Query e, em seguida, fazer com que o Power Query seja capaz de receber efetivamente os dados e prepará-los para as transformações que acontecem localmente podem levar algum tempo, dependendo do tamanho dos dados.

Para os exemplos apresentados, o Power Query teve de solicitar mais de 3,6 milhões de linhas à origem de dados para os exemplos de dobragem de consulta sem e com dobragem parcial. Para o exemplo de dobragem completa de consulta, foram solicitadas apenas 10 linhas. Para os campos solicitados, o exemplo de dobragem sem consulta solicitou todos os campos disponíveis da tabela. Tanto a dobragem parcial quanto os exemplos de dobragem de consulta completa enviaram apenas uma solicitação para exatamente os campos necessários.

Atenção

Recomendamos que você implemente soluções de atualização incremental que usem dobragem de consulta para consultas ou tabelas com grandes quantidades de dados. Diferentes integrações de produtos do Power Query implementam tempos limite para encerrar consultas de longa duração. Algumas fontes de dados também implementam limites de tempo em sessões de longa duração, tentando executar consultas dispendiosas nos seus servidores. Para obter mais informações: Utilizando a atualização incremental com fluxos de dados e Utilizando a atualização incremental para modelos semânticos

Transformações executadas pelo motor do Power Query

Este artigo mostrou como você pode usar o plano de consulta para entender melhor como sua consulta pode ser avaliada. Dentro do plano de consulta, você pode ver os nós exatos das operações de transformação executadas pelo mecanismo do Power Query.

A tabela a seguir mostra os nós dos planos de consulta das consultas anteriores que teriam sido avaliadas pelo mecanismo do Power Query.

Example	Etiqueta	Motor de transformação de nós do Power Query
Sem dobragem de consulta	Nenhum	Table.LastN, Table.SelectColumns
Dobragem parcial da consulta	Parcial	Table.LastN
Dobragem completa da consulta	Completo	—

Para os exemplos apresentados neste artigo, o exemplo de dobragem de consulta completa não requer que nenhuma transformação aconteça dentro do mecanismo do Power Query, pois a tabela de saída necessária vem diretamente da fonte de dados. Em contraste, as outras duas consultas exigiam algum cálculo para acontecer no motor do Power Query. Devido à quantidade de dados que precisa ser processada por essas duas consultas, o processo para esses exemplos leva mais tempo do que o exemplo de dobragem de consulta completa.

As transformações podem ser agrupadas nas seguintes categorias:

Tipo de Operador	Description
Controlo remoto	Operadores que são nós de fonte de dados. A avaliação destes operadores ocorre fora do Power Query.
Transmissão	Os operadores são operadores de passagem. Por exemplo, Table.SelectRows com um filtro simples geralmente pode filtrar os resultados à medida que eles passam pelo operador e não precisa reunir todas as linhas antes de mover os dados. Table.SelectColumns e Table.ReorderColumns são outros exemplos deste tipo de operadores.
Verificação completa	Operadores que necessitam de reunir todas as linhas antes que os dados possam passar para o próximo operador na cadeia. Por exemplo, para ordenar dados, o Power Query tem de reunir todos os dados. Outros exemplos de operadores de verificação completa são Table.Group, Table.NestedJoine Table.Pivot.

Sugestão

Embora nem todas as transformações sejam iguais do ponto de vista do desempenho, na maioria dos casos, ter menos transformações geralmente é melhor.

Considerações e sugestões

• Siga as práticas recomendadas ao criar uma nova consulta, conforme indicado em Práticas recomendadas no Power Query.
• Use os indicadores de dobragem da consulta para verificar quais etapas estão impedindo a dobragem da consulta. Reordene-os, se necessário, para aumentar o dobramento.
• Use o plano de consulta para determinar quais transformações estão acontecendo no mecanismo do Power Query para uma etapa específica. Considere modificar sua consulta existente reorganizando suas etapas. Em seguida, verifique o plano de consulta da última etapa da sua consulta novamente e veja se o plano de consulta parece melhor do que o anterior. Por exemplo, o novo plano de consulta tem menos nós do que o anterior, e a maioria dos nós são nós de "Streaming" e não de "verificação completa". Para fontes de dados que suportam dobragem, quaisquer nós dentro do plano de consulta, exceto os Value.NativeQuery nós de acesso à fonte de dados, representam transformações que não foram dobradas.
• Quando disponível, você pode usar a opção Exibir consulta nativa (ou Exibir consulta de fonte de dados) para garantir que sua consulta possa ser dobrada de volta para a fonte de dados. Se essa opção estiver desabilitada para sua etapa e você estiver usando uma fonte que normalmente a habilita, você criará uma etapa que interrompe a dobragem da consulta. Se estiver a utilizar uma fonte que não suporte esta opção, pode confiar nos indicadores de dobragem de consulta e no plano de consulta.
• Use as ferramentas de diagnóstico de consulta para entender melhor as solicitações que estão sendo enviadas à sua fonte de dados quando os recursos de dobragem de consulta estiverem disponíveis para o conector.
• Quando combina dados provenientes da utilização de vários conectores, o Power Query tenta enviar o máximo de trabalho possível para ambas as origens de dados, respeitando os níveis de privacidade definidos para cada origem de dados.
• Leia o artigo sobre os níveis de privacidade para proteger as suas consultas contra a execução de um erro do Data Privacy Firewall.
• Use outras ferramentas para verificar o processamento de consultas da perspetiva da solicitação recebida da fonte de dados. Com base no exemplo deste artigo, pode utilizar o Microsoft SQL Server Profiler para verificar os pedidos enviados pelo Power Query e recebidos pelo Microsoft SQL Server.
• Se adicionar um novo passo a uma consulta totalmente dobrada e o novo passo também se dobrar, o Power Query poderá enviar um novo pedido para a origem de dados em vez de utilizar uma versão em cache do resultado anterior. Na prática, este processo pode resultar em operações aparentemente simples numa pequena quantidade de dados demorarem mais tempo para atualizar na pré-visualização do que o esperado. Esta atualização mais longa deve-se ao facto de o Power Query reconsultar a fonte de dados em vez de utilizar uma cópia local.