Esempi di riduzione delle query

Questo articolo fornisce alcuni scenari di esempio per ognuno dei tre possibili risultati per la riduzione delle query. Include anche alcuni suggerimenti su come sfruttare al meglio il meccanismo di riduzione delle query e l'effetto che può avere nelle query.

Scenario

Si immagini uno scenario in cui, usando il database Wide World Importers per il database SQL di Azure Synapse Analytics, si deve creare una query in Power Query che si connette alla fact_Sale tabella e recupera le ultime 10 vendite con solo i campi seguenti:

• Chiave di vendita
• Customer Key
• Chiave data fattura
• Descrizione
• Quantità

Questo articolo illustra tre modi per ottenere lo stesso output con livelli diversi di riduzione delle query:

• Nessuna riduzione delle query
• Riduzione parziale delle query
• Riduzione completa delle query

Nessun esempio di riduzione delle query

Dopo la connessione al database e la navigazione alla fact_Sale tabella, selezionare la trasformazione Mantieni le righe inferiori trovate all'interno del gruppo Riduci righe della scheda Home .

Dopo aver selezionato questa trasformazione, viene visualizzata una nuova finestra di dialogo. In questa nuova finestra di dialogo è possibile immettere il numero di righe che si desidera mantenere. In questo caso, immettere il valore 10 e quindi selezionare OK.

Selezionare quindi la trasformazione Scegli colonne trovata all'interno del gruppo Gestisci colonne della scheda Home . È quindi possibile selezionare le colonne che si desidera mantenere dalla tabella e rimuovere il resto.

Infine, all'interno della finestra di dialogo Scegli colonne selezionare le Sale Keycolonne , Customer Key, DescriptionInvoice Date Key, e Quantity e quindi selezionare OK.

L'esempio di codice seguente è lo script M completo per la query creata:

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""

Nessuna riduzione della query: informazioni sulla valutazione della query

In Passaggi applicati nell'editor di Power Query si noterà che gli indicatori di riduzione delle query per Le righe inferiori mantenute e Scegliere le colonne sono contrassegnati come passaggi che verranno valutati all'esterno dell'origine dati o, in altre parole, dal motore di Power Query.

È possibile fare clic con il pulsante destro del mouse sull'ultimo passaggio della query, scegliere le colonne denominate e selezionare l'opzione visualizza piano di query. L'obiettivo del piano di query è fornire una visualizzazione dettagliata della modalità di esecuzione della query. Per altre informazioni su questa funzionalità, vedere Piano di query.

Ogni casella nell'immagine precedente è denominata nodo. Un nodo rappresenta la suddivisione dell'operazione per soddisfare questa query. I nodi che rappresentano origini dati, ad esempio SQL Server nell'esempio precedente e il Value.NativeQuery nodo, rappresentano la parte della query scaricata nell'origine dati. Il resto dei nodi, in questo caso Table.LastN e Table.SelectColumns evidenziato nel rettangolo nell'immagine precedente, viene valutato dal motore di Power Query. Questi due nodi rappresentano le due trasformazioni aggiunte, Mantieni le righe inferiori e Scegli colonne. Il resto dei nodi rappresenta le operazioni eseguite a livello di origine dati.

Per visualizzare la richiesta esatta inviata all'origine dati, selezionare Visualizza dettagli nel Value.NativeQuery nodo.

Questa richiesta di origine dati si trova nella lingua nativa dell'origine dati. In questo caso, tale linguaggio è SQL e questa istruzione rappresenta una richiesta per tutte le righe e i campi della fact_Sale tabella.

La consulenza di questa richiesta di origine dati consente di comprendere meglio la storia che il piano di query tenta di trasmettere:

• Sql.Database: questo nodo rappresenta l'accesso all'origine dati. Connessione al database e invia richieste di metadati per comprenderne le funzionalità.
• Value.NativeQuery: rappresenta la richiesta generata da Power Query per soddisfare la query. Power Query invia le richieste di dati in un'istruzione SQL nativa all'origine dati. In questo caso, che rappresenta tutti i record e i campi (colonne) della fact_Sale tabella. Per questo scenario, questo caso è indesiderato, poiché la tabella contiene milioni di righe e l'interesse si trova solo negli ultimi 10.
• Table.LastN: dopo che Power Query riceve tutti i record dalla fact_Sale tabella, usa il motore di Power Query per filtrare la tabella e mantenere solo le ultime 10 righe.
• Table.SelectColumns: Power Query userà l'output del Table.LastN nodo e applicherà una nuova trasformazione denominata Table.SelectColumns, che seleziona le colonne specifiche da mantenere da una tabella.

Per la valutazione, questa query doveva scaricare tutte le righe e i campi dalla fact_Sale tabella. Questa query ha richiesto una media di 6 minuti e 1 secondo per l'elaborazione in un'istanza standard dei flussi di dati di Power BI (che rappresenta la valutazione e il caricamento dei dati nei flussi di dati).

Esempio di riduzione parziale delle query

Dopo la connessione al database e la navigazione alla fact_Sale tabella, iniziare selezionando le colonne che si desidera mantenere dalla tabella. Selezionare la trasformazione Scegli colonne trovata all'interno del gruppo Gestisci colonne dalla scheda Home . Questa trasformazione consente di selezionare in modo esplicito le colonne che si desidera mantenere dalla tabella e rimuovere il resto.

Nella finestra di dialogo Scegli colonne selezionare le Sale Keycolonne , Customer KeyInvoice Date Key, , Descriptione Quantity e quindi selezionare OK.

Si crea ora la logica per ordinare la tabella in modo che disponga dell'ultima vendita nella parte inferiore della tabella. Selezionare la Sale Key colonna, ovvero la chiave primaria e la sequenza incrementale o l'indice della tabella. Ordinare la tabella usando solo questo campo in ordine crescente dal menu di scelta rapida per la colonna.

Selezionare quindi il menu contestuale della tabella e scegliere la trasformazione Mantieni le righe inferiori.

In Mantieni le righe inferiori immettere il valore 10 e quindi selezionare OK.

L'esempio di codice seguente è lo script M completo per la query creata:

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"

Esempio di riduzione parziale della query: Informazioni sulla valutazione della query

Controllando il riquadro dei passaggi applicati, si noterà che gli indicatori di riduzione della query mostrano che l'ultima trasformazione aggiunta, Kept bottom rows, è contrassegnata come un passaggio che verrà valutato all'esterno dell'origine dati o, in altre parole, dal motore di Power Query.

È possibile fare clic con il pulsante destro del mouse sull'ultimo passaggio della query, quello denominato Kept bottom rowse selezionare l'opzione Piano di query per comprendere meglio la modalità di valutazione della query.

Ogni casella nell'immagine precedente è denominata nodo. Un nodo rappresenta ogni processo che deve verificarsi (da sinistra a destra) affinché la query venga valutata. Alcuni di questi nodi possono essere valutati nell'origine dati, mentre altri, ad esempio il nodo per Table.LastN, rappresentato dal passaggio Mantieni le righe inferiori, vengono valutati usando il motore di Power Query.

Per visualizzare la richiesta esatta inviata all'origine dati, selezionare Visualizza dettagli nel Value.NativeQuery nodo.

Questa richiesta si trova nella lingua nativa dell'origine dati. In questo caso, tale linguaggio è SQL e questa istruzione rappresenta una richiesta per tutte le righe, con solo i campi richiesti dalla fact_Sale tabella ordinata dal Sale Key campo.

La consulenza di questa richiesta di origine dati consente di comprendere meglio la storia che il piano di query completo tenta di trasmettere. L'ordine dei nodi è un processo sequenziale che inizia richiedendo i dati dall'origine dati:

• Sql.Database: Connessione al database e invia richieste di metadati per comprenderne le funzionalità.
• Value.NativeQuery: rappresenta la richiesta generata da Power Query per soddisfare la query. Power Query invia le richieste di dati in un'istruzione SQL nativa all'origine dati. In questo caso, che rappresenta tutti i record, con solo i campi richiesti dalla fact_Sale tabella nel database ordinati in ordine crescente in base al Sales Key campo.
• Table.LastN: dopo che Power Query riceve tutti i record dalla fact_Sale tabella, usa il motore di Power Query per filtrare la tabella e mantenere solo le ultime 10 righe.

Per la valutazione, questa query doveva scaricare tutte le righe e solo i campi obbligatori dalla fact_Sale tabella. L'elaborazione in un'istanza standard dei flussi di dati di Power BI è durata media di 3 minuti e 4 secondi, che determina la valutazione e il caricamento dei dati nei flussi di dati.

Esempio di riduzione della query completa

Dopo la connessione al database e la navigazione alla fact_Sale tabella, iniziare selezionando le colonne che si desidera mantenere dalla tabella. Selezionare la trasformazione Scegli colonne trovata all'interno del gruppo Gestisci colonne dalla scheda Home . Questa trasformazione consente di selezionare in modo esplicito le colonne che si desidera mantenere dalla tabella e rimuovere il resto.

