CommandType.StoredProcedure moins rapide que CommandType.Text. Où est l'erreur?

**Immobilis** · 13/12/2009, 00h29

Salut,

Je suis un peu abasourdi, pour ne pas diresur le c**.

J'ai récupéré les source de ce site pour voir un peu de LINQ ou SQL celui qui est le plus performant. Mes test correspondent bien à ce que trouve l'auteur, jusque là ça va. Dans son code il utilise des requetes SQL de type CommandType.Text. Je me suis dit se serait interessant de voir ce que ça donne avec des procédures stockées. Mais là catastrophe! Les procédures stockées seraient moins rapides sur un simple SELECT qu'une commande text!!

Afin de vous permettre de vous faire votre propre jugement je vous laisse les sources. J'ai peut-être fait des erreurs...

Telecharger et installer AdventureWorks

Ajouter à la base la procédure stockée suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE PROCEDURE uspSelectCustomers 
AS
BEGIN
 
    SELECT * FROM [AdventureWorks].[Sales].[Customer]
 
END
GO

Le programme à console executer:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml.XPath;
 
namespace RetrieveUsingADO
{
    class Program
    {
        private static string connectionstring = @"Server=NOM_MACHINE\MSSQL01;Database=AdventureWorks;user id=sa;password=;Connection timeout = 5 ;Pooling='false';";
 
        static void Main(string[] args)
        {
            displaySettings();
            Console.WriteLine("Executing GatherDataADORead");
            GatherDataADORead();
        } // end of main
 
        # region GatherData
        public static void GatherDataADORead()
        {
            using (DataSet dataSet = new DataSet())
            {
                using (DataTable dt = new DataTable("ADORead"))
                {
                    dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
                    dt.Columns.Add(new DataColumn("SP", typeof(Int64)));
                    dt.Columns.Add(new DataColumn("TEXT", typeof(Int64)));
                    dataSet.Tables.Add(dt);
 
                    DataRow dr;
                    Stopwatch sw = new Stopwatch();
                    for (int i = 0; i <= 500; i++)
                    {
                        dr = dt.NewRow();
                        dr[0] = i;
                        sw.Start();
                        ADOReadingStoredProc();
                        dr[1] = ((long)(sw.ElapsedTicks / 100));
                        sw.Reset();
                        sw.Start();
                        ADOReadingText();
                        dr[2] = ((long)(sw.ElapsedTicks / 100));
                        sw.Reset();
                        dt.Rows.Add(dr);
                    } //end of for
                }
                dataSet.WriteXml(string.Format("c:\\temp\\{0}_ADORead.xml", DateTime.Now.ToString("yyyy-MM-ddThh-mm-ss")));
            }
        }
 
        # endregion GatherData
 
        # region ActualFunctions
        /// <summary>
        /// This function reads from customer table using ado and then sums up the values of the first column.
        /// </summary>
        private static void ADOReadingText()
        {
            using (SqlConnection Conn = new SqlConnection(connectionstring))
            {
                Conn.Open();
 
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM [AdventureWorks].[Sales].[Customer]", Conn))
                {
                    cmd.CommandType = CommandType.Text;
                    double i = 0;
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            i = i + Convert.ToDouble(dr[0]);
                        }
                    }
                }
            }
        }//end ADO Reading
 
        private static void ADOReadingStoredProc()
        {
            using (SqlConnection Conn = new SqlConnection(connectionstring))
            {
                Conn.Open();
 
                using (SqlCommand cmd = new SqlCommand("uspSelectCustomers", Conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    double i = 0;
                    using (SqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            i = i + Convert.ToDouble(dr[0]);
                        }
                    }
                }
            }
        }
 
        # endregion ActualFunctions
 
        # region Helpers
        private static void displaySettings()
        {
            if (Stopwatch.IsHighResolution)
            {
                Console.WriteLine("Operations timed using the system's high-resolution performance counter.");
            }
            else
            {
                Console.WriteLine("Operations timed using the DateTime class.");
            }
 
            long frequency = Stopwatch.Frequency;
            Console.WriteLine("  Timer frequency in ticks per second = {0}",
                frequency);
            long nanosecPerTick = (1000L * 1000L * 1000L) / frequency;
            Console.WriteLine("  Timer is accurate within {0} nanoseconds",
                nanosecPerTick);
        }
        # endregion Helpers
    }
}

Analyser avec Excel les fichiers Xml générés dans le repertoire temp. Attention l'unité de mesure du temps (Axe y) est en ElapsedTicks. Cela ne gène pas la comparaison.

D'avance merci de votre analyse.

A+

**MetalGeek** · 13/12/2009, 10h03

Salut,

il me semble que c'est normal, par contre sur plusieurs exécutions d'affilée la procédure stockée, mise en cache par la BD, gagne en perfs.

**AndreiS** · 13/12/2009, 10h25

