Plano de consulta para o Power Query (versão prévia)

O plano de consulta para o Power Query é um recurso que fornece uma visão melhor da avaliação da consulta. É útil ajudar a determinar por que uma consulta específica pode não dobrar em uma etapa específica.

Por meio de um exemplo prático, este artigo demonstrará o caso de uso principal e os benefícios potenciais de usar o recurso de plano de consulta para examinar as etapas de consulta. Os exemplos usados neste artigo foram criados usando o banco de dados de exemplo AdventureWorksLT para o Azure SQL Server, que você pode baixar dos bancos de dados de exemplo do AdventureWorks.

Observação

O recurso de plano de consulta para o Power Query só está disponível no Power Query Online.

Este artigo foi dividido em uma série de etapas recomendadas para interpretar o plano de consulta. Essas etapas são:

1. Examine os indicadores de dobragem de consultas.
2. Selecione a etapa de consulta para examinar seu plano de consulta.
3. Implemente as alterações na consulta.

Use as etapas a seguir para criar a consulta em seu próprio ambiente do Power Query Online.

1. Em Power Query – Escolha a fonte de dados, selecione Consulta em branco.

2. Substitua o script da consulta em branco pela consulta a seguir.

   let
     Source = Sql.Database("servername", "database"),
     Navigation = Source{[Schema = "Sales", Item = "SalesOrderHeader"]}[Data],
     #"Removed other columns" = Table.SelectColumns(Navigation, {"SalesOrderID", "OrderDate", "SalesOrderNumber", "PurchaseOrderNumber", "AccountNumber", "CustomerID", "TotalDue"}),
     #"Filtered rows" = Table.SelectRows(#"Removed other columns", each [TotalDue] > 1000),
     #"Kept bottom rows" = Table.LastN(#"Filtered rows", 5)
   in
     #"Kept bottom rows"
   

3. Altere servername e database pelos nomes corretos para seu próprio ambiente.

4. (Opcional) Se você estiver tentando se conectar a um servidor e banco de dados para um ambiente local, configure um gateway para esse ambiente.

5. Selecione Avançar.

6. No Editor do Power Query, selecione Configurar conexão e forneça as credenciais para sua fonte de dados.

Observação

Para obter mais informações sobre como se conectar a um SQL Server, acesse Banco de dados do SQL Server.

Depois de seguir estas etapas, sua consulta será semelhante à imagem a seguir.

Esta consulta se conecta à tabela SalesOrderHeader e seleciona algumas colunas dos últimos cinco pedidos com um valor TotalDue acima de 1000.

Observação

Este artigo usa um exemplo simplificado para demonstrar esse recurso, mas os conceitos descritos neste artigo se aplicam a todas as consultas. Recomendamos que você tenha um bom conhecimento da dobragem de consultas antes de ler o plano de consulta. Para saber mais sobre a dobragem de consultas, acesse as Noções básicas sobre dobragem de consultas.

1. Examine os indicadores de dobragem de consultas

Observação

Antes de ler esta seção, recomendamos que você examine o artigo sobre Indicadores de dobragem de consultas.

Sua primeira etapa nesse processo é examinar sua consulta e prestar muita atenção aos indicadores de dobragem de consultas. A meta é examinar as etapas marcadas como não dobradas. Em seguida, você pode ver se fazer alterações na consulta geral faz com que essas transformações sejam dobradas completamente.

Neste exemplo, a única etapa que não pode ser dobrada é Manter linhas inferiores, o que é fácil de identificar por meio do indicador de etapa não dobrada. Essa etapa também é a última etapa da consulta.

O objetivo agora é examinar essa etapa e entender o que está sendo dobrado para a fonte de dados e o que não pode ser dobrado.

2. Selecionar a etapa de consulta para examinar seu plano de consulta

Você identificou a etapa Linhas inferiores mantidas como uma etapa de interesse, pois ela não é dobrada para a fonte de dados. Clique com o botão direito do mouse na etapa e selecione a opção Exibir plano de consulta. Essa ação exibe um novo diálogo que contém um diagrama do plano de consulta da etapa selecionada.

O Power Query tenta otimizar sua consulta aproveitando a avaliação lenta e a dobragem de consultas, conforme mencionado nas Noções básicas de dobragem de consultas. Esse plano de consulta representa a tradução otimizada da consulta M para a consulta nativa enviada para a fonte de dados. Ele também inclui todas as transformações executadas pelo Mecanismo do Power Query. A ordem na qual os nós são exibidos segue a ordem da consulta a partir da última etapa ou saída da consulta, que é representada no extremo esquerdo do diagrama e, nesse caso, é o nó Table.LastN que representa a etapa Linhas inferiores mantidas.

Na parte inferior do diálogo, há uma barra com ícones que ajudam você a ampliar ou reduzir o modo de exibição do plano de consulta e outros botões para ajudá-lo a gerenciar a exibição. Para a imagem anterior, a opção Ajustar para exibir dessa barra foi usada para apreciar melhor os nós.

Observação

