Consiglio performance Stored Procedure

di il
13 risposte

Consiglio performance Stored Procedure

Ciao a tutti
sto per mettere mano ad una stored procedure lunga ben 1500 righe per cercare di migliorare i tempi di calcolo; ho visto che ci sono un sacco di cursori, anche innestati, e pensavo di sostituirli perchè ho letto da qualche parte che questi oggetti possono usare molte risorse, anche se alla fine ciascun di questi ha come record una o due righe come risultato della select.
In alcuni casi quindi posso sostituire il cursore con una semplice query del tipo

SELECT TOP 1 @colona = colonnaT FROM Tabella Where ... order by id desc

ma in alcuni casi ho bisogno di gestire un record alla volta di una tabella, esempio:

DECLARE cmpCur CURSOR FOR
SELECT VL.IDNumber, VL.LaidType AS DispType, MT.Manufacturer, MT.WallThickness,
MR.HeatNumber, MR.Length
,VL.WeldID,VL.Component, VL.Extension, VL.Remarks, VL.Remarks2,VL.NDTExtension
FROM dbo.V_LAY_List VL
LEFT JOIN (dbo.MainRegistry MR INNER JOIN dbo.MaterialTypes MT ON MR.Component = MT.Component AND MR.MaterialType = MT.MaterialType)
ON VL.Component = MR.Component AND VL.IDNumber = MR.IDNumber AND VL.Extension = MR.Extension
WHERE VL.Location = @Location AND VL.DocNumber = @DocNumber
ORDER BY VL.LineItem ASC;

e cosi via ...
qui
http://blog.sqlauthority.com/2010/02/16/sql-server-stored-procedure-optimization-tips-best-practices/
si consiglia di usare un ciclo while ma mi sembra abbastanza complicato, che ne dite se usassi una tabella temporanea?? è meglio o peggio? o è meglio una tabella del DB creata al volo e poi rimossa?
ciao e grazie

