Forum Discussion

codyraptor's avatar
codyraptor
Resolver I
8 years ago

Direct Query...Performance Issue

Hey everyone,

 

I have a large dataset and I'm trying to decide between 'import' and 'direct query'.  Import takes forever to refresh this model.  It sometimes works and sometimes can timeout, so it isn't stable.  The direct query method seems like it would be a better fit, but I'm running into an issue.  The 'editor' view pulls my sql results extremely fast.  As soon as I hit 'close and apply', then Power BI 'evaluates' for multiple hours until it finally crashes.  

 

My model consists of 4yrs worth of data that is aggregated in sql.  I'm calculating a sum of price, sum of revenue, and count of contracts in my aggregates against many dimensions.  The sql is basic..and the editor works great.  Also, import works..just takes forever.  

 

Any thoughts?

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi codyraptor

    As far as I understand,

    In direct query mode you can't see editor window, you see this only in import mode.

    The editor view only pull the first n number of rows it doesn't pull the entire data set after evaluating your query. When you hit close and apply now it's trying to pull the entire data set which fails to pull may be due to many number of rows and finally it's getting crashed.

  • ricardocamargos's avatar
    ricardocamargos
    Continued Contributor
    8 years ago

    hi codyraptor,

     

    Have you tried to run this query on the database ? Is it ok ?

     

    Are you writing your SQL or just selecting the tables ?

     

    Thanks,

     

    Ricardo

    • codyraptor's avatar
      codyraptor
      Resolver I
      8 years ago

      I have ran this query against the DB and it's fine.  I can also run the same exact query in 'import' mode...and it works fine.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        The default timeout on each query is 10 minutes. I have a couple of queries that need a bit longer than that.  You are able to choose a longer timeout time if required.  When you are in your Query Editor, find the Source line of the table.  Click on the Cog icon.  In the window that appears, open up the 'Advanced Options' and the first box is "Command Timeout".

         

        I generally work with Import where possible.  It seems to give a better level of user performance and I'm able to manage server strain a little easier.  Fortunately i'm working with data that only needs to be up to date as of close of business, so i don't have that direct query need.