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"