In Scegli colonne selezionare le Sale Keycolonne , Invoice Date KeyCustomer Key, Description, e Quantity e quindi selezionare OK.

Si crea ora la logica che ordina la tabella in modo che disponga dell'ultima vendita nella parte superiore della tabella. Selezionare la Sale Key colonna, ovvero la chiave primaria e la sequenza incrementale o l'indice della tabella. Ordinare la tabella solo usando questo campo in ordine decrescente dal menu di scelta rapida per la colonna.

Selezionare quindi il menu contestuale della tabella e scegliere la trasformazione Mantieni le prime righe .

In Mantieni le prime righe immettere il valore 10 e quindi selezionare OK.

L'esempio di codice seguente è lo script M completo per la query creata:

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"

Esempio di riduzione della query completa: Informazioni sulla valutazione della query

Quando si controlla il riquadro passaggi applicati, si noterà che gli indicatori di riduzione delle query mostrano che le trasformazioni aggiunte, Scegli colonne, Righe ordinate e Righe mantenute vengono contrassegnate come passaggi che verranno valutati nell'origine dati.

È possibile fare clic con il pulsante destro del mouse sull'ultimo passaggio della query, quello denominato Righe principali mantenute e selezionare l'opzione che legge piano di query.

Questa richiesta si trova nella lingua nativa dell'origine dati. In questo caso, tale linguaggio è SQL e questa istruzione rappresenta una richiesta per tutte le righe e i campi della fact_Sale tabella.

La consulenza di questa query sull'origine dati consente di comprendere meglio la storia che il piano di query completo tenta di trasmettere:

• Sql.Database: Connessione al database e invia richieste di metadati per comprenderne le funzionalità.
• Value.NativeQuery: rappresenta la richiesta generata da Power Query per soddisfare la query. Power Query invia le richieste di dati in un'istruzione SQL nativa all'origine dati. In questo caso, che rappresenta una richiesta solo per i primi 10 record della fact_Sale tabella, con solo i campi obbligatori dopo essere stati ordinati in ordine decrescente utilizzando il Sale Key campo .

Per la valutazione, questa query scarica solo 10 righe, con solo i campi richiesti dalla fact_Sale tabella. Questa query ha richiesto una media di 31 secondi per l'elaborazione in un'istanza standard dei flussi di dati di Power BI (che rappresenta la valutazione e il caricamento dei dati nei flussi di dati).

Confronto delle prestazioni

Per comprendere meglio l'impatto della riduzione delle query in queste query, è possibile aggiornare le query, registrare il tempo necessario per aggiornare completamente ogni query e confrontarle. Per semplicità, questo articolo fornisce i tempi medi di aggiornamento acquisiti usando il meccanico di aggiornamento dei flussi di dati di Power BI durante la connessione a un ambiente Azure Synapse Analytics dedicato con DW2000c come livello di servizio.

L'ora di aggiornamento per ogni query è la seguente:

Spesso una query che si riduce completamente all'origine dati supera le prestazioni di query simili che non vengono completamente ripiegate all'origine dati. Ci potrebbero essere molte ragioni per cui questo è il caso. Questi motivi variano dalla complessità delle trasformazioni eseguite dalla query, alle ottimizzazioni delle query implementate nell'origine dati, ad esempio indici e risorse di calcolo dedicate e di rete. Esistono comunque due processi chiave specifici che la riduzione delle query tenta di usare che riduce al minimo l'impatto che entrambi questi processi hanno con Power Query:

• Dati in transito
• Trasformazioni eseguite dal motore di Power Query

Le sezioni seguenti illustrano l'effetto che questi due processi hanno nelle query menzionate in precedenza.

Dati in transito

Quando una query viene eseguita, tenta di recuperare i dati dall'origine dati come uno dei primi passaggi. I dati recuperati dall'origine dati vengono definiti dal meccanismo di riduzione delle query. Questo meccanismo identifica i passaggi della query che possono essere scaricati nell'origine dati.

La tabella seguente elenca il numero di righe richieste dalla fact_Sale tabella del database. La tabella include anche una breve descrizione dell'istruzione SQL inviata per richiedere tali dati dall'origine dati.

