none
Indici su tabelle temporanee RRS feed

  • Domanda

  • Ciao,

    ho la necessità di creare indici su tabelle temporanee che vengono create da un stored procedure.

    Gli indici sono necessari in quanto l'esecuzione è lenta.

    C'è il problema che se 2 utenti eseguono nello stesso momento la SP, sembra che cerchino di creare lo stesso indice (anche se la tb temporanea è differente).

    C'è un modo di fargli ereditare il nome dalla tb temporanea per evitare il conflitto?

    venerdì 25 gennaio 2013 10:47

Risposte

  • Si, quando parli di tabelle indicizzate in effetti le performance sono migliori nelle tabelle temporanee quando hai dei set di dati grandi.. in ogni caso, ho appena fatto un paio di prove ed in effetti l'indice viene duplicato, però hai un modo per ovviare al problema, riscrivendo la query così :

    CREATE TABLE #FDAMaster
    (
    	[numero] [int] NOT NULL PRIMARY KEY WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    	[oda] [nvarchar](50) NOT NULL , 
    	[anno] [int] NOT NULL, 
    	[mese] [int] NOT NULL, 
    	[idxda_min] [int] NULL , 
    	[idxda] [nvarchar](500) NULL , 
    	[stato_id] [int] NOT NULL , 
    	[stato] [nvarchar](50) NULL , 
    	[fornitore_id] [int] NULL , 
    	[fornitore] [nvarchar](250) NULL , 
    	[richiedente_id] [int] NULL , 
    	[richiedente] [nvarchar](150) NULL , 
    	[responsabile_id] [int] NULL , 
    	[responsabile] [nvarchar](150) NULL , 
    	[periodo] [nvarchar](150) NULL , 
    	[data_approvato] [datetime] NULL , 
    	[importo_totale] [decimal](38, 4) NOT NULL
    );
    

    SQL Server assegnerà automaticamente un nome al tuo indice evitando che si diano conflitti, già testato e funzionante quà in ufficio, stesso utente SQL, sessioni diverse, stessa query lanciata con creazione indice.. ;)

    Un saluto

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:27
    venerdì 25 gennaio 2013 13:49
  • Veramente le clausole utilizzate sono le stesse, ho solo omesso il nome dell'indice dichiarandolo "in line" sul campo, ho omesso "CLUSTERED" e "ASC" per il fatto che una primary key ad un campo è sempre clustered e l'ordinamento di default è ASC.

    Controlla che il tuo problema non sia dovuto al query optimizer che deve ricostruire il piano di query quando la esegue, oppure se vuoi prima dichiari la tabella temporanea e poi usi la SP_ExecuteSQL per creare un indice con nome dinamico.

    Comunque se vuoi, puoi anche inserire "CLUSTERED" in questa maniera.. il tuo Data Execution Plan dov'è che indica il carico maggiore?

    CREATE TABLE #FDAMaster
    (
    	[numero] [int] NOT NULL PRIMARY KEY  CLUSTERED WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    	[oda] [nvarchar](50) NOT NULL , 
    	[anno] [int] NOT NULL, 
    	[mese] [int] NOT NULL, 
    	[idxda_min] [int] NULL , 
    	[idxda] [nvarchar](500) NULL , 
    	[stato_id] [int] NOT NULL , 
    	[stato] [nvarchar](50) NULL , 
    	[fornitore_id] [int] NULL , 
    	[fornitore] [nvarchar](250) NULL , 
    	[richiedente_id] [int] NULL , 
    	[richiedente] [nvarchar](150) NULL , 
    	[responsabile_id] [int] NULL , 
    	[responsabile] [nvarchar](150) NULL , 
    	[periodo] [nvarchar](150) NULL , 
    	[data_approvato] [datetime] NULL , 
    	[importo_totale] [decimal](38, 4) NOT NULL
    );


    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    venerdì 25 gennaio 2013 14:56
  • Ciao,

    forse intendi la primary key? In generale i constraint hanno nome univoco, quindi non te lo fa fare. Capisco la tua perplessità.

    Però, parlando di indici, uno script come il seguente va, e l'indice ha lo stesso nome:

    CREATE TABLE #tb (id int, valore varchar(10))
    GO
    
    CREATE INDEX IX_tb_1 ON #tb (id);
    GO
    
    CREATE INDEX IX_tb_2 ON #tb (valore);
    GO

    Quindi in realtà sono i constraint, non gli indici. L'unico modo per farlo senza problemi e senza EXEC è non dare nomi al constraint.


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    martedì 29 gennaio 2013 10:32
    Moderatore
  • Veramente mi pare che ti sono stati forniti 3 modi per risolvere il tuo problema.. volendo si può aggiungere anche la rename dinamica dell'indice fatta da te col seguente codice (da verificare però sulle tabelle temporanee, in questo momento non posso provarlo) :

    USE AdventureWorks2012;
    GO
    EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';
    GO

    In ogni caso, leggendo anche in giro l'esperienza di altre persone, la prima tecnica proposta (ovvero lasciare che sia SQL Server a decidere i nomi degli indici) è quella che funziona e risolve il tuo problema di conflitti di creazione indici. 

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    martedì 29 gennaio 2013 11:21

