Table preview is very slow on big tables

[samusaran]

When trying to preview data on a big table (30mil rows, 40 columns) the initial query time is very long. The program hangs until the records are displayed.

I guess the table preview just queryes EVALUATE 'Table name' instead of using TOPN, but I didn't trace the query so I don't really know.

Also often no records are returned at all (timeout?)

[m-kovalsky]

Running a SQL profiler trace while using the Table Preview feature shows that TE3 is using the TOPNSKIP function. The TOPNSKIP is quite effective from a performance standpoint. And, the default setting for Table Preview is to only query 100 rows. I've used this against a similarly sized table and it is quite fast (could be your connection?).

[otykier]

@samusaran what type of Analysis Services instance are you connecting to? (SSAS 2016 / 2017 / 2019 / Azure AS / PBI Premium?)

@m-kovalsky is correct that Tabular Editor 3 uses TOPNSKIP, which is the fastest way to retrieve a subset of rows from a table, but unfortunately not all editions of Analysis Services supports this function. If you're connected to SSAS 2016, TE3 will fall back to using TOPN, which could be slower.

[samusaran]

It's a SSAS 2016 on premise. I have no problem with a 2017. I can do more tests if needed. Sent from Nine<http://www.9folders.com/>

…

________________________________ Da: Daniel Otykier ***@***.***> Inviato: giovedì 8 luglio 2021 19:44 A: TabularEditor/TabularEditor3 Cc: Alessandro Losi; Mention Oggetto: Re: [TabularEditor/TabularEditor3] Table preview is very slow on big tables (#126) @samusaran<https://github.com/samusaran> what type of Analysis Services instance are you connecting to? (SSAS 2016 / 2017 / 2019 / Azure AS / PBI Premium?) @m-kovalsky<https://github.com/m-kovalsky> is correct that Tabular Editor 3 uses TOPNSKIP, which is the fastest way to retrieve a subset of rows from a table, but unfortunately not all editions of Analysis Services supports this function. If you're connected to SSAS 2016, TE3 will fall back to using TOPN, which could be slower. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#126 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AACG7N4NXZBQ26272XHRBULTWXPXNANCNFSM5AASIINQ>.

[samusaran]

@otykier I see that TOPNSKIP is available on SSAS 2016 https://dax.guide/topnskip/

[samusaran]

Ok i've traced and I can see some differences in the queries. This is what I expected TE to do:

EVALUATE
TOPNSKIP ( 71, 0, 'Dati Assistenza' )

This query works fine and fast.

This is the query TE is doing:

EVALUATE
TOPNSKIP (
    71,
    0,
    SELECTCOLUMNS (
        'Dati Assistenza',
        "CodUtenteRichiedente", [CodUtenteRichiedente],
        "CodLineaProdotto", [CodLineaProdotto],
        "TempoRisposta2", [TempoRisposta2],
        "Intervento-Fuori Orario Si/No", [Intervento-Fuori Orario Si/No],
        "Caso-Codice", [Caso-Codice],
        "DataCreazioneCaso", [DataCreazioneCaso],
        "CodPartnerDiRif", [CodPartnerDiRif],
        "CodErogante", [CodErogante],
        "CodCausaleR", [CodCausaleR],
        "DurataRispostaMinuti", [DurataRispostaMinuti],
        "DurataRispostaOre", [DurataRispostaOre],
        "Intervento-Fatturabile Si/No", [Intervento-Fatturabile Si/No],
        "OreDaFatturare", [OreDaFatturare],
        "MinutiDaFatturare", [MinutiDaFatturare],
        "CodTipoIntervento", [CodTipoIntervento],
        "CodArea", [CodArea],
        "CodVersioneProdotto", [CodVersioneProdotto],
        "CodUrgenza", [CodUrgenza],
        "CodPriorita", [CodPriorita],
        "CodStato", [CodStato],
        "CodTipoRiga", [CodTipoRiga],
        "CodUtenteFinale", [CodUtenteFinale],
        "CodUfficio", [CodUfficio],
        "CodUtenteChiusura", [CodUtenteChiusura],
        "CodRisorsaChiusura", [CodRisorsaChiusura],
        "Intervento-Data", [Intervento-Data],
        "Intervento-Ora", [Intervento-Ora],
        "DataCreazioneRF", [DataCreazioneRF],
        "OraCreazioneRF", [OraCreazioneRF],
        "CodUtAutore1Int", [CodUtAutore1Int],
        "CodRisorsaAutore1Int", [CodRisorsaAutore1Int],
        "CodUtAutore", [CodUtAutore],
        "CodRisorsaAutore", [CodRisorsaAutore],
        "CodUtAutoreintR", [CodUtAutoreintR],
        "CodRisorsaAutorerint", [CodRisorsaAutorerint],
        "CodTipoRigaIntR", [CodTipoRigaIntR],
        "CodTipologiaAssist", [CodTipologiaAssist],
        "Caso-Caricato da Partner/Utente", [Caso-Caricato da Partner/Utente],
        "MinutiTrascTimeAss", [MinutiTrascTimeAss],
        "oreTrascTimeLavDR", [oreTrascTimeLavDR],
        "SNBozzaRF", [SNBozzaRF],
        "IdTempoRisposta", [IdTempoRisposta],
        "IdRisorsaPartner", [IdRisorsaPartner],
        "IdAutoreRichiesta", [IdAutoreRichiesta],
        "CodAssUtAssegnato", [CodAssUtAssegnato],
        "CodAssAssegnato", [CodAssAssegnato],
        "IdDurataRisposta", [IdDurataRisposta],
        "IdDurataCaso", [IdDurataCaso],
        "IdNumeroRisposte", [IdNumeroRisposte],
        "DurataMinutiRF", [DurataMinutiRF],
        "DurataMinutiRFSistemi", [DurataMinutiRFSistemi],
        "IdRisorsaCentro", [IdRisorsaCentro],
        "SNAnalisiArchivi", [SNAnalisiArchivi],
        "IdCertAutore", [IdCertAutore],
        "IdCertAutore1Int", [IdCertAutore1Int],
        "Caso-Codice IDS", [Caso-Codice IDS],
        "CodCausaleT", [CodCausaleT],
        "Caso con Risposta-Si/No", [Caso con Risposta-Si/No],
        "Caso con Richiedente e Utente finale coincidenti-Si/No", [Caso con Richiedente e Utente finale coincidenti-Si/No],
        "CodiceModulo", [CodiceModulo],
        "Intervento-Numero Progressivo", [Intervento-Numero Progressivo],
        "AppKey_AreaApplicativa", [AppKey_AreaApplicativa],
        "AppKey_UtenteAssegnato", [AppKey_UtenteAssegnato],
        "AppKey_Caso", [AppKey_Caso],
        "Caso-Caricato da Portale/Prodotto", [Caso-Caricato da Portale/Prodotto],
        "Caso-Titolo", [Caso-Titolo],
        "CodCommessa", [CodCommessa],
        "Caso-Riferimento", [Caso-Riferimento],
        "KeyCommessa", [KeyCommessa]
    )
)

The second query gets terminated with an out-of-memory error, and tabular editor tries this query another time. Why there's the need to use SELECTCOLUMNS with all columns, instead of previewing the whole table directly?

This is on SSAS 2016.

[otykier]

Hi @samusaran
Thanks for providing these additional details. The reason TE3 uses SELECTCOLUMNS is in case the table contains columns that are not in a queryable state - for example a calculated column that needs processing, or a column that was recently added to the table, but where the table data has not yet been refreshed. This is normally not a problem, however, it seems that SSAS 2016 is not well optimized for the use of SELECTCOLUMNS inside TOPNSKIP, causing the OOM-issues you mention.

What I can do to fix the issue in most cases, is to check if all columns on the table are in a queryable state, and then simply generate a TOPNSKIP(100, 0, 'Table Name') query. In other words, I should use SELECTCOLUMNS only when one or more columns are not queryable.

[otykier]

Hi all,

It's been a while, but I'm happy to inform that our latest release no longer has this issue, as we're now only using SELECTCOLUMNS in the generated DAX, when one or more columns on the table is in a non-queryable state (such as when a calculated column needs processing). This should speed up the data preview when all columns are queryable.

Thanks for your patience.