Quando si richiedono dati da un'origine dati, l'origine dati deve calcolare i risultati per la richiesta e quindi inviare i dati al richiedente. Anche se le risorse di calcolo sono già state menzionate, le risorse di rete dello spostamento dei dati dall'origine dati a Power Query e quindi power Query può ricevere in modo efficace i dati e prepararli per le trasformazioni che verranno eseguite localmente può richiedere tempo a seconda delle dimensioni dei dati.

Per gli esempi presentati, Power Query doveva richiedere oltre 3,6 milioni di righe dall'origine dati per gli esempi di riduzione delle query e riduzione parziale delle query. Per l'esempio di riduzione della query completa, richiedeva solo 10 righe. Per i campi richiesti, l'esempio di riduzione della query non ha richiesto tutti i campi disponibili dalla tabella. Sia la riduzione parziale della query che gli esempi di riduzione completa della query hanno inviato solo una richiesta per esattamente i campi necessari.

Trasformazioni eseguite dal motore di Power Query

Questo articolo illustra come usare il piano di query per comprendere meglio il modo in cui la query può essere valutata. All'interno del piano di query è possibile visualizzare i nodi esatti delle operazioni di trasformazione che verranno eseguite dal motore di Power Query.

Nella tabella seguente vengono illustrati i nodi dei piani di query delle query precedenti che sarebbero stati valutati dal motore di Power Query.

Per gli esempi illustrati in questo articolo, l'esempio di riduzione completa delle query non richiede alcuna trasformazione all'interno del motore di Power Query perché la tabella di output richiesta proviene direttamente dall'origine dati. Al contrario, le altre due query richiedevano l'esecuzione di alcuni calcoli nel motore di Power Query. A causa della quantità di dati che devono essere elaborati da queste due query, il processo per questi esempi richiede più tempo rispetto all'esempio di riduzione completa della query.

Le trasformazioni possono essere raggruppate nelle categorie seguenti:

Considerazioni e suggerimenti

• Seguire le procedure consigliate per la creazione di una nuova query, come indicato in Procedure consigliate in Power Query.
• Usare gli indicatori di riduzione delle query per verificare quali passaggi impediscono la riduzione della query. Riordinarli se necessario per aumentare la riduzione.
• Usare il piano di query per determinare quali trasformazioni si verificano nel motore di Power Query per un passaggio specifico. Valutare la possibilità di modificare la query esistente riordinando i passaggi. Controllare quindi di nuovo il piano di query dell'ultimo passaggio della query e verificare se il piano di query è migliore di quello precedente. Ad esempio, il nuovo piano di query ha meno nodi rispetto a quello precedente e la maggior parte dei nodi sono nodi "Streaming" e non "analisi completa". Per le origini dati che supportano la riduzione, tutti i nodi del piano di query diversi dai Value.NativeQuery nodi di accesso all'origine dati rappresentano trasformazioni che non sono stati piegati.
• Se disponibile, è possibile usare l'opzione View Native Query (o View data source query) per assicurarsi che la query possa essere ripiegata nell'origine dati. Se questa opzione è disabilitata per il passaggio e si usa un'origine che normalmente la abilita, è stato creato un passaggio che arresta la riduzione delle query. Se si usa un'origine che non supporta questa opzione, è possibile basarsi sugli indicatori di riduzione delle query e sul piano di query.
• Usare gli strumenti di diagnostica delle query per comprendere meglio le richieste inviate all'origine dati quando sono disponibili funzionalità di riduzione delle query per il connettore.
• Quando si combinano dati originati dall'uso di più connettori, Power Query tenta di eseguire il push del maggior numero possibile di operazioni in entrambe le origini dati, rispettando al tempo stesso i livelli di privacy definiti per ogni origine dati.
• Leggere l'articolo sui livelli di privacy per proteggere le query dall'esecuzione in caso di errore di Firewall per la privacy dei dati.
• Usare altri strumenti per controllare la riduzione delle query dal punto di vista della richiesta ricevuta dall'origine dati. In base all'esempio riportato in questo articolo, è possibile usare Microsoft SQL Server Profiler per controllare le richieste inviate da Power Query e ricevute da Microsoft SQL Server.
• Se si aggiunge un nuovo passaggio a una query completamente piegata e il nuovo passaggio si riduce, Power Query potrebbe inviare una nuova richiesta all'origine dati anziché usare una versione memorizzata nella cache del risultato precedente. In pratica, questo processo può comportare operazioni apparentemente semplici su una piccola quantità di dati che richiedono più tempo per l'aggiornamento nell'anteprima del previsto. Questo aggiornamento più lungo è dovuto alla rieseguizione di query sull'origine dati anziché a una copia locale dei dati.