Tutte le risposte

  • Ciao Lullo,

    Le tue tabelle temporanee sono locali o globali? 

    Se sono a livello di server, puoi gestirla creando una tabella ed un indice soltanto, checkando prima se esiste già nel sistema (così non provi a crearne una uguale) e usando un campo che definisce l'appartenenza del set di dati ad un utente piuttosto che ad un altro.

    Creandole come tabelle temporanee locali non si risolve il problema dell'indice? Queste tabelle nascono e muoiono nella stessa stored? Se si, potresti usare delle table variable, a me hanno risolto un sacco di problemi quando dovevo elaborare grosse quantità di dati.

    Un'esempio di uso delle table variable è :

     DECLARE @Users TABLE
        (
            UserID  INT PRIMARY KEY,
            UserName VARCHAR(50),
            FirstName VARCHAR(50),
            UNIQUE CLUSTERED (UserName,UserID)
        )

    Un saluto

    venerdì 25 gennaio 2013 10:59
  • Ciao Luca,

    purtroppo il mio problema a livello performance usando la variabile al posto che la temporanea persiste.

    La temporanea era locale:

    CREATE TABLE #FDAMaster

    ( [numero] [int] NOT NULL , [oda] [nvarchar](50) NOT NULL , [anno] [int] NOT NULL , [mese] [int] NOT NULL , [idxda_min] [int] NULL , [idxda] [nvarchar](500) NULL , [stato_id] [int] NOT NULL , [stato] [nvarchar](50) NULL , [fornitore_id] [int] NULL , [fornitore] [nvarchar](250) NULL , [richiedente_id] [int] NULL , [richiedente] [nvarchar](150) NULL , [responsabile_id] [int] NULL , [responsabile] [nvarchar](150) NULL , [periodo] [nvarchar](150) NULL , [data_approvato] [datetime] NULL , [importo_totale] [decimal](38, 4) NOT NULL

    ,CONSTRAINT [PK_#FDAMaster] PRIMARY KEY CLUSTERED ( [numero] ASC )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) );

    E viene droppata in fondo alla SP

    venerdì 25 gennaio 2013 12:09
  • Si, quando parli di tabelle indicizzate in effetti le performance sono migliori nelle tabelle temporanee quando hai dei set di dati grandi.. in ogni caso, ho appena fatto un paio di prove ed in effetti l'indice viene duplicato, però hai un modo per ovviare al problema, riscrivendo la query così :

    CREATE TABLE #FDAMaster
    (
    	[numero] [int] NOT NULL PRIMARY KEY WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    	[oda] [nvarchar](50) NOT NULL , 
    	[anno] [int] NOT NULL, 
    	[mese] [int] NOT NULL, 
    	[idxda_min] [int] NULL , 
    	[idxda] [nvarchar](500) NULL , 
    	[stato_id] [int] NOT NULL , 
    	[stato] [nvarchar](50) NULL , 
    	[fornitore_id] [int] NULL , 
    	[fornitore] [nvarchar](250) NULL , 
    	[richiedente_id] [int] NULL , 
    	[richiedente] [nvarchar](150) NULL , 
    	[responsabile_id] [int] NULL , 
    	[responsabile] [nvarchar](150) NULL , 
    	[periodo] [nvarchar](150) NULL , 
    	[data_approvato] [datetime] NULL , 
    	[importo_totale] [decimal](38, 4) NOT NULL
    );
    

    SQL Server assegnerà automaticamente un nome al tuo indice evitando che si diano conflitti, già testato e funzionante quà in ufficio, stesso utente SQL, sessioni diverse, stessa query lanciata con creazione indice.. ;)

    Un saluto

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:27
    venerdì 25 gennaio 2013 13:49
  • Grazie,

    questo escamotage sembra funzionare a livello conflittualità nome indici.

    Peccato che ho altri problemi in termini di performance.

    venerdì 25 gennaio 2013 14:46
  • Veramente le clausole utilizzate sono le stesse, ho solo omesso il nome dell'indice dichiarandolo "in line" sul campo, ho omesso "CLUSTERED" e "ASC" per il fatto che una primary key ad un campo è sempre clustered e l'ordinamento di default è ASC.

    Controlla che il tuo problema non sia dovuto al query optimizer che deve ricostruire il piano di query quando la esegue, oppure se vuoi prima dichiari la tabella temporanea e poi usi la SP_ExecuteSQL per creare un indice con nome dinamico.

    Comunque se vuoi, puoi anche inserire "CLUSTERED" in questa maniera.. il tuo Data Execution Plan dov'è che indica il carico maggiore?

    CREATE TABLE #FDAMaster
    (
    	[numero] [int] NOT NULL PRIMARY KEY  CLUSTERED WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    	[oda] [nvarchar](50) NOT NULL , 
    	[anno] [int] NOT NULL, 
    	[mese] [int] NOT NULL, 
    	[idxda_min] [int] NULL , 
    	[idxda] [nvarchar](500) NULL , 
    	[stato_id] [int] NOT NULL , 
    	[stato] [nvarchar](50) NULL , 
    	[fornitore_id] [int] NULL , 
    	[fornitore] [nvarchar](250) NULL , 
    	[richiedente_id] [int] NULL , 
    	[richiedente] [nvarchar](150) NULL , 
    	[responsabile_id] [int] NULL , 
    	[responsabile] [nvarchar](150) NULL , 
    	[periodo] [nvarchar](150) NULL , 
    	[data_approvato] [datetime] NULL , 
    	[importo_totale] [decimal](38, 4) NOT NULL
    );


    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    venerdì 25 gennaio 2013 14:56
  • Ciao Lullo73,

    per capire i tuoi problemi di performance però, servirebbe capire anche un po' di più il tuo modello. E soprattutto vedere il piano di esecuzione. Perchè tu hai dato per scontato che la temp sia la soluzione (e credo che possa essere effettivamente una buona soluzione in tanti casi) però leggendo il piano si capiscono tante cose.

    Magari basta cambiare un pochettino approccio, che ne dici di passarci il piano di esecuzione della tua query?

    ciao


    Alessandro Alpi SQL Server MVP

    martedì 29 gennaio 2013 08:41
    Moderatore
  • Ciao Alessandro,

    alla fine, in effetti, ho risolto il problema stringente creando degli indici ad hoc sulle tabelle sottostanti, anche se non sono convintissimo che a tendere (crescita del db) sia sufficiente.

    Purtroppo con il cliente che seguo in questo momento è da tempo che cerchiamo di cambiare approccio ma.....

    Trovo comunque strano che Microsoft non abbia pensato/risolto quello che comunque ritengo una problematica. Cerco di spiegarmi meglio:

    secondo me non è possibile che, creando degli indici su una temporanea ad esempio #tb, che nel flusso di esecuzione di una sp viene rinominata da sql server ad esempio in #tb______dlffjflkf e creando un indice su tale tabella (#tb) anche l'indice non venga rinominato.

    Ci fosse ad esempio la possibilità di assegnare all'indice un nome tramite una variabile il problema sarebbe risolto. Invece è obbligatorio specificare una costante.

    Un collega mi ha suggerito di creare l'indice con una esecuzione runtime (creare il comando da eseguire tramite una variabile (@variabile ed eseguirlo con EXECUTE (@variabile).

    Personalmente non mi fa impazzire questa soluzione anche perchè in questo modo il piano di esecuzione viene costruito ogni volta.

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 10:20
    • Contrassegno come risposta annullato Lullo73 martedì 29 gennaio 2013 14:26
    martedì 29 gennaio 2013 10:19
  • Ciao,

    forse intendi la primary key? In generale i constraint hanno nome univoco, quindi non te lo fa fare. Capisco la tua perplessità.

    Però, parlando di indici, uno script come il seguente va, e l'indice ha lo stesso nome:

    CREATE TABLE #tb (id int, valore varchar(10))
    GO
    
    CREATE INDEX IX_tb_1 ON #tb (id);
    GO
    
    CREATE INDEX IX_tb_2 ON #tb (valore);
    GO

    Quindi in realtà sono i constraint, non gli indici. L'unico modo per farlo senza problemi e senza EXEC è non dare nomi al constraint.


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    martedì 29 gennaio 2013 10:32
    Moderatore
  • Veramente mi pare che ti sono stati forniti 3 modi per risolvere il tuo problema.. volendo si può aggiungere anche la rename dinamica dell'indice fatta da te col seguente codice (da verificare però sulle tabelle temporanee, in questo momento non posso provarlo) :

    USE AdventureWorks2012;
    GO
    EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';
    GO

    In ogni caso, leggendo anche in giro l'esperienza di altre persone, la prima tecnica proposta (ovvero lasciare che sia SQL Server a decidere i nomi degli indici) è quella che funziona e risolve il tuo problema di conflitti di creazione indici. 

    • Contrassegnato come risposta Lullo73 martedì 29 gennaio 2013 14:24
    martedì 29 gennaio 2013 11:21
  • Grazie a tutti.

    Penso che i problemi riscontrati con quanto suggerito possano essere completamente risolti.

    Grazie a tutti

    martedì 29 gennaio 2013 14:26
  • Ottimo così.. (:

    martedì 29 gennaio 2013 15:06