Quand est-il préférable d'écrire du "sql ad hoc" plutôt que des procédures stockées ?

Question

J'ai 100% de sql ad hoc dans toute mon application. Un de mes amis m'a recommandé de passer aux procédures stockées pour obtenir des performances et une sécurité accrues. Cela a soulevé une question dans mon esprit, à part la vitesse et la sécurité, y a-t-il une autre raison de s'en tenir aux requêtes sql ad hoc ?

Answer 1

Le serveur SQL met en cache les plans d'exécution des requêtes ad hoc, de sorte que (si l'on fait abstraction du temps pris par le premier appel) les deux approches seront identiques en termes de vitesse.

En général, l'utilisation de procédures stockées signifie prendre une partie du code nécessaire à votre application (les requêtes T-SQL) et la placer dans un endroit qui n'est pas sous contrôle de la source (il puede être, mais généralement n'est pas ) et où il peut être modifié par d'autres personnes à votre insu.

Avoir les requêtes dans un endroit central comme celui-ci mai être une bonne chose, selon le nombre d'applications différentes qui doivent accéder aux données qu'ils représentent. Je trouve généralement qu'il est plus facile de conserver les requêtes utilisées par une application dans le code de l'application elle-même.

Au milieu des années 1990, la sagesse conventionnelle disait que les procédures stockées dans SQL Server étaient la solution pour les situations critiques en termes de performances, et à l'époque, elles l'étaient vraiment. Cependant, les raisons qui sous-tendent cette CW ne sont plus valables depuis longtemps.

Mise à jour : De même, dans les débats sur la viabilité des procédures stockées, la nécessité d'empêcher les injections SQL est souvent invoquée pour défendre les procédures. Il est certain que personne de sensé ne pense que l'assemblage de requêtes ad hoc par concaténation de chaînes de caractères est la bonne chose à faire (bien que cela ne vous expose à une attaque par injection SQL que si vous concaténer entrée utilisateur ). Il est évident que les requêtes ad hoc doivent être paramétrées, non seulement pour éviter le monstre sous le lit d'une attaque par injection sql, mais aussi pour vous faciliter la vie en tant que programmeur (à moins que vous n'aimiez savoir quand utiliser des guillemets simples autour de vos valeurs).

Mise à jour 2 : J'ai fait d'autres recherches. Sur la base de ce livre blanc MSDN il semble que la réponse dépende de ce que vous entendez par "ad-hoc" avec vos requêtes, exactement. Par exemple, une simple requête comme celle-ci :

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5

... se a son plan d'exécution en cache. De plus, comme la requête ne contient pas certains éléments disqualifiants (comme presque tout ce qui n'est pas un simple SELECT d'une table), le serveur SQL va en fait "auto-paramétrer" la requête et remplacer la constante littérale "5" par un paramètre, et mettre en cache le plan d'exécution de la version paramétrée. Cela signifie que si vous exécutez ensuite este requête ad-hoc :

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 23

... il sera en mesure d'utiliser le plan d'exécution mis en cache.

Malheureusement, la liste des éléments de requête disqualifiant pour l'auto-paramétrage est longue (par exemple, oubliez l'utilisation de DISTINCT , TOP , UNION , GROUP BY , OR etc.), vous ne pouvez donc pas vraiment compter sur cela pour les performances.

Si vous avez une requête "super complexe" qui ne sera pas auto-paramétrée, par exemple :

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5 OR ITEM_COUNT < 23

... il sera toujours mis en cache par le texte exact de la requête, donc si votre application appelle cette requête avec les mêmes valeurs littérales "codées en dur" à plusieurs reprises, chaque requête après la première réutilisera le plan d'exécution mis en cache (et sera donc aussi rapide qu'une proc stockée).

Si les valeurs littérales changent (en fonction des actions de l'utilisateur, par exemple, comme le filtrage ou le tri des données visualisées), les requêtes ne bénéficieront pas de la mise en cache (sauf occasionnellement lorsqu'elles correspondent exactement à une requête récente).

La façon de bénéficier de la mise en cache avec des requêtes "ad-hoc" est de les paramétrer. Créer une requête à la volée en C# comme ceci :

int itemCount = 5;
string query = "DELETE FROM tblSTUFF WHERE ITEM_COUNT > " + 
        itemCount.ToString();

est incorrect. La manière correcte (en utilisant ADO.Net) serait quelque chose comme ceci :

using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlCommand com = new SqlCommand(conn);
    com.CommandType = CommandType.Text;
    com.CommandText = 
        "DELETE FROM tblSTUFF WHERE ITEM_COUNT > @ITEM_COUNT";
    int itemCount = 5;
    com.Parameters.AddWithValue("@ITEM_COUNT", itemCount);
    com.Prepare();
    com.ExecuteNonQuery();
}

