Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning, everybody.
In a DirectQuery report, using Performance Analyzer, the SQL statement (query) that PowerBI sends to the database to display the visuals has been captured. This query contains certain statements that affect performance and response time.
For example, for a stacked bar chart, use the CAST and TOPfunctions. If I test the query by running it directly in the database without modifying anything, it lasts approximately 2.55 seconds. If I modify the query by removing the CAST and TOP functions, the same dataset is obtained, but in a time of 0.37 seconds (almost 7 times faster).
Is it possible to modify the statement (or query SQL) that Power BI autogenerates and sends to the database to get the dataset and build the visual component?.
Thank you in advance for your feedback.
This is the query that self-generates. Runs in 2.55 seconds. Use TOP and CAST.
Select
TOP 1000001 "t1"." estado_cliente",
CAST(COUNT("t1"." codigo_cliente") AS BIGINT)
AS "a0"
FROM
(
SELECT
CAST("codigo_cliente" AS VARCHAR(4000))
AS "codigo_cliente",
CAST("estado_cliente" AS VARCHAR(4000))
AS "estado_cliente"
FROM
(
(select "$Table"." apellidos_cliente" as "apellidos_cliente",
"$Table"." nombres_cliente" as "nombres_cliente",
"$Table"." direccion_cliente" as "direccion_cliente"
from "Base_datos"." tabla_clientes" "$Table")
)
AS "t1"
)
AS "t1"
GROUP BY "t1"." estado_cliente"
If I remove the CAST and TOP functions. Runs in 0.37 seconds. Removing TOP and CAST.
Select
"t1"." estado_cliente",
COUNT("t1"." codigo_cliente")
AS "a0"
FROM
(
SELECT
"codigo_cliente"
AS "codigo_cliente",
"estado_cliente"
AS "estado_cliente"
FROM
(
(select "$Table"." apellidos_cliente" as "apellidos_cliente",
"$Table"." nombres_cliente" as "nombres_cliente",
"$Table"." direccion_cliente" as "direccion_cliente"
from "Base_datos"." tabla_clientes" "$Table")
)
AS "t1"
)
AS "t1"
GROUP BY "t1"." estado_cliente"
Solved! Go to Solution.
Raise this as an Idea on the ideas page. Not currently possible to impact any of the folded queries.
Raise this as an Idea on the ideas page. Not currently possible to impact any of the folded queries.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |