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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MCacc
Helper III
Helper III

Using Direct Query my pbix is extremely slow

Hello, 

 

I'm using Direct Query import mode to connect my dashboard to Oracle SQL DB. 

 

I don't have any problem when I launch queries directly on my DB in the sql Editor. The queries work well and super fast. 

 

The problem is that my pbix is extremely slow. I thought it was the high amount of rows uploaded (16 million), but things didn't change after I have uploaded 355 rows using a where condition from my DB. In both scenarios, my pbix is VERY VERY slow. I'm talking about 1-2 minutes to upload a slicer and 3-minutes to upload a line chart.

 

I don't think it's normal. 

 

I thought the issue could be because I used a concatenation of fields to create my table key to be used in my pbix in the direct query.  

 

In direct query I did something like this:

SELECT field_1  ||   '_'   ||  field_2   ||   '_' ||   field_3 as table_key
field_4,
field_5,
field_6,
FROM table

(I thought it could be the || operator, but it gives me the same problem when I use CONCAT)

 

I read somewhere that fields concatenation can be an issue when in direct query mode. 

 

Do you guys have any idea?

 

Thank you

 

 

9 REPLIES 9
v-diye-msft
Community Support
Community Support

Hi @MCacc 

 

1. The relational database source can be optimized in several ways, as described in the following bulleted list.

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-data-source-p... 

2. A DirectQuery model can be optimized in many ways, as described in the following bulleted list

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-model-design 

3. Optimize the report design:

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance#optimize-report-design... 

4. The benefits of Import and DirectQuery models can be combined into a single model by configuring the storage mode of the model tables. The table storage mode can be Import or DirectQuery, or both, known as Dual. When a model contains tables with different storage modes, it is known as a Composite model. For more information, see Use composite models in Power BI Desktop.

There are many functional and performance enhancements that can be achieved by converting a DirectQuery model to a Composite model. A Composite model can integrate more than one DirectQuery source, and it can also include aggregations. Aggregation tables can be added to DirectQuery tables to import a summarized representation of the table. They can achieve dramatic performance enhancements when visuals query higher-level aggregates. For more information, see Aggregations in Power BI Desktop.

 

Alternative, you can use the performance analyzer to exam report element performance https://docs.microsoft.com/en-us/power-bi/desktop-performance-analyzer Or the dax studio: https://community.powerbi.com/t5/Desktop/VAR-and-FILTERS-combination-to-make-an-efficient-column/m-p... 

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thank you very much for the great info, I will try to follow the suggestions in the links. 

 

 

Anonymous
Not applicable

What the MVPs fail to mention is that Microsofts connector for Oracle is quite frankly speaking, garbage. The default vaules, which are not configurable by the user when using this connector, are the main source of this pain.

If you don't mind importing your model, you can utilize extended connection string properties in combination with Ole DB to increase your througput. But this will sacrifice your ability to utilize DirectQuery.

Thank you, this is the problem, unfortunately... I don't see any solutions if not trying to improve dba performance 

 

Thank you very much for your help

Valentb
Frequent Visitor

I would suggest you use this method for large scale data models.

Clicky Clicky draggy droopy 


Create a table of the information that you need and aggregate it - for performance.

amitchandak
Super User
Super User

It would help if you could clarify a few things, as it seems you may be confusing a few concepts. There are 2 modes for power BI. Direct Query and Import Mode. If you are using direct query, the data is not loaded into power BI tables - all queries are sent to the source DB. If you are loading data into power BI, then it is import mode. 

 

You then say you are uploading slicers and line charts. What do you mean?  Are you saying it is taking a long time for those visuals to display in your report?

 

You say your report is very slow. What is your database design (assuming import mode)?  Have you built a star schema following dimensional modeling principles?  Or have you just imported your Oracle DB?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi  MattAllington,

 

I'm using direct query mode to upload the schema, not the data, of one table which contains all my info. In my DB, it can be considered as a giant fact table with no other tables to be put in relation with in my pbix.

Consider this, I didn't specifically make this table, but I know it's a left join between two tables. 

 

And yes, it's taking a long time for those visuals to display in my report.

 

 

 

If you are using direct query mode, then query performance is managed by the source DB, not Power BI. You will need to have a dba anaylse the queries being sent to the DB and optimise the DB to support the queries. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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