Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
codyraptor
Resolver I
Resolver I

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?

 

9 REPLIES 9
ricardocamargos
Continued Contributor
Continued Contributor

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

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
Not applicable

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.

I have the query set to 120 minutes.  Definitely not an issue with timeout.  I'm also receiving a schema error.

Anonymous
Not applicable

My next question would be what happens between the Source statement and when the query finalises.  What operations are happening on the table after you get it from the source?

 

Also are there other tables that will rely on this source, for either merging or as its own source?

I am not doing any manipulation of the data after the source statement.  I'm not changing any of the headers, formats, conditional columns, etc...  I'm literally just pulling the single sql statement in and loading it.

 

Other tables eventually could be used from this table using the referencing function in order to create dimensions, but I am not currently doing that.  I'm just loading the 1 single table right now.

 

I should mention....Our warehouse is an Oracle warehouse.  We are currently on 11g I believe is what our DB guy told me.  Could any of our issues be caused from the version of Oracle we're using?   I've also noticed our 'auto refresh' feature for my other models fail almost daily due to timeout issues.

Anonymous
Not applicable

I've only had to connect Power BI to oracle once, it was awful. It worked though, but i'd rather eat my own head than try that again.

ha...nice.

Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.