Power Query Editor slow for all steps - from loading data to even add an index column

[Winniethewinner]

Hi everyone, 

My data connection to PBI is super slow, so I would like some advise / suggestions. 

We get data from ODBC driver: 

And then define the DSN, and have a SQL statement: 

The SQL query generates about ~2 million rows. When I run the query in SQL assistant it takes ~4 mins to run. But when I copy paste the query to this PBI SQL statement, it takes several hours to load. 

Also, after the data is loaded, and if I perform extra step - for example simply add an index column, it takes another several hours to perform. This load takes very long to perform.  

Can someone help me what could be the underlying reasons? Any advise or suggestions is much appreciated. Thank you.   

[edhans]

What is your data source and why are you using an ODBC driver vs the driver for your server? For example, use the SQL Server connector if it is that, vs an ODBC driver for SQL Server. 

We need more info.

[Winniethewinner]

Thanks for your reply @edhans . 

The data source is Teradata. Actually I'm not very familiar with the database connection. The way I connected is how I was instructed. If there could be other way (more efficient way) to connect, can you please instruct me how, and what type of information is needed? For example if selecting SQL Server, where could I get the Server name? Apologies if it is a dumb question. 

[edhans · Accepted solution]

Hrm... yeah. that might be the way. When searching for info on Power BI and Terradata, there are several 3rd party tools that make it fast, which suggests it is slow as a rule, so I think the issue here is finding a good connector. I have no experience directly with Terradata databases, so I don't want to post links you can google yourself. You could repost a new question and put slow Terredata in the description and someone with that experience is more likely to see it.

[Winniethewinner]

Thanks @edhans. That sounds an idea - to post a new Teradata-related question. 

I also contacted our Teradata vendor see if they could help. 

Wil accept your solution.