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

[croberts21]

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. 

[lbendlin · Accepted solution]

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.