none
Query MS Access su SQL Server con inner join molto lente RRS feed

  • Domanda

  • Buongiorno,

    dopo l'aggiornamento della release dell'applicativo Client Access all'ultima disponibile (V7R1) sui nostri applicativi sviluppati con MS Access abbiamo riscontrato effettivi blocchi su query che coinvolgono in inner join tabelle SQL e AS400, anche se filtrate su valori chiave.

    Tramite il SQL Profiler abbiamo notato che avviene una trasformazione delle query e che, prima di tutto, viene eseguita la inner join su tutti i campi e poi viene applicato il filtro, sul campo chiave, che ritorna un record.

    Riportiamo di seguito un esempio della problematica e dei vari test effettuati:

    Data la query:

    select
    FROM TabSQL A
    inner join TabAS400 B on B.campoChiave = A.campoX
    inner join TabAS400 C on C.campoY = B.campoZ
    where A.campo1=123

    Se in TabSQL A il CampoX è indicizzato e B.campoChiave è appunto chiave, trasforma la query in :

    1) prepare select FROM TabSQL A where (A.campo1=123) and (A.campoX = p1)
    2) per ogni record in TabAS400 B  lancia una sp_execute B.campoChiave

    ...quindi per una tabella con molti record ...tempi biblici
     
    Se in TabSQL A il CampoX NON è indicizzato, trasforma la query in :

    1) prepare select FROM TabSQL A where (A.campo1=123)

    ....risultato in qualche millisecondo

    Abbiamo anche scoperto che fa la stessa cosa (del tempo infinito) in una query più semplice ma con la group by, sempre se A.campoX è indicizzato

    SELECT A.campo1, B.campoChiave
    FROM TabSQL A
    inner join TabAS400 B on B.campoChiave = A.campoX
    group by A.campo1, B.campoChiave

    ....Tempo eterno

    Eseguendo invece:

    SELECT A.campo1, B.campoChiave
    FROM TabSQL A
    inner join TabAS400 B on B.campoChiave = A.campoX

    ...in un attimo

    Riportiamo di seguito le versioni dei software coinvolti:

    S.O. PC: Windows 7 Professional 64 bit
    Microsoft Access: 2010 Professional 32 bit (rilevato anche su Runtime Access 2016 32/64 bit)
    SQL Server 2014  build 12.0.4213
    iSeries AS400: V7R2M0
    Client Access PC: V7R1M0 SP: SI57907
    martedì 29 marzo 2016 06:24

Risposte

  • Buonasera,

    nel primo caso, quello con 2 inner join,  personalmente ho sempre visto il comportamento riscontrato, Access cerca di mantenere il recordset in lettura/scrittura e per fare questo utilizza il meccanismo rilevato. Impostando il la proprietà "Tipo recordset" della maschera o query da dynaset a snapshot il comportamento cambia, verrà inviato al server una query molto simile a quella impostata, ovviamente il recordset sarà in sola lettura.

    Per il caso della query con una sola inner join e Group by non mi è mai capitato ma credo che anche in questo caso il la modifica del tipo recordset porterà benefici.

    Eventualmente c'è sempre la possibilità di utilizzare le query pass through come indicato in questo articolo ACC: Ottimizzazione delle prestazioni Client/Server

    Giorgio Rancati

    mercoledì 30 marzo 2016 16:21
    Moderatore