Aiuto per ottimizzare query

di
Calida
il
9 risposte
C
Calida
Pochi posts
Iscritto da
set, 2016
Messaggi:
49

Aiuto per ottimizzare query

Salve a tutti, ho un applicazione con database su cartella condivisa da server linux e 4 pc con front-end che si collegano al database. Tutte le maschere si aprono velocemente tranne una che va ad interrogare tre tabelle. Il risultato è solo per visualizzazione per la modifica si clicca clicca sul record e si apre un'altra maschera dove modificare i dati. La maschera a volte impiega anche 30/40 secondi per restituire i dati. la query è questa.

SELECT ANAGRAFICA.CONO, AUTO.MODELLO, AUTO.TARGA, AUTO.TELAIO, LAVORI.KM, LAVORI.DATA, LAVORI.INCONVENIENTE, LAVORI.ID, LAVORI.OPERATORE
FROM (ANAGRAFICA INNER JOIN AUTO ON ANAGRAFICA.ID = AUTO.IDCLIENTE) INNER JOIN LAVORI ON AUTO.ID = LAVORI.IDAUTO
WHERE (((LAVORI.TIPO_DOCUMENTO)='SCHEDA'))
ORDER BY LAVORI.DATA DESC;

9 Risposte

  • max.riservo
    max.riservo
    Molto attivo
    Iscritto da
    ago, 2016
    Messaggi:
    704

    Re: Aiuto per ottimizzare query

    13/02/2024 - Calida ha scritto:

    … ho un applicazione con database su cartella condivisa da server linux e 4 pc con front-end che si collegano al database…

    Se puoi chiarisci meglio : 

    • BE Access oppure ? 
    • Cartella condivisa : Samba?

    13/02/2024 - Calida ha scritto:

    SELECT ANAGRAFICA.CONO, AUTO.MODELLO, AUTO.TARGA, AUTO.TELAIO, LAVORI.KM, LAVORI.DATA, LAVORI.INCONVENIENTE, LAVORI.ID, LAVORI.OPERATORE
