Takes 15 minutes to pull data from sybase IQ

[Anonymous]

Hi,

When I am running a query to pull data from SAP Sybase IQ using TOAD it only takes 24 seconds to load 293522 records where as in Power BI and Power Query in excel takes more than 15 mintues and really frustrated as to what casing the delay.

I just tested an old query in Power BI and it is pretty fast but not sure what’s happening with this one.

Any help is greatly appreciated.

[Greg_Deckler]

@Anonymous Are you doing the same transformations in each? Can you post your Advanced Editor code, minus any source info so that we can see the transformations?

[Anonymous]

Hi @Greg_Deckler ,

I am not doing any transformations in power query editor. Everything done at the Sybase query level in the data source.

[Greg_Deckler]

@Anonymous - Well, ODBC, there's problem #1! 🙂 You have like 18 steps in that query but no transformation is going on?

[Anonymous]

@Greg_Deckler

I have no issues with my query performance as it is taking only 26 seconds to pull the data using TOAD or Interactive SLQ editor .

But Power BI takes 15-20 mins so slow.

[Greg_Deckler]

@Anonymous - It's hard to say, what are the 18 steps doing? I take it TOAD is also using the same ODBC connection?

[Anonymous]

@Greg_Deckler 

Everything is the same data source. I just copied the TOAD SQL code into Power BI source and ran the query.

I wish I can upload a video TOAD vs Power BI data loading but due to organization privacy rules I can’t do that.

Thanks,

Deevs

[Greg_Deckler]

@Anonymous - Ah, so all those lines of code are really just the complex SQL statement that you pasted into the Advanced section when you created the query. So, here's my guess and I'll let @edhans chime in if he wants. Something in that SQL statement is preventing query folding from happening in Power BI but for whatever reason it is happening with TOAD. That's my guess. It would be interesting to see if you took that SQL statement, created a view from it in the database. Connected Power BI to that view and see what the data load times are. This is pure speculation on my part because I don't know what your SQL query is doing. But, if you created a view from it then you could point Power BI to that view, query folding would happen and it should run much faster.

[Anonymous]

@Greg_Deckler  

Never Thought about creating a View. I will Try that and see but really disappointed as I am this close to get a premium instance and now on the fence.

By the way I ran the same query in Tableau . it didn’t even take 10 seconds and really impressed.

Also I was told by Sys admin that when using TOAD and Tableau there is only one connection to database where as with power BI three connections.

[Greg_Deckler]

@Anonymous Honestly, a little surprised myself about the performance difference. Power Query is generally quite good performance-wise. It's difficult to say what is going on. Have you enabled the Performance Analyzer in Power Query to see what is going on "under the hood"? Definitely @ me in any response about how the view performs in comparison. Genuinely interested.

[Anonymous]

@Greg_Deckler  

I just ran the same query without the case statement below and it loaded data in less than 10 seconds. But I Really don’t want to do these transformations in Power BI query editor.

Power BI team needs to figure out a way to handle those query transformations efficiently in the data source.

My case Stament :

[Greg_Deckler]

@Anonymous - I think this is fantastic feedback. If you wouldn't mind, write this up and post it to the Issues forum. Give them the detail. I 100% guarantee they pay attention to this stuff and take action. Apparently that CASE statement is preventing query folding and they need to know about it. Nice job troubleshooting!!

[Anonymous]

@Greg_Deckler 

Hey Craig,

I am super confused as I have run it again and got the  same issue  without even the case statement.

I just realised that I updated my power BI to the latest version yesterday?

I got some pretty complex queries in the old reports and working fine.

Anything I created from yesterday taking ages to load. I have a feeling it has something do with the new update. May be bug that they need to fix. any thoughts ?

Thanks

Deevs

[Anonymous · Accepted solution]

@Greg_Deckler 

Hey Craig,

I figured what’s causing the delay.

It was nothing to do with Case statement or having 100 lines or complex sub queries or upgrading to the latest version.

Actually my query has got over 100 lines with 10 sub queries the initial screenshot I sent you had been truncated to take the screenshot.

It was due to the below query block. Witout declaring it took 40 seconds to load the data :).

It was due to the below query block.

[amitchandak]

@Anonymous , is the time for complete date load in toad or the first few rows

also refer: https://www.thebiccountant.com/speedperformance-aspects/

https://www.thebiccountant.com/speedperformance-aspects/