La requête ne contient aucun littéral et est déjà entièrement paramétrée, de sorte que les requêtes suivantes utilisant la même déclaration paramétrée utiliseront le plan mis en cache (même si elles sont appelées avec des valeurs de paramètres différentes). Notez que le code ici est pratiquement le même que celui que vous utiliseriez pour appeler une procédure stockée de toute façon (la seule différence étant le CommandType et le CommandText), donc tout dépend de l'endroit où vous voulez que le texte de cette requête "vive" (dans votre code d'application ou dans une procédure stockée).

Enfin, si par requêtes "ad hoc" vous entendez que vous construisez dynamiquement des requêtes avec différentes colonnes, tables, paramètres de filtrage et autres, comme par exemple celles-ci :

SELECT ID, DESC FROM tblSTUFF WHERE ITEM_COUNT > 5

SELECT ID, FIRSTNAME, LASTNAME FROM tblPEEPS 
    WHERE AGE >= 18 AND LASTNAME LIKE '%What the`

SELECT ID, FIRSTNAME, LASTNAME FROM tblPEEPS 
    WHERE AGE >= 18 AND LASTNAME LIKE '%What the`
    ORDER BY LASTNAME DESC

... alors vous avez à peu près ne peut pas faire cela avec des procédures stockées (sans le EXEC qui ne doit pas être mentionné dans la société polie), donc le point est discutable.

Mise à jour 3 : Voici le seul vrai bon lié à la performance raison (à laquelle je peux penser, en tout cas) d'utiliser une procédure stockée. Si votre requête est longue et que le processus de compilation du plan d'exécution prend beaucoup plus de temps que l'exécution proprement dite, et que la requête n'est appelée que rarement (comme un rapport mensuel, par exemple), le fait de la placer dans une procédure stockée pourrait inciter SQL Server à conserver le plan compilé dans le cache suffisamment longtemps pour qu'il soit encore disponible le mois prochain. Je ne sais pas si c'est vrai ou non, cependant.

Answer 2

Il n'y a rien dans les procédures stockées qui les rende magiquement plus rapides ou plus sûres. Dans certains cas, une procédure stockée bien conçue peut être plus rapide pour certains types de tâches, mais l'inverse est également vrai pour le SQL ad hoc.

Codez de la manière qui vous semble la plus productive.

"Faites-le bien avant de le faire plus vite." -- Brian Kernighan

Answer 3

Si vous n'écrivez pas de procédures stockées, étudiez requêtes paramétrées . Si vous construisez vous-même le SQL, y compris la concaténation des paramètres, vous invitez une Attaque par injection SQL .

Answer 4

Il existe quelques mythes liés à ce sujet dont vous devriez vous défaire :

Mythe 1 : les procédures stockées sont précompilées
http://scarydba.wordpress.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

Mythe 2 : Les requêtes SQL ad hoc ne réutilisent pas les plans d'exécution : http://scarydba.wordpress.com/2009/10/05/ad-hoc-queries-dont-reuse-execution-plans-myth-or-fact/

À mon avis, les procs ont l'avantage lorsque vous avez absolument besoin de verrouiller la base de données. Dans ces situations, vous pouvez utiliser un compte qui n'a que les droits d'exécution des procédures stockées. En outre, ils peuvent fournir une couche d'abstraction entre votre application et la base de données du point de vue du DBA.

De même, le SQL dynamique est préférable dans les situations où la requête peut avoir besoin de changer un peu et être... eh bien... dynamique. Ou si vous savez que vous devez vous porter vers plusieurs bases de données.

Les deux sont aussi sûrs l'un que l'autre en ce qui concerne l'injection SQL, pour autant que toutes les valeurs saisies par l'utilisateur soient paramétrées.

Answer 5

J'ai 100% de sql ad hoc dans toute mon application. application. Un de mes amis m'a recommandé de passer à des procédures stockées pour les performances et la sécurité.

Je ne m'inquiéterais pas des performances tant qu'il n'y a pas de points douloureux réels. Par exemple, quelqu'un utilise votre application et se plaint qu'elle est lente. Jusqu'à ce que vous atteigniez ce point, il est préférable de consacrer votre temps à améliorer votre application.

En matière de sécurité, vous devez trouver un équilibre entre l'effort et le risque. Si votre site ne stocke rien de valeur, même l'injection SQL est un risque parfaitement acceptable, comme le prouve un grand nombre de sites Web :)