FROM (ANAGRAFICA INNER JOIN AUTO ON ANAGRAFICA.ID = AUTO.IDCLIENTE) INNER JOIN LAVORI ON AUTO.ID = LAVORI.IDAUTO
WHERE (((LAVORI.TIPO_DOCUMENTO)='SCHEDA'))
ORDER BY LAVORI.DATA DESC;

    La query non sembra ‘strana’ :

    • indicativamente quanti records hai per ognuna delle 3 tabelle coinvolte nei join?
    • i campi in join sono indicizzati?
    • lavori.data è indicizzato?

    13/02/2024 - Calida ha scritto:

    La maschera a volte impiega anche 30/40 secondi per restituire i dati …

    Questa prestazione ‘scarsa’ la ottiene aprendo la maschera quando c'è un solo utente connesso al BE oppure quando ci sono più utenti connessi?

    Fai uso di transazioni?

    Utilizzi qualche blocco sui recordset (tramite VBA)?

  • C
    Calida
    Pochi posts
    Iscritto da
    set, 2016
    Messaggi:
    49

    Re: Aiuto per ottimizzare query

    Salve, grazie per l'intervento,

    si il be è un file accdb con solo le tabelle, ed è in una cartella samba condivisa.

    per i record non so quanti sono attualmente , ho una copia del db di 2 anni fa ed i record erano su Anagrafica circa 5500 , su Auto circa 9000 e su Lavori circa 27000. 

    le chiavi esterne sono indicizzate, il campo data non è indicizzato.

    le scarse prestazioni sono anche per singolo utente.

    no nessuna transazione.

    Nessun blocco record

  • @Alex
    @Alex
    Super Famoso
    Iscritto da
    feb, 2012
    Messaggi:
    8744

    Re: Aiuto per ottimizzare query

    Come ti ha detto Max la query va bene non può essere ottimizzata perché di fatto non ha nulla di particolare.

    Indicizza il campo Data e verifica se qualche cosa migliora… anche se temo di no.

    Solitamente con Access quando si lavora con Fe e Be si sfrutta uno stratagemma per velocizzare la connessione al BE che in assenza di scambio dati viene congelata… e questo rallenta molto.

    Fai un test apri una tabella in visualizzazione dati ed esegui la query confrontando se puoi le performance.

    Se cambiano significativamente tieni un rs aperto con 0 dati su una tabella inutile che nella sostanza evita al driver di sconnettersi e ma tiene il Pool delle connessioni attivo, quindi quando eseguirai la tua query sfrutterà la connessione gia attiva.

    Solitamente una cosa simile, apri un RS all'avvio e lo distruggi alla chiusura(questa è solo questione di pulizia ma non necessaria):

    SELECT * FROM DummyTable WHERE 1=0

    Questa query non genera traffico dati ma assicura che la connessione rimanga aperta finchè hai access attivo

    Se questo test non da risultati visibili… mon hai altro da fare che passare a SQL SERVER o RDBMS alternativo a Jet.

  • max.riservo
    max.riservo
    Molto attivo
    Iscritto da
    ago, 2016
    Messaggi:
    704

    Re: Aiuto per ottimizzare query

    Direi che Alex ti ha fornito l'unico approccio possibile per capire se qualcosa migliora.

    Alternative possibili :

    • passare a MySQl/MariaDB (visto che il server è Linux e puoi tramite phpMyAdmin gestirlo abbastanza facilmente) oppure MSSQL Server (che è disponibile anche per Linux, però credo non esistano gli strumenti di gestione e l'utilizzo di DBeaver per Linux sembra essere, dalla mia poca esperienza, un tool non completo). Con il passaggio ad un RDBMS la tua query può diventare una query passthru oppure una vista lato BE sulla quale fare poi una query lato FE)
    • installare sul server Linux una VM Windows Server (con gestione della multiutenza) e far connettere i Client tramite remote desktop. Con questo approccio il traffico di rete si trasforma nel traffico della sessione grafica rispetto al traffico dei dati. Potrebbe essere una soluzione in caso di grossi volumi di dati però ha un costo non indifferente (hardware performante, licenze, ulteriore S.O. da gestire)

    Come nota finale, se sei colui che gestisce/controlla il server Linux ti direi di verificare i log di sistema (tramite dmesg / Journal) per capire se hai qualche problema con la scheda di rete. Mi è recentemente capitato con un nuovo server di avere problemi saltuari e casuali di perdita di connessione di rete/rallentamenti vistosi dovuto ad un problema con i driver (Linux) di uno specifico chipset Intel (che al momento non ricordo ma se riesco in giornata poi specifico).

  • C
    Calida
    Pochi posts
    Iscritto da
    set, 2016
    Messaggi:
    49

    Re: Aiuto per ottimizzare query

    Buonasera, non ho ancora avuto modo di provare le modifiche ma oggi controllando il file di back-end ho visto che la sua dimensione si è ingrandita in maniera esponenziale. Uso lo stesso file da oltre 15 anni (infatti ho sbagliato a scrivere non è un accdb ma addirittura un mdb e prima lo usavo con il front-end scritto completamente con Vb6 poi da due anni fa ho dovuto apportare delle modifiche e per fare presto ho rifatto il programma interamente in access) fino a 2 anni fa il file aveva una dimensione di circa 17Mb oggi ho controllato ed è di 70Mb. in 2 anni è aumentato di 50Mb mentre per tutti gli anni precedenti nemmeno 20Mb….

    Ero tentato di fare un compatta e ripristina, ma l'unica volta che l'ho fatto ho notato una cosa che non mi è piaciuta e che mai avrei immaginato, ossia i valori autoincrement delle chiavi primarie sono tornati indietro utilizzando per i nuovi record chiavi gia utilizzate di record cancellati. Questo lo voglio escludere a priori perche mi è capitato di eliminare per sbaglio un record con tutti i suoi sottorecord e sotto-sottorecord, avendo un db di backup  pensavo che non avrei avuto problemi a ripristinare i record mancanti, così non l'ho fatto subito. quando mi sono deciso a farlo gli ID dei record da ripristinare erano di nuovo in uso e così ho perso la possibilita di poter ripristinare in maniera semplice e veloce semplicemente copiando e incollando i record…

    Forse la lentezza è dovuta anche all'ingrandimento del file ma non voglio compattare per il motivo appena detto.

    c'è un modo per evitare il reset degli autoincrement? 

  • M
    migliorabile
    Super Famoso
    Iscritto da
    apr, 2013
    Messaggi:
    6072

    Re: Aiuto per ottimizzare query

    Non esageriamo. 70MB sono una quisquillia, un microbo. 

    70GB o 7TB potrebbero ricevere l'appellativo ‘ingrandito in modo esponenziale’.

    in teoria una compattazione del database ha solo lo scopo di ricuperare lo spazio dei record cancellati. non dovrebbe toccare assolutamente niente altro e certamente non i contatori. Strano.

    perché non fai delle prove su un CLONE del tuo database? 

    ASSIOMI: 

    I dati originali non si toccano MAI.

    Se funziona NON SI TOCCA. 

    Gli ID devono essere solo degli identificatori univoci SENZA ASSOLUTAMENTE NESSUNA SEMANTICA AGGIUNTIVA. Se serve un ID con una semantica, va gestito personalmente come alternativa o in aggiunta ai sudetti ID. 

    ;-) 

  • C
    Calida
    Pochi posts
    Iscritto da
    set, 2016
    Messaggi:
    49

    Re: Aiuto per ottimizzare query

    Si lo so che 70Mb sono praticamente nulla, facevo solo il paragone che lo stesso file partendo da zero e utilizzato per 13 anni aveva una dimensione di 17Mb, in soli 2 anni di utilizzo (da quando uso access come fe) la dimensione è aumentata di 50Mb. Non intendevo dire che il file era esageratamente grande.

    Ho chiesto perche basta cercare in rete è la prima cosa che tutti consigliano di fare in caso di rallentamenti è un compatta e ripristina.

    Ora correggetemi se sbaglio ma qualcuno una volta mi ha detto che quando si elimina un record in access, questo viene eliminato, ma è come se al suo posto rimanesse un buco vuoto, ossia il vuoto di spazio che si crea tra il record prima e quello successivo non viene automaticamente recuperato, e se così fosse immagino che sia questo il motivo per cui molti consigliano di compattare e ripristinare per recuperare prestazioni ed immagino sia lo stesso motivo per cui il fila dopo la procedura risulta di minori dimensioni.

    faro anche questa prova

  • M
    migliorabile
    Super Famoso
    Iscritto da
    apr, 2013
    Messaggi:
    6072

    Re: Aiuto per ottimizzare query

    La cancellazione di un record non e' mai una vera cancellazione, e questo e' vero per TUTTI i DBMS.

    I motivi sono diversi :

    1. Se dentro una transizione fai una cancellazione e poi un rollback, il record cancellato DEVE ricomparire (anche se tu non specifichi esplicitamente la transazione, questa c'e' SEMPRE). Poi, ovviamente, fino ache non chiudi la transizione, TUTTI gli altri client DEVONO vedere quel record 
    2. compattare la tabella ad ogni cancellazione sarebbe inutilmente oneroso
    3. la compattazione della tabella potrebbe richiedere MOOOLTO tempo (minuti o ore) quindi e' meglio che sia l'utente a decidere quando farla, poiché durante questo periodo la tabella non e' accessibile
    4. ci sono dei meccanismi che permettono di ripristinare lo stato di una tabella allo stato di parecchie transazioni gia' concluse usando il file di ‘log’ (delle transazioni) 

    quindi un record cancellato non viene mai cancellato veramente, ma solo marcato come ‘cancellato’, in modo da poterlo ripristinare se necessario. .

    Solo la compattazione della tabella elimina fisicamente i record cancellati, e riorganizza tutti gli altri record. E tale operazione, ovviamente, non puo' essere invertita. 

  • C
    Calida
    Pochi posts
    Iscritto da
    set, 2016
    Messaggi:
    49

    Re: Aiuto per ottimizzare query

    Allora, vi aggiorno dopo aver fatte due operazioni.

    la prima è stata fare una copia del BE e provare a compattare e ripristinare. Dopo aver ripristinato la dimensione del file è di poco piu di un terzo del valore precedente, ora è sui 25Mb (prima 70) 

    la seconda è stata indicizzare il campo TIPO_DOCUMENTO che è nella clausola WHERE della SELECT.

    Beh ora per aprire la maschera impiega 2-3 secondi.  Sono rimasto sorpreso… ora il merito è di aver compattato o di aver indicizzato il campo?

Devi accedere o registrarti per scrivere nel forum
9 risposte