Forum Discussion

LimitFounder's avatar
LimitFounder
New Member
2 months ago
Solved

DirectQueryCapabilities.From() takes too much time

I see in PowerBI Desktop, that the DirectQueryCapabilities.From() function takes around 11s from a 14s query, and I would like to know more about this. Loading the data from Redshift itself is under 500ms. The only documentation I've found is the following: https://learn.microsoft.com/en-us/powerquery-m/directquerycapabilities-from  , which doesn't really helps.

Have you ever faced similar issue? 

Need Help
  • Shai_Karmani's avatar
    Shai_Karmani
    2 months ago

    Yeah, seen this. That step isn't loading data, it's the ODBC capabilities handshake. The connector fires a bunch of metadata calls (SQLGetInfo, SQLGetTypeInfo, catalog queries) to figure out what SQL it can fold. On Redshift those hit pg_catalog on the leader node, and it's dozens of small sequential round trips, so if there's any latency between you and the cluster (VPN, cross region) it adds up fast. Desktop also spins up fresh containers per evaluation so you pay it again and again while authoring.

     

    Quick checks: test from a machine in the same region as the cluster, if the 11s disappears it's pure latency.

    Update the Redshift ODBC driver, older ones are chattier. And in Options > Data Load turn off background preview and lower simultaneous evaluations so fewer containers do the handshake.

     

    If that was helpful, please give a thumbs up and mark it as the accepted solution.

     

     

     

    Thanks, Shai Karmani

    Let's connect in LinkedIn

3 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    2 months ago

    Yeah, seen this. That step isn't loading data, it's the ODBC capabilities handshake. The connector fires a bunch of metadata calls (SQLGetInfo, SQLGetTypeInfo, catalog queries) to figure out what SQL it can fold. On Redshift those hit pg_catalog on the leader node, and it's dozens of small sequential round trips, so if there's any latency between you and the cluster (VPN, cross region) it adds up fast. Desktop also spins up fresh containers per evaluation so you pay it again and again while authoring.

     

    Quick checks: test from a machine in the same region as the cluster, if the 11s disappears it's pure latency.

    Update the Redshift ODBC driver, older ones are chattier. And in Options > Data Load turn off background preview and lower simultaneous evaluations so fewer containers do the handshake.

     

    If that was helpful, please give a thumbs up and mark it as the accepted solution.

     

     

     

    Thanks, Shai Karmani

    Let's connect in LinkedIn

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    2 months ago

    Hi LimitFounder ,

    Thank you for reaching out Fabric Community Forum.

    According to Microsoft documentation, DirectQueryCapabilities.From() is a Power Query M function that returns the DirectQuery capabilities for a connector. DirectQueryCapabilities.From - PowerQuery M | Microsoft Learn

    You can review the connector documentation to understand the supported DirectQuery capabilities and limitations for the data source connector. List of all Power Query connectors - Power Query | Microsoft Learn

    Power Query Diagnostics can be used to collect detailed information about query evaluation and performance in Power Query. Query diagnostics - Power Query | Microsoft Learn

    Thank you.

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      2 months ago

      Hi LimitFounder ,

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you