Problemi di performance nell'esecuzione di una SQL Query: stored procedure vs SQL nativo

[Luca Petrini]

Ciao a tutti,

Ho un problema di performance con l'esecuzione di una query SQL su NHibernate.

Ho fatto un test provando ad importare il codice SQL di una stored procedure come codice nativo direttamente esplicitato nel tag <sql-query>, riscontrando un notevole degrado di performance nell'esecuzione della query.

Ecco un esempio che esplicita il test che ho effettuato...

Nel file di mapping ho queste due definizioni:

<sql-query name="sp_Test1">
       <return-scalar column="Field5" type="Int32" />
      exec MyStoredProcedure :Param1, :Param2, :Param3, :Param4
</sql-query>

<sql-query name="sp_Test2">
       <return-scalar column="Field5" type="Int32" />
       <![CDATA[
          SELECT Field5 FROM MyTable WHERE Field1 = :Param1 AND Field2 = :Param2 AND Field3 = :Param3 AND Field4 = :Param4 ORDER BY Field5 
       ]]>
</sql-query>

Il codice sql di "sp_Test2" è esattamente lo stesso codice presente all'interno della stored procedure.

Ora, se io eseguo un test come questo:

int count = 2500;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
for (int i=0; i<=count; i++) { 
     //Esecuzione query "sp_Test1" 
} 
sw.Stop(); 
//Stampa sw.ElapsedMilliseconds 

sw.Reset(); 
sw.Start(); 
for (int i=0; i<=count; i++) { 
     //Esecuzione query "sp_Test2" 
}
sw.Stop();
//Stampa sw.ElapsedMilliseconds

inserendo la chiamata alle query all'interno di un ciclo (in questo caso 2500 chiamate consecutive), ho questo risultato:

il ciclo di chiamate alla query sp_Test1 impiega meno di un secondo ad essere completato, mentre il ciclo di chiamate alla sp_Test2 impiega circa 20 secondi ad essere completato!

Qualcuno sa dirmi quale potrebbe essere la causa di questo degrado di performance? ...e se c'è un modo per ottimizzare l'esecuzione della query sp_Test2?

N.B. per fare questi test ho utilizzato NHibernate 2.1.2

Vi ringrazio in anticipo per ogni informazione utile a riguardo.

[ferro.to]

Ciao Luca,

penso che il problema non risieda direttamente in NH.

Però dato che la query diretta ti risulta più lenta della SP mi sorge il dubbio che si potrebbe trattare di un problema di tipologia dei parametri.

 

Ad es. in SQL Server se crei una query con un filtro su una colonna varchar e come valore fornisci un parametro nvarchar allora viene prima eseguita la conversione di tutti i valori in tabella in nvarchar e solo dopo viene eseguito il confronto, allungando notevolmente i tempi.

 

La prima cosa da fare è verificare il codice sql eseguito da Nh e il tipo di parametri che utilizzi, utilizzando l'opzione showsql e verificando i parametri passati.

In alternativa puoi attivare il profiler di SQL Server durante l'esecuzione del codice.

In entrambi i casi verifica da SQL Server Management Console l'execution plan e il costo delle singole operazioni.

 

Inoltre performance diverse tra SP e query possono anche essere dovute alla compilazione delle procedure ed al diverso utilizzo delle statistiche per decidere l'execution plan.

Fai conto che anche come viene richiamata una procedura può portare a execution plan diversi, se da ssms richiami una procedura indicando i parametri come costanti:

EXEC MyProc 'VALUE'

oppure se la invochi tramite delle variabili:

DECLARE @param AS VARCHAR()

SET @param = 'VALUE'

EXEC MyProc @param

puoi ottenere execution plan e performance diversi.

Quando NH richiama una procedura/query utilizza sempre i parametri (secondo caso).

 

Ciao

 

[Luca Petrini]

Capito, sei stato molto chiaro, appena possibile farò una verifica e riporterò gli esiti. Grazie per la risposta.

[Luca Petrini]

Sono riuscito a risolvere il mio problema cambiando il codice della query "sp_Test2", scrivendolo in questo modo:

<sql-query name="sp_Test2">
       <return-scalar column="Field5" type="Int32" />

       <![CDATA[
        DECLARE @Param1 nvarchar(30)
        DECLARE @Param2 nvarchar(30)
        DECLARE @Param3 nvarchar(30)
        DECLARE @Param4 smallint

        SET @Param1 = :Param1
        SET @Param2 = :Param2
        SET @Param3 = :Param3
        SET @Param4 = :Param4

        SELECT Field5 FROM MyTable WHERE Field1 = :Param1 AND Field2 = :Param2 AND Field3 = :Param3 AND Field4 = :Param4 ORDER BY Field5 

       ]]>
</sql-query>

scrivendo la query in questo modo non riscontro più i problemi di performance ed ho sostanzialmente gli stessi temi di esecuzione tra stored procedure e sql nativo.

On Friday, February 14, 2014 9:26:15 AM UTC+1, ferro.to wrote: