none
Creazione indici in SQL Server 2000 - Come agisce

    Question

  • Ciao a tutti,
    creando un indice in una tabella in SQL Server 2000, bisogna eseguire un altro comando affinchè le query di Select che poi agiscono su questa tabella lo "sentano"?

    Ed avere troppi indici in una medesima tabella, può avere un effetto controproducente, sempre sulle performance delle query di Select su quella tabella?

    Grazie

    Luigi

    Monday, January 10, 2011 2:50 PM

Answers

  • Ciao Luigi, 

    si grazie si riesce a capire.

    Noto per prima cosa che la Constraint IX_T_LettureContatoriEnergia ([ContatoriEnergia_ID] ASC, [DataOraDa] ASC, [DataOraA] ASC, [SKCConduzione_ID] ASC) è un indice anomalo.

    Si tratta un indice che mira ad essere un COVERED ovvero che estragga le colonne che vengono poi utilizzate nei filtri/join. Considera PERO' che NON potrà essere ottimizzata la ricerca (il FILTRO per data) in quanto le colonne vengono specificate in un ORDINE NON OTTIMIZZABILE al meglio.

    Infatti, affinchè l'indice sia utile proverei a cambiarlo così:

     IX_T_LettureContatoriEnergia (FlagOrario ASC, UnitaProduttive ASC, [DataOraA] ASC)

    Le logiche di definizione dell'indice sono le seguenti:

    a) Il campo "FlagOrario" è specificato perchè metti una condizione ESPLICITA (=15) quindi è facile che l'indice possa scremare gli altri dati al primo livello di filtro.

    b) UnitàProduttive viene messo in INNER JOIN e quindi filtrato a mo di "IN" sostanzialmente. Quindi per questo motivo è un filtro selettivo.

    c) Altro filtro è sul BETWEEN di "DataA", visto che la DATAINSERIMENTO (pare) estrae troppe righe , quindi sulla DataA visto che il filtro di esempio è "'08/01/2010 23:00:00' and '09/01/2010 23:00:00'".

    d) L'ordine dei campi dell'indice va da quelli con MINOR VARIABILITA' a quello con MAGGIOR VARIABILITA' (la data è probabilmetne diversa da record a record se è a livello di secondi... ad esempio, le UnitaProduttive sono sicuramente un set di valori distinti relativamente ridotto, mentre il FlagOrario è probabilmente un set ben definito di valori (quindi con poca variabilità).

    Un approccio simile va fatto sulla T_LettureContatoriEnergiaStoria.

    Fammi sapere se questo da dei miglioramenti, considerando prima di tutto le query singole (1) e (2) - quelle da 5 minuti.

     


    Adriano
    • Marked as answer by Ciupaz Tuesday, January 11, 2011 10:48 AM
    Tuesday, January 11, 2011 8:30 AM
  • No no è corretto il probelma è che non è un indice UNIQUE.

    Usa CREATE INDEX

    PS: Aggiungi anceh l'altra colonna (FlagOrario ASC, UnitaProduttive ASC, [DataOraA] ASC).

    Fammi sapere


    Adriano
    • Marked as answer by Ciupaz Tuesday, January 11, 2011 10:48 AM
    Tuesday, January 11, 2011 10:03 AM

All replies

  • Ciao Luigi, no non è necessario.

    L'utilizzo dell'indice corretto è qualcosa che avviene automaticamente ed avviene durante la fase di computazione del piano di esecuzione. 

    L'indice viene scelto se è stato costruito correttamente per l'esigenza della query che stai eseguendo.

    E' buona prassi scegliere gli indici considerando molti importanti fattori che elenco di seguito:

    1) Una query puntuale del tipo "where campo = costante" oppure "where campo > costante" può beneficiare di un indice che abbia "campo" come primo campo dell'indice stesso.

    2) Gli indici si creano quando strettamente necessari: Numero di query elevato nell'unità di tempo; necessità di risposte rapide delle query. Se si ha a che fare con un sistema OLTP con numero elevato di transazioni allora può essere necessario cambiare strategia e/o approccio perchè troppi indici rallenterebbero l'inserimento/modifica di record

    3) Troppi indici possono essere dannosi (vedi punto 2).

    4) A volte può essere necessario ricostruire le statistiche degli indici (utili appunto alla scelta dell'indice corretto), eseguendo la stored proc "sp_updatestats".

    5) Non creare indici sulle stesse colonne qualora sia possibile inserire due indici differenti. 

    6) Analizzare sempre il piano di esecuzione per capire se SQL server sta approcciando la query in maniera corretta o meno

    Di fronte a che probelmi di trovi di preciso? Analizzare il piano di esecuzione può dare indicazioni sui colli di bottiglia.

    A volte se una tabella è "troppo grossa" è necessario considerare la storizzazione delle informazioni più "vecchie" e/o partizionare i dati.

    HTH,
    Ciao


    Adriano
    Monday, January 10, 2011 4:46 PM
  • ti aggiungerei anche

    - metti gli indici su campi con tipi di dati "compatti" come tinyint, smallint  etc piuttosto che su di un varchar gigantesco

    - utilizza l'analizzatore di prestazione di sql server che ti suggerisce lui quali indici creare eventualmente (molto importante il punto 3 di Adriano)

    Monday, January 10, 2011 7:46 PM
  • Grazie per le spiegazioni, molto interessanti.
    Il mio problema è che un report va in timeout.
    Dal codice ho estratto la query che utilizza per popolare la sua tabella.
    E' composta da una Union di 4 Select (aggrega i risultati per mese e per anno).
    Le prime due Select ci mettono circa 5 minuti l'una, e sono quelle responsabili della lentezza complessiva, credo, dato che le altre due ci mettono solo qualche secondo.
    Entrambe agiscono su due tabelle gigantesche (qualche milione di record) messe in join e filtrate con due filtri su campi data.
    Il primo filtro utilizza un Between (il report ha DataDa e DataA come parametri, e vengono confrontati con un campo DataRiferimento di una tabella), mentre il secondo filtro utilizza solo un <= su di un altro campo data.
    Come posso far scegliere a SQL Server il miglior indice da utilizzare?
    Il mio database è SQL Server 2000.

    Luigi

    Monday, January 10, 2011 8:07 PM
  • Ciao Luigi, focalizzerei una prima analisi appunto sulle prime due SELECT. 

    5 Minuti sono effettivamente un'eternità. Mi chiedo:

    1) Fai un SELECT * oppure stai selezionando IL MINIMO DEI CAMPI che ti servono?

    2) L'Indice più efficace è sicuramente quello che ti permette di ottenere le tue informazioni in maniera più diretta. Quindi, se per esempio il tuo Between lo fai su DataRiferimento, un indice su questo campo sicuramente aiuta.

    Ad ogni modo, ... sicuramente hai già considerato queste due prime cose,... quindi ti chiedo se puoi girarci la struttura di queste tabelle? Fai un GENERATE SCRIPT delle tabelle e mandaci la query che usi... può essere di aiuto.

    Infine, se fai un UNION considera che UNION ALL è piu' performante, perchè NON ESEGUE UN DISTINCT. Se sei SICURO che i dati NON necessitino di un DISTINCT vai con il UNION ALL. E' garantito un piano di esecuzione più performante e ottimizzato.

    Fammi sapere,

    CIAO

     


    Adriano
    Monday, January 10, 2011 8:35 PM
  • Questa è la query che utilizza il report, e all'inizio di ogni blocco ho messo il tempo in minuti che ci impiega ciascuna Select. Come si vede sono essenzialmente le prime 2 quelle che ci mettono molto.

    select sum(Cnt) 
    from (

    -- 05:38
    select count(*) as Cnt 
    from T_LettureContatoriEnergia a  inner join T_ContatoriEnergia b
    on a.ContatoriEnergia_ID = b.ContatoriEnergia_ID 
    inner join app.fn_ParseCommaDelimitedList('769,770,771,772,1000001,775,776,777,778,779,780,781,782,783,784,785,786,787,1000003,789,790,791,792,793,794,795,796,797,798,799,800,803,804,805,806,807,808,809,810,836,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,1000002,1000004') c
    on b.UnitaProduttive_ID = c.ItemID 
    where a.DataOraA between '08/01/2010 23:00:00' and '09/01/2010 23:00:00'
    and a.DataOraInserimento<='10/01/2011 14:17:00'
    and a.FlagOrario=15 

    union 

    -- 03:45
    select count(*) 
    from T_LettureContatoriEnergiaStoria a 
    inner join T_LettureContatoriEnergia b on a.LettureContatoriEnergia_ID=b.LettureContatoriEnergia_ID 
    inner join T_ContatoriEnergia c on b.ContatoriEnergia_ID=c.ContatoriEnergia_ID 
    inner join app.fn_ParseCommaDelimitedList('769,770,771,772,1000001,775,776,777,778,779,780,781,782,783,784,785,786,787,1000003,789,790,791,792,793,794,795,796,797,798,799,800,803,804,805,806,807,808,809,810,836,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,1000002,1000004')  d
    on c.UnitaProduttive_ID=d.ItemID 
    where b.DataOraA between '08/01/2010 23:00:00' and '09/01/2010 23:00:00'
    and a.DataOraInserimento < = '10/01/2011 14:17:00' and b.FlagOrario=15
     
    union
     
    -- 00:01 sec
    select count(*) from T_LettureContatoriEnergiaAusiliari a 
    inner join T_ContatoriEnergia b on a.ContatoriEnergia_ID=b.ContatoriEnergia_ID 
    inner join app.fn_ParseCommaDelimitedList('769,770,771,772,1000001,775,776,777,778,779,780,781,782,783,784,785,786,787,1000003,789,790,791,792,793,794,795,796,797,798,799,800,803,804,805,806,807,808,809,810,836,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,1000002,1000004')  c
    on b.UnitaProduttive_ID=c.ItemID
    where a.DataOraA between '08/01/2010 23:00:00' and '09/01/2010 23:00:00'
    and a.DataOraInserimento < = '10/01/2011 14:17:00' and a.FlagOrario=15
      
    UNION

    -- 00:01 sec
    SELECT COUNT(*) 
    FROM t_PNScadaUp  INNER JOIN T_PNScadaUPTrasf
    ON T_PNScadaUPTrasf.DescrizioneUP = t_PNScadaUp.DescrizioneUP 
    INNER JOIN T_UnitaProduttive ON T_PNScadaUPTrasf.UnitaProduttive_ID = T_UnitaProduttive.UnitaProduttive_ID 
    INNER JOIN app.fn_ParseCommaDelimitedList('769,770,771,772,1000001,775,776,777,778,779,780,781,782,783,784,785,786,787,1000003,789,790,791,792,793,794,795,796,797,798,799,800,803,804,805,806,807,808,809,810,836,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,1000002,1000004')
    c ON T_UnitaProduttive.UnitaProduttive_ID = c.ItemID
    AND (t_PNScadaUp.DataOra >= '08/01/2010 23:00:00' AND t_PNScadaUp.DataOra < '09/01/2010 23:00:00' )

    ) s

     

    -- Dati di esempio
    DataA = 09/01/2010 23:00:00
    analisi = 10/01/2011 14:17:00
    FlagOrario = 15

    e la tabella T_LettureContatoriEnergia (forse quella responsabile), è così composta:

     


    CREATE TABLE [app].[T_LettureContatoriEnergia](
     [LettureContatoriEnergia_ID] [int] IDENTITY(1,1) NOT NULL,
     [ContatoriEnergia_ID] [int] NOT NULL,
     [Causale] [varchar](18) NOT NULL,
     [DataOraLettura] [datetime] NULL,
     [Segnante] [decimal](17, 5) NULL,
     [EnergiaLorda] [decimal](18, 3) NULL,
     [EnergiaNetta] [decimal](18, 3) NULL,
     [FlagAsteriscoLorda] [smallint] NULL,
     [FlagAsteriscoNetta] [smallint] NULL,
     [DataOraDa] [datetime] NULL,
     [DataOraA] [datetime] NULL,
     [DataOraInserimento] [datetime] NULL,
     [Utente_Id] [int] NULL,
     [SKCConduzione_ID] [int] NOT NULL,
     [FlagOrario] [int] NULL,
     [ConteggioCommerciale] [char](1) NULL,
     [ConteggioFiscale] [char](1) NULL,
     [ConteggioTecnico] [char](1) NULL,
     [Differenza] [decimal](18, 4) NULL,
     [FlagSegnanteMax] [smallint] NULL,
     [FlagContatoreGuasto] [smallint] NULL,
     [TipoRicostruzione_ID] [int] NULL,
     [SegnanteIniziale] [decimal](18, 4) NULL,
     CONSTRAINT [PK_T_LettureContatoriEnergia] PRIMARY KEY CLUSTERED
    (
     [LettureContatoriEnergia_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [IX_T_LettureContatoriEnergia] UNIQUE NONCLUSTERED
    (
     [ContatoriEnergia_ID] ASC,
     [DataOraDa] ASC,
     [DataOraA] ASC,
     [SKCConduzione_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia]  WITH NOCHECK ADD  CONSTRAINT [FK_T_LettureContatoriEnergia_T_ContatoriEnergia] FOREIGN KEY([ContatoriEnergia_ID])
    REFERENCES [app].[T_ContatoriEnergia] ([ContatoriEnergia_ID])
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia] CHECK CONSTRAINT [FK_T_LettureContatoriEnergia_T_ContatoriEnergia]
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia]  WITH NOCHECK ADD  CONSTRAINT [FK_T_LettureContatoriEnergia_T_SKC_Conduzione] FOREIGN KEY([SKCConduzione_ID])
    REFERENCES [app].[T_SKC_Conduzione] ([SK_Conduzione_ID])
    ON DELETE CASCADE
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia] CHECK CONSTRAINT [FK_T_LettureContatoriEnergia_T_SKC_Conduzione]
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia]  WITH NOCHECK ADD  CONSTRAINT [FK_T_LettureContatoriEnergia_T_Utenti] FOREIGN KEY([Utente_Id])
    REFERENCES [app].[T_Utenti] ([Utenti_ID])
    GO

    ALTER TABLE [app].[T_LettureContatoriEnergia] CHECK CONSTRAINT [FK_T_LettureContatoriEnergia_T_Utenti]
    GO

    Non so se si riesce a capire qualcosa.

    Luigi

     

    Tuesday, January 11, 2011 8:12 AM
  • Ciao Luigi, 

    si grazie si riesce a capire.

    Noto per prima cosa che la Constraint IX_T_LettureContatoriEnergia ([ContatoriEnergia_ID] ASC, [DataOraDa] ASC, [DataOraA] ASC, [SKCConduzione_ID] ASC) è un indice anomalo.

    Si tratta un indice che mira ad essere un COVERED ovvero che estragga le colonne che vengono poi utilizzate nei filtri/join. Considera PERO' che NON potrà essere ottimizzata la ricerca (il FILTRO per data) in quanto le colonne vengono specificate in un ORDINE NON OTTIMIZZABILE al meglio.

    Infatti, affinchè l'indice sia utile proverei a cambiarlo così:

     IX_T_LettureContatoriEnergia (FlagOrario ASC, UnitaProduttive ASC, [DataOraA] ASC)

    Le logiche di definizione dell'indice sono le seguenti:

    a) Il campo "FlagOrario" è specificato perchè metti una condizione ESPLICITA (=15) quindi è facile che l'indice possa scremare gli altri dati al primo livello di filtro.

    b) UnitàProduttive viene messo in INNER JOIN e quindi filtrato a mo di "IN" sostanzialmente. Quindi per questo motivo è un filtro selettivo.

    c) Altro filtro è sul BETWEEN di "DataA", visto che la DATAINSERIMENTO (pare) estrae troppe righe , quindi sulla DataA visto che il filtro di esempio è "'08/01/2010 23:00:00' and '09/01/2010 23:00:00'".

    d) L'ordine dei campi dell'indice va da quelli con MINOR VARIABILITA' a quello con MAGGIOR VARIABILITA' (la data è probabilmetne diversa da record a record se è a livello di secondi... ad esempio, le UnitaProduttive sono sicuramente un set di valori distinti relativamente ridotto, mentre il FlagOrario è probabilmente un set ben definito di valori (quindi con poca variabilità).

    Un approccio simile va fatto sulla T_LettureContatoriEnergiaStoria.

    Fammi sapere se questo da dei miglioramenti, considerando prima di tutto le query singole (1) e (2) - quelle da 5 minuti.

     


    Adriano
    • Marked as answer by Ciupaz Tuesday, January 11, 2011 10:48 AM
    Tuesday, January 11, 2011 8:30 AM
  • Strano, ho provato a lanciare questo script di creazione dell'indice (il campo UnitaProduttive è sull'altra tabella), dopo averlo prima eliminato:

    ALTER TABLE [app].[T_LettureContatoriEnergia] ADD  CONSTRAINT [IX_T_LettureContatoriEnergia] UNIQUE NONCLUSTERED
    (
     FlagOrario ASC,
     DataOraA ASC
    )


    ma mi restituisce il seguente errore:

    Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 21. Most significant primary key is '<NULL>'.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.
    The statement has been terminated.


    Luigi

    • Edited by Ciupaz Tuesday, January 11, 2011 10:09 AM
    Tuesday, January 11, 2011 10:01 AM
  • No no è corretto il probelma è che non è un indice UNIQUE.

    Usa CREATE INDEX

    PS: Aggiungi anceh l'altra colonna (FlagOrario ASC, UnitaProduttive ASC, [DataOraA] ASC).

    Fammi sapere


    Adriano
    • Marked as answer by Ciupaz Tuesday, January 11, 2011 10:48 AM
    Tuesday, January 11, 2011 10:03 AM
  • Ho creato questo indice:

    CREATE INDEX [IX_T_LettureContatoriEnergia]
    ON [app].[T_LettureContatoriEnergia](FlagOrario ASC,DataOraA ASC)


    e ora la prima select ci mette 40 secondi, mentre la seconda 45.
    Direi un gran passo avanti.

    Per ora mi potrebbe bastare :-)

    Grazie mille Adriano.

    Luigi

    Tuesday, January 11, 2011 10:47 AM
  • Ottimo. 

    CIAO!


    Adriano
    Tuesday, January 11, 2011 10:49 AM
  • Mi consigli un buon libro (va bene anche in inglese, non è un problema) dove posso approfondire questi argomenti di sviluppo su SQL Server?

    Se c'è bisogno di aprire un altro post, lo apro.


    Luigi

    Tuesday, January 11, 2011 10:53 AM
  • C'è un sito che è nato per questo e che è un HUB ottimo (Inglese però):

    http://www.sql-server-performance.com/

    Qui trovi un po di tutto.

    Come Libri, ti dirò, questo è ottimo:

    http://www.amazon.com/Microsoft-SQL-Server-2008-Unleashed/dp/0672330563/ref=sr_1_4?s=books&ie=UTF8&qid=1294743444&sr=1-4

    In generale comunque le best practices le impari nel tempo e con l'esperienza. 

    CIAO!


    Adriano
    Tuesday, January 11, 2011 10:59 AM
  • Il libro è un tomone da 1704 pagine, ma va bene lo stesso ;)
    Il sito in effetti è una fonte incredibile di articoli; lo tengo tra i preferiti.

    Grazie mille.

    Luigi

    Tuesday, January 11, 2011 11:16 AM