O plano de consulta representa o plano otimizado. Quando o mecanismo está avaliando uma consulta, ele tenta dobrar todos os operadores em uma fonte de dados. Em alguns casos, ele pode até mesmo fazer uma reordenação interna das etapas para maximizar a dobragem. Com isso em mente, os nós/operadores deixados neste plano de consulta otimizado normalmente contêm a consulta de fonte de dados "dobrada" e todos os operadores que não puderam ser dobrados e são avaliados localmente.

Identificar nós dobrados de outros nós

Você pode identificar os nós neste diagrama como dois grupos:

• Nós dobrados: esse nó pode ser Value.NativeQuery ou nós de "fonte de dados", como Sql.Database. Eles também podem ser identificados com o rótulo remoto abaixo do nome de função.
• Nós não dobrados: outros operadores de tabela, como Table.SelectRows, Table.SelectColumns e outras funções que não puderam ser dobradas. Eles também podem ser identificados com os rótulos Verificação completa e Streaming.

A imagem a seguir mostra os nós dobrados no retângulo vermelho. O restante dos nós não pôde ser dobrado para a fonte de dados. Você precisará examinar o restante dos nós, pois a meta é tentar fazer com que esses nós sejam dobrados para a fonte de dados.

Você pode selecionar Exibir detalhes na parte inferior de alguns nós para exibir informações estendidas. Por exemplo, os detalhes do nó Value.NativeQuery mostram a consulta nativa (no SQL) que será enviada à fonte de dados.

A consulta mostrada aqui pode não ser exatamente a mesma consulta enviada à fonte de dados, mas é uma boa aproximação. Nesse caso, ele informa exatamente quais colunas serão consultadas da tabela SalesOrderHeader e, em seguida, como ela filtrará essa tabela usando o campo TotalDue para obter apenas as linhas em que o valor desse campo é maior que 1000. O nó ao lado, Table.LastN, é calculado localmente pelo mecanismo de Power Query, pois não pode ser dobrado.

Observação

Os operadores podem não corresponder exatamente às funções usadas no script da consulta.

Examine os nós não dobrados e considere ações para fazer sua dobra de transformação

Agora você determinou quais nós não puderam ser dobrados e serão avaliados localmente. Esse caso só tem o nó Table.LastN, mas em outros cenários poderia ter muito mais.

O objetivo é aplicar alterações à sua consulta para que a etapa possa ser dobrada. Algumas alterações que você pode implementar podem variar desde a reorganização de suas etapas até a aplicação de uma lógica alternativa à consulta mais explícita à fonte de dados. Isso não significa que todas as consultas e todas as operações sejam dobráveis aplicando algumas alterações. Mas é uma boa prática determinar por tentativa e erro se sua consulta pode ser dobrada novamente.

Como a fonte de dados é um banco de dados SQL Server, se a meta for recuperar os últimos cinco pedidos da tabela, uma boa alternativa seria aproveitar as cláusulas TOP e ORDER BY no SQL. Como não há nenhuma cláusula BOTTOM no SQL, a transformação Table.LastN no PowerQuery não pode ser convertida em SQL. Você pode remover a etapa Table.LastN e substituí-la por:

• Uma etapa de classificação decrescente pela coluna SalesOrderID na tabela, uma vez que esta coluna determina qual ordem irá primeiro e qual foi inserida por último.
• Selecione as cinco primeiras linhas desde que a tabela foi classificada. Essa transformação realiza o mesmo que uma Linha inferior mantida (Table.LastN).

Essa alternativa é equivalente à consulta original. Embora essa alternativa, em teoria, pareça boa, você precisa fazer as alterações para ver se isso fará com que esse nó volte totalmente para a fonte de dados.

3. Implementar as alterações na consulta

Implemente a alternativa discutida na seção anterior:

1. Feche o diálogo do plano de consulta e volte para o Editor do Power Query.

2. Remova a etapa Linhas inferiores mantidas.

3. Classifique a coluna SalesOrderID em ordem descendente.

   

4. Selecione o ícone de tabela no canto superior esquerdo da exibição de visualização de dados e selecione a opção Manter linhas superiores. Na caixa de diálogo, passe o número cinco como o argumento e pressione OK.

   

Depois de implementar as alterações, verifique os indicadores de dobragem de consultas novamente e veja se oferecem um indicador dobrado.

Agora é hora de examinar o plano de consulta da última etapa, que agora é Manter linhas superiores. Agora só há nós dobrados. Selecione Exibir detalhes em Value.NativeQuery para verificar qual consulta está sendo enviada ao banco de dados.

Embora este artigo sugira qual alternativa aplicar, o objetivo principal é que você saiba como usar o plano de consulta para investigar a dobragem de consultas. Este artigo também fornece visibilidade do que está sendo enviado para a fonte de dados e quais transformações serão feitas localmente.

Você pode ajustar seu código para ver o impacto que ele tem em sua consulta. Usando os indicadores de dobragem de consultas, você também terá uma ideia melhor de quais etapas estão impedindo que sua consulta dobre.