13 Risposte

  • Re: Consiglio performance Stored Procedure

    Ciao karmo e benvenuto su IProgrammatori.
    Ho letto l'articolo che hai citato e contiene dei consigli che condivido assolutamente.
    Per quanto riguarda i cursori della tua stored procedure, ti consiglio di smontarli e dove hai bisogno di iterare usa le tabelle temporanee.

    Riguardo la lentezza delle stored procedure, devi comunque testare bene le singole query, a volte un indice sbagliato, query innestate e order by quando non sono necessari vanno a degradere le performance dell'elaborazione.

    Puoi usare SQL Profiler per testare i tempi di elaborazione e impatto sulla CPU delle query.

    Se hai bisogno di qualche consiglio fammi sapere.

    Un saluto
  • Re: Consiglio performance Stored Procedure

    Ciao toki e grazie per il suggerimento.
    ho trovato anche questa guida
    http://www.ibm.com/developerworks/data/library/techarticle/0306arocena/0306arocena.html
    che ritengo molto utile quando parla di tabelle temporanee, cursori con WITH UR e di come dichiarare/usarele variabili.
    domanda: nella mia sp dichiaro tantissime variabili una sotto l'altra, es:
    DECLARE @Doc_TotShiftLen DECIMAL(10,3);
    DECLARE @Doc_TotSectionLen DECIMAL(10,3);
    DECLARE @Doc_TotShiftWelds INTEGER;
    DECLARE @Doc_TotSectionWelds INTEGER;
    DECLARE @Doc_TotShiftAWelds INTEGER;
    DECLARE @Doc_TotSectionAWelds INTEGER;
    DECLARE @Doc_TotShiftARWelds INTEGER;
    DECLARE @Doc_TotSectionARWelds INTEGER;
    DECLARE @Doc_TotShiftCutWelds INTEGER;
    DECLARE @Doc_TotSectionCutWelds INTEGER;
    DECLARE @Doc_TotShiftComponents INTEGER;
    DECLARE @Doc_TotSectionComponents INTEGER;
    DECLARE @DOC_TotSubShiftComponents INTEGER;
    DECLARE @Doc_TotSubSectionComponents INTEGER;

    per ottimizzare è meglio dichiararle in un' unica riga? o è insiginificante?
  • Re: Consiglio performance Stored Procedure

    Aiuta qualcosa spezzare la stored procedure in + parti? 1500 righe per una sp forse sono un pò troppi, che dite?
  • Re: Consiglio performance Stored Procedure

    Ciao karmo,
    per quanto riguarda l'elaborazione è insignificante dichiarare le variabili in una sola riga o diverse.
    Personalmente le variabili le dichiaro cosi:
    DECLARE 
    @Doc_TotShiftLen DECIMAL(10,3)
    , @Doc_TotSectionLen DECIMAL(10,3)
    , @Doc_TotShiftWelds INTEGER
    Splittare la stored procedure in più parti non aiuta l'elaborazione ma ne aiuta sicuramente la comprensione da parte di uno sviluppatore che ci deve mettere le mani.

    Lavoro da 10 anni con SQL server e ne ho scritte di stored procedure... ma credimi una procedura da 1500 righe non è mai uscita dalle mie mani... al limite le ho spezzettate in più stored procedure e funzioni.

    Per quanto riguarda i tempi di eleborazione come ti dicevo nel post precedente devi considerare l'impatto delle singole query moltiplicato per il numero di volte che vengono eseguite (iterazioni).
  • Re: Consiglio performance Stored Procedure

    Grazie mille toki
    ma come faccio a capire i tempi di elaborazione di ciascuna query della sp? ho provato a far partire il SQL profiler ma ho notato che mi dice solo quanta CPU e quante read ha fatto.

    altra cosa che stavo considerando: se riesco a togliere i cursori allora è meglio usare una funzione piuttosto che la sp visto che alla fine popola una tabella e non ritorna nulla (anche se in realtà la funzione dovrebbe tornare ma devo migliorare i tempi ad ogni costo)?
    se invece devo usare per forza i cursori allora è meglio rimanere sulla stored procedure.
    concordi toki?
  • Re: Consiglio performance Stored Procedure

    Ciao le stored procedure non sono fatte solo per ritornare valori o resulset, puoi benissimo fare una stored procedure che fa operazioni di modifica dati senza restituire nulla.

    Per quanto riguarda il profiler vede la stored procedure come unica elaborazione, dovresti eseguire le singole query a parte in modo da testarle una ad una.

    Puoi in realtà visualizzare le statistiche di esecuzione delle operazioni, tramite l'istrazione SET STATISTICS TIME:
    SET STATISTICS TIME ON
    GO
    
    --Query di cui vuoi monitorare le statistiche
    
    SET STATISTICS TIME OFF;
    GO
    
    Fammi sapere
  • Re: Consiglio performance Stored Procedure

    Un'altro aiuto che ho trovato è questo
    http://www.sql-server-performance.com/tips/cursors_p1.aspx

    in cui si suggerisce di definire il cursore FAST_FORWARD per migliorarne performance e velocità.
  • Re: Consiglio performance Stored Procedure

    Si è vero migliora le performance.
    Comunque ti continuo a consigliare le tabelle temporanee, di rivedere le query innestate e le iterazioni.
  • Re: Consiglio performance Stored Procedure

    Ciao toki
    ho rimosso gran parte dei cursori e ora sono passato da 8:08 mn a ben 25 secondi! un bel guadagno!
    ora vedo di fare anche una bella pulizia.
    grazie dell'aiuto Toki! molto prezioso!!
  • Re: Consiglio performance Stored Procedure

    Un'ultima domanda: tu che editor di t-sql usi (meglio se freeware)?
    io ultimamente mi sto trovando meglio con il toad che è freeware e permette l'utilizzo delle region.
  • Re: Consiglio performance Stored Procedure

    Ciao, da 8 minuti a 25 secondi hai fatto davvero un ottimo lavoro

    Il Toad è sicuramente uno tra i migliori, io l'ho usato sia per Oracle che per MySQL.
    Per quanto riguarda T-sql non uso editor di terze parti, mi va benissimo il client Microsoft SQL Server Management Studio.

    Un salutone
  • Re: Consiglio performance Stored Procedure

    Ok come non detto, avevo un baco ... per questo ci metteva cosi poco!
    il tempo non è migliorato ... forse è diminuito di qualche secondo ma non di più.
    ho sostituito i cursori con una struttura di questo tipo
    
    CREATE TABLE #CompTotCur (
    CompType varchar(3)
    ,SubTot bit
    ,CompDescription varchar(50) )
    
    INSERT INTO #CompTotCur
    SELECT CP.Component, .... FROM Tabella
    
    SELECT @NumRowsCmpCur = @@ROWCOUNT 
    SET @CurrentNumCmpCur = 1
    
    IF @NumRowsCmpCur > 0
    BEGIN
    	SET @CicleCmpCur = 1
    END
    ELSE
    BEGIN
    	SET @CicleCmpCur = 0
    END
    SET @Custom_TotPresent = 0;
    
    while @CicleCmpCur = 1
    BEGIN
    SET @CompType = NULL
      SET @SubTot = NULL
      SET @CompDescription = NULL
      
      SELECT TOP 1 @CompType = CompType, @SubTot = SubTot, @CompDescription = CompDescription
    FROM #CompTotCur 
      
    -- fa qualcosa ...
    
       DELETE FROM #CompTotCur WHERE ISNULL(CompType,'') = ISNULL(@CompType,'') AND ISNULL(SubTot, 0) = ISNULL(@SubTot, 0) AND ISNULL(CompDescription,'') = ISNULL(@CompDescription ,'')
    
    SET @CurrentNumCmpCur = @CurrentNumCmpCur + 1
    IF @CurrentNumCmpCur > @NumRowsCmpCur
    BEGIN
    	SET @CicleCmpCur = 0
    END
    
    END -- end del ciclo 
    
    DROP TABLE #CompTotCur
    
    
    di solito questa tabella contiene dai 50 ai 200 record e per ciascun di questi fa diverse select, popola una tabella di SQL (che il prog legge a fine processo) e crea dalle 2 alle 10 tabelle temporanee usate per ciclare altre attività (sono i vecchi cursori).
    Sto sbagliando qualcosa?
  • Re: Consiglio performance Stored Procedure

    Ciao hai provato a monitorare con le istruzioni SET STATISTICS TIME
    quanto ci mettono le singole query?
Devi accedere o registrarti per scrivere nel forum
13 risposte