Forum Discussion
Direct query BAD performance, what to do to try to optimize?
Hey guys, can anyone please give me some tips on how to improve performance on my model?
- I have a simple star schema.
- The fact table has only 40k rows.
- I'm required to use DirectQuery.
- I embed the report on an embedded capacity.
- I query a SQL Server on Azure.
- My visual is just a matrix with 6 columns (only sum and %) with a hierarchy made with a dimension table.
I've monitored how much time the SQL takes to respond, and it never goes above 1 second using SQL Profiler. However, the matrix takes from 10 to even 45 seconds to expand a level or to load.
One thing that I didn't understand it's why every interaction with the matrix it sends like 15 sql query
Does anyone know what could be the issue?
5 Replies
Use DAXStudio to evaluate the performance of the query for that visual. You can grab both queries (DAX and DQ) from the Performance Analyzer and then paste them into DAX Studio. There you can see the actual query plan and the server timings between the storage and formula engines.
Look for the number of records (high number means potential issue with the data model) and for excessive formula engine timing.
Hey thanks for the reply.
I checked with dax studio. I've FE 527ms and SE 4453MS.
It sends almost 100 SQL query.
This is the most expensive one, almost 1 sec:
SELECT TOP (1000001) [t13].[Level1Description] AS [t13_Level1Description],
[t13].[Level1Order] AS [t13_Level1Order],
COUNT_BIG([t13].[Level2Code]) AS [a0],
MIN([t13].[Level2Code]) AS [a1],
MAX([t13].[Level2Code]) AS [a2],
COUNT_BIG(*) AS [a3]
FROM ([dim].[Reclassifications]) AS [t13]
WHERE (
(
[t13].[Level1Description] IN (
'Costi del personale',
'Accantonamenti e svalutazioni',
'CONTI NON CLASSSIFICATI',
'COSTI DELLA PRODUZIONE',
'Godimento beni di terzi',
'MARGINE OPERATIVO LORDO',
'Variazioni delle rimanenze prodotti finiti',
'Altri ricavi',
'Acquisti di servizi',
'Oneri e proventi finanziari',
'Piano conti Co.Ge.',
'Ricavi dalle vendite e prestazioni',
'RENDICONTO FINANZIARIO, METODO DIRETTO',
'RISULTATO CORRENTE',
'Acquisti di merci',
'RISULTATO NETTO',
'Ammortamenti',
'RISULTATO OPERATIVO',
'Budget',
'CASH FLOW DI PERIODO',
'RISULTATO PRIMA DELLE IMPOSTE',
'CONTI D''ORDINE',
'STATO PATRIMONIALE',
'CONTI NON CLASSIFICATI',
'VALORE AGGIUNTO',
'VALORE DELLA PRODUZIONE',
'CONTO ECONOMICO'
)
)
)
GROUP BY [t13].[Level1Description],
[t13].[Level1Order]
I don't know what to doIn your SQL Server slap an index on Level1Description and Level1Order
Show the measure(s) that feed(s) the values area of the matrix.
