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
croberts21
Responsive Resident
Responsive Resident

How to increase speed of PBI in ODBC SQL statement? Use ORDER BY or not?

I have Free PBI Desktop v2.105 (64 bit free version May 2022) on 64-bit Windows Server 2019 Datacenter v1809.

We get data from a Postgresql database to use in PBI, using Import mode, so the data will be stored in the PBIX file. One table I use a lot is called Jobcost, and it is pushing 10 million records. We use an ODBC data source with an SQL statement to connect to this Postgresql database, one ODBC connection per table. 1 other table would be connected to Jobcost in another ODBC SQL statement, and from there I would pick data to use in my table viz. The table relations would be via PBI relations tab called the "Model" tab.

My question is, how do I make PBI run faster? Should I sort the records in the ODBC SQL statement or not? I ask this becasue sometimes it can take 15-20 minutes for a viz to update when data in that viz (normally a table viz) uses some part of the Jobcost data. I already limit data in Jobcost to data after Jan 1, 2021. This is a new PBI report and the users are unlikely to need data older than that. So what I'm actually dealing with is 2-3 million records with that date filter.

Given that the number of fields in my SQL SELECT is the same, which is faster?

SELECT field1, field2, field3 FROM jobcost

SELECT field1, field2, field3 FROM jobcost ORDER BY field1, field2

Thank you!

p.s. I have no idea which fields are indexed as this is a black box from a vendor, we cannot add indexes ourselves. 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

1. Try not to do any transforms or filters. Since you have no control over the indices your best bet is a plain spool. The maximum you should do is a column selection but even that will slow you down. Don't waste your time ordering data, let the Vertipaq engine do that in memory.

2. Don't use ODBC. Use the native SQL connector.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

1. Try not to do any transforms or filters. Since you have no control over the indices your best bet is a plain spool. The maximum you should do is a column selection but even that will slow you down. Don't waste your time ordering data, let the Vertipaq engine do that in memory.

2. Don't use ODBC. Use the native SQL connector.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.