cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
girinpanda Helper III
Helper III

Performance of Excel vs Database Data Source

I'm currently having issues with my PBIX performance (It always crashes). In my current setup, I'm using 4 Excel files with multiple sheets as data sources. But I didn't experience these crashes before when I'm connecting to around 3 tables from a Database (SQL). And the number of rows is much larger in the database compared to what I have in Excel. 

 

 

Is there a whitepaper or reference relating to Power BI performance when considering what data source to use (particularly Excel and database)? Or do you have any idea if a type of data source can be a factor to performance?

 

FYR, here's my system setup:

16GB Ram

64 bit 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Performance of Excel vs Database Data Source

HI @girinpanda,


AFAIK, If you only get data from excel and merge them, it should works well without any performance issues.

 

Have you do any advanced operation on these query?(e.g. invoke custom function, refer any query table in calculate, refer previous query steps)

 

These operations may cause the memory issue.(power query will spend memory resource multiple times to handle each row calculation)

 

If that is a case you can consider to use list.buffer or table.buffer to cache reference source to memory to reduce cost of resource.

How and When to use List & Table Buffer?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

2 REPLIES 2
Community Support
Community Support

Re: Performance of Excel vs Database Data Source

HI @girinpanda,


AFAIK, If you only get data from excel and merge them, it should works well without any performance issues.

 

Have you do any advanced operation on these query?(e.g. invoke custom function, refer any query table in calculate, refer previous query steps)

 

These operations may cause the memory issue.(power query will spend memory resource multiple times to handle each row calculation)

 

If that is a case you can consider to use list.buffer or table.buffer to cache reference source to memory to reduce cost of resource.

How and When to use List & Table Buffer?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

girinpanda Helper III
Helper III

Re: Performance of Excel vs Database Data Source

Right, I have operations which refers to previous query steps. 

 

Thanks @v-shex-msft! I'll check out table.buffer.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors