Forum Discussion

Helper IV

9 years ago

Loading Large Tables

I'm connecting successfully to our postgresql database. I then select the tables I want and attempt to Load them. Power BI fails with the error message about running out of memory - one of the tables...

Solution Supplier

9 years ago

I think the best practice for loading large datasets is using the Direct Query method instead of Import.

Also, are you running on a 32-bit or 64-bit machine? And do you have atleast 8GB of RAM?

I would also recommend looking at your current Connection Timeout configuration.

Cheers,

Alan

Helper IV

9 years ago

I'm running Windows 10 64-bit, with 8GB of RAM.

~~I'm using the Direct Query method, not Import.~~ My mistake - we are using Import, we have no choice - Direct Query for postgresql is not supported.

I'm unable to find the Connection Timeout configuration - where is it?

tringuyenminh92
Memorable Member
9 years ago
Hi Budfudder,

Does your postgre database store in this computer? Is this error from database side? With DirectQuery, each chart just loads maximum 1 mil rows when you interactive( https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery) so I don't think it's problem from PBI.
- Budfudder
  Helper IV
  9 years ago
  Unfortunately (from what I can see) postgresql isn't one of the databases supported by DirectQuery - so I can ONLY import. Does importing actually create a copy of the whole table locally?
  
  I've had similar problems connecting to our CRM database (not timeouts or failures, but just HUGE load times) - again, it's not supported by DirectQuery.
  - v-caliao-msft
    Microsoft Employee
    9 years ago
    Hi Budfudder,
    
    To improve query performance, here are two tips for you.
    
    Tall, narrow tables are faster. Reduce the unused columns in order to improve performance.
    
    Integers are faster than strings. Strings are stored in a hash table, they are effectively referenced twice, once for the hash value and once to fetch the string associated with that value.
    
    Reference
    http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques
    http://promx.net/en/2016/09/optimizing-power-bi-query-performance-with-crm-2016-online-odata-v4-service/
    
    Regards,
    
    Charlie Liao

Related content