Direct query is excessively slow

[Anonymous]

Hi Mentors;

I am building a report and this needs immediate data; Hence I am using direct query over a table valued function as there are lots of logics need to be implemented. Now in the query, I have 3 slicers and 4 visuals and the reportis excessively slow. SOmehow this works in desktop, but in service or external application (where the report is embedded), its simply failing.

Consider that I need to have employee details; so I am using "SEELCT * FROM tvfEmpDtl()"

Is there a way that I can supply the parameter to the TVF life "SELEC * FROM tvfEmpDtl(2)" so that I can get the data of employeeID 2?

Please consider that I need to pass teh parameter 2 from another portal. I have tried to play with Power Query but still in vain.

Any helpp is much appriciated.

Regards,

Arup

[v-stephen-msft]

Hi @Anonymous ,

Please refer to Change the Source of Power BI Datasets Dynamically Using Power Query Parameters

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi Stephen,

Thanks for your reply. I have already gone through the link. However, this is not working in DirectQuery while published to Service. This is the main issue.

However, I am found a workaround. Initially I was using the "Table Valued Function" and THIS WAS THE CAUSE OF SLOWNWSS. Now I have inserted the data in a physical table and so far the performance is satisfactory.

So, my learning is not to use TVF unless this is the only solution.

Regards,

Arup

[v-stephen-msft]

Hi @Anonymous ,

You may want to konw about optimizing your data source, please kindly refer to

Optimization guide for Power BI

DirectQuery model guidance in Power BI Desktop

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Stephen,

Thanks for your reply. However, already I have considered all these and still in dark. Since my report is based on a single table value function, is it the problem? I have not found anywhere that using TVF can degrade th performance, but this is happening here.

Can we send a parameter to table value function while using direct query?

Regards,

Arup

[Anonymous]

Hello Mentors,

Is there any solution for this or thisis NOT possible so far in PBI?

Regards,

Arup

[amitchandak]

@Anonymous , I have not tested this, but see if dynamic m query parameters, can help

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

Another suggestion, think about mix mode, where few tables can be import mode.

[Anonymous]

Hi Amit,

Thanks for your reply. However, I am able to make the Direct query dynamic with parameters as below:

let
Source = Sql.Database("testdb.database.windows.net", "testDB", [Query="SELECT * FROM [risk].[fnCustomar_details]( " &Number.ToText(CustID) & ")"])
in
Source

This is working fine in PBI Desktop. However, after publishing, I am not able to see the Parameter section from Dataset->Settings->Parameters. Hence just confused how to send the customer ID as a parameter to the service.

Your help is pretty much appreciated.

Thanks,

Arup