Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Direct query is excessively slow

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Mentors,

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

    Regards,

    Arup

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
      Anonymous
      Not applicable
      5 years ago

      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