All DirectQuery requests are sent to the source database, so the time required for a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries).
the issue occurs when a visual has attempted to query too much data for the server to complete the result with the available resources. you may need to try filtering the visual to reduce the amount of data in the result currently.
please read through the article below to learn how to get best performance.
For now let me not discuss refresh schedule but focus on direct query only.
Let me put in some more details.
Data is sitting on a Azure SQL DB. I connect via SQL authentication using Direct Query. I am facing delays in both Power BI desktop and Power BI service. More in latter. Goal is to provide interactive report on Power BI service.
I have 10 direct query tables. Any given table/view does not have more than 100K entries.
On SQL client (SMSS) the queries render pretty fast. I am also not putting any DAX query in Power BI itself except for calculating average in one visual.
My Power BI report has 16 visuals with 4 slicer filters. The 10 tables are interacting with each other via one key. This key in some tables can have Many-Many, Many-1 or 1-1 relationship.
One thing best practice web page mentions is to limit visuals in the report. But I do want the interacting visuals.
I want to make sure that I am not missing some trivial or straightforward points that are causing performance loss. If my report load is indeed heavy then I will re-think my strategy.