En appelant une requête avec la classe SqlCommand, la base de données va exécuter la procédure stockée 'sp_executesql' qui met le Transact SQL produit pour la requête en cache. Je ne sais pas pour quoi la méthode avec une procédure stockée est moins rapide mais ça explique pour quoi les deux méthodes nécessitent des temps similaires.

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Pour un autre test tu peut appeler ADOReadingStoredProc 500 fois à la suite après 500 appels à ADOReadingText

**Immobilis** · 13/12/2009, 12h51

Envoyé par MetalGeek

Salut,

il me semble que c'est normal, par contre sur plusieurs exécutions d'affilée la procédure stockée, mise en cache par la BD, gagne en perfs.

Normal? Je me suis toujours laissé dire que les SP étaient plus rapides. De plus, la SP reste toujours moins performante à la requete texte... Bizarre non?

En PJ j'ai ajouter des données de LINQ. J'ai pris soin de retirer les données de l'execution de la première requête qui est tellement peu performante qu'elle prend trop d'importance par rapport au reste du graphique et le rend illisible.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
  <Read>
    <Pass>0</Pass>
    <SP>42912</SP>
    <TEXT>14228</TEXT>
    <LINQ>39435</LINQ>
  </Read>

A+

**Immobilis** · 13/12/2009, 14h41

Envoyé par AndreiS

Pour un autre test tu peut appeler ADOReadingStoredProc 500 fois à la suite après 500 appels à ADOReadingText

Test fait cf. image jointe. Resultat:

Text (meilleurs temps)
SP (temps plus constants)
LINQ (sans commentaires... )

Premiere execution toujours lente:

/Read/TEXT 32401
/Read/SP 39313
/Read/LINQ 44926

A+

**Immobilis** · 15/12/2009, 19h57

Les tests ont été réalisé sur mon PC perso.
Je les ai donc refait dans l'environnement du boulot sur un serveur web séparé du serveur de base de données.
Le résultat indique que la procédure stockée est légèrement plus rapide. Ouf!
Peut-être que cette différence s'accentue quand les requêtes deviennent plus complexes.

Par contre, je confirme que LINQ est plus lent. Il y a cependant des aspects que je n'ai pas encore tésté:

Requete LINQ précompilées
Requête LINQ sur une procédure stockée (utilisation du contexte de données simplement).

A tester.

A+

**Immobilis** · 18/12/2009, 01h00

J'ai testé les requête précompilées. Les performance sont très proches de celles des requête texte et procédures stockées. Un conseil n'utilisez pas les requêtes LINQ non compilées. Et gardez un oeil sur le nombre de DataContext que vous instanciez. Cela allonge le temps de traitement.

A+

**WOLO Laurent** · 30/06/2011, 14h33

C'est vrai que ca date de 2009 mais ma rigueur me conduit à vous demander de toujours acetiver le pool de connexion :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
private static string connectionstring = @"Server=NOM_MACHINE\MSSQL01;Database=AdventureWorks;user id=sa;password=;Connection timeout = 5 ;Pooling='true';";

Et l'usage du compte sa sans mot de passe même si c'est seulement sur ton portable, tu connais les dégats que ça peut engendrer !

**Immobilis** · 30/06/2011, 14h51

Envoyé par WOLO Laurent

C'est vrai que ca date de 2009 mais ma rigueur me conduit à vous demander de toujours acetiver le pool de connexion

En .Net il est toujours activé par défaut. Pour ce qui est du mot de passe qu'est-ce qui te dit que je n'en ai pas mis un puis simplement retiré?

Pour plus de sécurité tu devrais plutôt suggérer d'utiliser la sécurité intégrée. Tout du moins, suggérer l'utilisation d'un autre compte que "sa".

**WOLO Laurent** · 30/06/2011, 15h39

Envoyé par Immobilis

En .Net il est toujours activé par défaut. Pour ce qui est du mot de passe qu'est-ce qui te dit que je n'en ai pas mis un puis simplement retiré?

Pour plus de sécurité tu devrais plutôt suggérer d'utiliser la sécurité intégrée. Tout du moins, suggérer l'utilisation d'un autre compte que "sa".

Rien justement ne le dira a un lecteur et la personne peut recopier simplement et coller ce code croyant que ca ne pose aucun problème !

Envoyé par Immobilis

Pour plus de sécurité tu devrais plutôt suggérer d'utiliser la sécurité intégrée. Tout du moins, suggérer l'utilisation d'un autre compte que "sa".

En fait c'est ce que j'allais te dire mais j'ai voulu faire d'une pierre deux coups en faisant remarquer ton compte sa sans mot de passe

**Immobilis** · 30/06/2011, 15h57

Envoyé par WOLO Laurent

croyant que ca ne pose aucun problème !

Il faut que ces personnes commencent à réfléchir. Il n'y a rien de plus dangereux que le copié collé.

CommandType.StoredProcedure moins rapide que CommandType.Text. Où est l'erreur?

ASP.NET

Discussions similaires

Partager

Partager