none
piani di esecuzione e performances RRS feed

  • Domanda

  • Ciao a tutti

    ho un problema che non riesco a risolvere di questo tipo:

    ho una tabella di testata ordine cliente simile a questa

    CREATE TABLE [dbo].[MA_SaleOrd] (
    [SaleOrdId] [int] NOT NULL,
     [InternalOrdNo] [varchar] (10) NULL CONSTRAINT DF_SaleOrd_InternalOr_00 DEFAULT(''),
     [ExternalOrdNo] [varchar] (20) NULL CONSTRAINT DF_SaleOrd_ExternalOr_00 DEFAULT(''),
     [OrderDate] [datetime] NULL CONSTRAINT DF_SaleOrd_OrderDate_00 DEFAULT('17991231'),
    [Customer] [varchar] (12) NULL CONSTRAINT DF_SaleOrd_Customer_00 DEFAULT(''),
    ....... diversi altri campi
       CONSTRAINT [PK_SaleOrd] PRIMARY KEY NONCLUSTERED

     (
      [SaleOrdId]
     ) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE INDEX [MA_SaleOrd2] ON [dbo].[MA_SaleOrd] ([InternalOrdNo]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd3] ON [dbo].[MA_SaleOrd] ([Customer], [OrderDate]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd4] ON [dbo].[MA_SaleOrd] ([OrderDate], [InternalOrdNo]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd5] ON [dbo].[MA_SaleOrd] ([Delivered]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd6] ON [dbo].[MA_SaleOrd] ([Invoiced]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd7] ON [dbo].[MA_SaleOrd] ([Allocated]) ON [PRIMARY]
    CREATE INDEX [MA_SaleOrd8] ON [dbo].[MA_SaleOrd] ([PreShipped]) ON [PRIMARY]

    il software gestionale che la utilizza, esegue delle query del tipo

    select  saleordid, customer, ordderdate ......

     from MA_SaleOrd where Customer like '14759%'

    per ricercare gli ordini di uno specifico cliente.

    Ho notato una notevole differenza di velocità nella ricerca di un certo codice cliente rispetto ad altri

    La ricerca per questo codice cliente "14759" restituisce 211 righe  con un rid lookup eseguito 211 volte  in tempi maggiori

    rispetto a

    select  saleordid, customer, ordderdate ......

     from MA_SaleOrd where Customer like'11692%'

    la query qui sopra effettua un table scan 8 volte per restituire oltre 5000 righe.

    Ho
    ricostruito gli indici della tabella che erano molto frammentati ed aggiornato le statistiche ma poco è cambiato.

    non riesco a trovare spiegazioni/soluzioni per ottimizzare questa ricerca: non posso variare la query che viene eseguita dal software gestionale ma non sono riuscita a migliorare le prestazioni aggiungendo indici, ricreandoli o altro.

    Grazie in anticipo a chi mi darà un parere in merito.

    ciao

    Paola

    venerdì 29 aprile 2016 10:22

Risposte

  • ciao a tutti,

    dopo innumerevoli prove, ho verificato che il tempo di esecuzione della query di sql in realtà è congruente con il numero di record estratti! Nelle prove davo per scontato che i tempi di risposta del gestionale fossero congruenti con quelli di sql, quindi non mi basavo sui tempi calcolati dai piani di esecuzione/statistiche, bensì da quelli del gestionale. Segnalato il problema al produttore, quest'ultimo mi ha risposto di aver già riscontrato in diversi contesti questa incongruenza.

    Grazie a tutti per le utilissime indicazioni che mi hanno aiutato a sviscerare la problematica.

    Paola

    giovedì 12 maggio 2016 15:19

Tutte le risposte

  • Ciao a tutti

    ho un problema che non riesco a risolvere di questo tipo:

    [CUT]

    non riesco a trovare spiegazioni/soluzioni per ottimizzare questa ricerca: non posso variare la query che viene eseguita dal software gestionale ma non sono riuscita a migliorare le prestazioni aggiungendo indici, ricreandoli o altro.

    Grazie in anticipo a chi mi darà un parere in merito.

    ciao

    Paola

    Ciao Paola,

    se quel tipo di ricerca è importante puoi pensare di creare un indice di copertura per la query in questione

    CREATE INDEX [MA_SaleOrd3] ON [dbo].[MA_SaleOrd] ([Customer], [OrderDate])  INCLUDE (elencoCampi, ecc)

    vedi : Creare indici con colonne incluse

    Ovviamente devi considerare se i benefici ottenuti valgano il costo speso per mantenere quel tipo di indice, vedi: Linee guida generali per la progettazione di indici dove dice:

    Evitare di aggiungere colonne non necessarie. L'aggiunta di un numero eccessivo di colonne di indice può avere effetti negativi sullo spazio su disco e sulle prestazioni per la gestione degli indici.

    Ciao
    Giorgio Rancati


    sabato 30 aprile 2016 06:40
    Moderatore
  • Ciao Giorgio,

    innanzitutto ti ringrazio. Avevo pensato alle colonne incluse nell'indice: purtroppo le ricerche del gestionale selezionano tutte le colonne della tabella, quindi non credo sia una strada percorribile.

    Grazie del consiglio.

    ciao

    Paola

    sabato 30 aprile 2016 16:16
  • Ciao Paola,

    premetto che non ho l'esperienza in merito di performance tuning che puoi trovare qui nel forum, però ho dedicato qualche momento al tuo caso, cercando di ricreare un ambiente simile in un'instanza sul mio pc.

    Ho notato che la tua tabella è una heap, ovvero non contiene un clustered index. Per quanto concerne il comportamento non costante del query optimizer, credo si tratti semplicemente di un caso di parameter sniffing. Se vuoi approfondire, ecco il link.

    Assumendo che non sia possibile chiedere al tuo vendor di modificare la query, ho ottenuto buoni risultati con una soluzione molto POCO elegante. Ho creato un clustered index sul campo customer. Non essendo unique, SQL aggiungera' dietro le quinte un ulteriore campo nascosto e univoco, denominato appunto uniquifier.

    E' una situazione che normalmente non propongo...ma se le alternative si chiamano Table Scan e Rid Lookup...con tale indice, la query che sottoponi viene risolta con un Clustered Index Seek.

    Premetto che in condizioni normali/ottimali:

    • le tabelle sono clustered index e non heap
    • le query di questo tipo non estraggono tutti i campi, pertanto con piccoli subset - ti basta fare covered indexes, come spiegava Giorgio
    • chiederei al vendor di trasformare la tabella in clustered index, con tale indice sulla chiave primaria, e poi raffinare le query estraendo SOLO i campi davvero necessari, modificando gli indici non-clustered di conseguenza. Questo è chiaramente lo scenario migliore, e di solito, poco praticabile per ragioni di tempo e denaro.

    Se...

    • la query che sottoponi è molto importante e spesso utilizzata
    • è possibile fare delle prove su un tuo eventuale ambiente di test per valutarne l'impatto sulle altre query
    • non giungono soluzioni più eleganti e professionali

    ...puoi fare una prova e farci sapere...


    • Modificato Luca Bruno domenica 1 maggio 2016 09:07
    domenica 1 maggio 2016 09:06
  • Ciao Luca,

    confermo che la tabella, come tutte quelle del gestionale, non ha indici cluster. Ho letto il link segnalato e l'ho trovato davvero molto interessante!

    Su una copia del db ho creato un indice cluster ed effettivamente il piano di esecuzione ricerca tramite l'indice cluster invece che con un rid lookup.

    Mi aspettavo un miglioramento dei tempi di  risposta del gestionale, invece non ho trovato variazioni: circa 12 secondi per 211 record del cliente "incriminato" e 2-3 per oltre 5000 record di un altro codice.

    Ho allora provato ad esportare la tabella in xml tramite la console del programma e a reimportare la tabella in un altro db: i tempi di esecuzione sono notevolmente ridotti;

    Proverò a lavorarci su.

    Ti ringrazio molto dei consigli

    ciao

    Paola

    domenica 1 maggio 2016 15:01
  • Ciao Luca,

    confermo che la tabella, come tutte quelle del gestionale, non ha indici cluster. Ho letto il link segnalato e l'ho trovato davvero molto interessante!

    Su una copia del db ho creato un indice cluster ed effettivamente il piano di esecuzione ricerca tramite l'indice cluster invece che con un rid lookup.

    Mi aspettavo un miglioramento dei tempi di  risposta del gestionale, invece non ho trovato variazioni: circa 12 secondi per 211 record del cliente "incriminato" e 2-3 per oltre 5000 record di un altro codice.

    [CUT]

    umm abbastanza anomala come situazione, 211 record estratti con clustered index seek impiegano 4 volte il tempo di estrazione di 5000 record  ?

    Sei sicura di aver collegato il gestionale alla copia modificata del Db ?

    Ciao
    Giorgio Rancati

    domenica 1 maggio 2016 19:14
    Moderatore
  • Ciao 

    Proverei a verificare la distribuzione dei dati in base alle statistiche presenti sull'indice MA_SaleOrd3:

    DBCC show_Statistics (MA_SaleOrd ,MA_SaleOrd3)

    Se ci sono valori di "Customer" con cardinalità molto più alta di altri con buona  probabilità ti trovi in presenza di un problema di parameter sniffing ( che di per se non è un male, ma dipende...)

    cfr: http://www.sommarskog.se/query-plan-mysteries.html


    lunedì 2 maggio 2016 09:35
  • ciao a tutti,

    dopo innumerevoli prove, ho verificato che il tempo di esecuzione della query di sql in realtà è congruente con il numero di record estratti! Nelle prove davo per scontato che i tempi di risposta del gestionale fossero congruenti con quelli di sql, quindi non mi basavo sui tempi calcolati dai piani di esecuzione/statistiche, bensì da quelli del gestionale. Segnalato il problema al produttore, quest'ultimo mi ha risposto di aver già riscontrato in diversi contesti questa incongruenza.

    Grazie a tutti per le utilissime indicazioni che mi hanno aiutato a sviscerare la problematica.

    Paola

    giovedì 12 maggio 2016 15:19