Discussion :

[Matt_NewDev]

Bonjour,

Je suis sur un projet qui me demande de créer des procédures stockés.
Mon Pb est le suivant:
J'ai créer une procédure qui demande un temps d'exécution mini de14s pour 1000 valeurs.
cette procédure sera exécuté sur plus de 600 000 valeurs. ce qui va me donner un temps d'exécution faramineux.
Comment puis je procéder pour optimiser au mieux cette procédure.
y a t il des outils qui permettent d'optimiser ?

Merci d'avance.

[Christianbt]

Bonjour,

avez vous déjà essayé de lancer un plan d'exécution ou d'utiliser le DTA (database tuning advisor) ?

Christian

[Matt_NewDev]

Merci de votre reponse

Je débute un peu en ce qui concerne les procédure sql.
Je ne connais pas ces choses la.
je developpe sous Management studio.
comment fonctionne vos propositions ?

[soazig]

Bonjour,
Est-ce que les colonnes sur lesquels portent les filtres sont indéxés.
Est-ce que les foreigns key sont indéxés.
Est-ce que les critères de ton where sont sargable
Voir icihttp://sqlpro.developpez.com/cours/quoi-indexer/

Si tu veux qu'on t'aide à optimiser ta procédure stockée, il va falloir que tu nous donnes plus d'informations.
Ton modèle de données, ta procédure stockée, les index, la volumétrie.

Parce que sinon c'est un peu vague comme question.
PS : Database tuning advisor plus d'information http://blog.developpez.com/mikedavem...itaire-dta-ex/

Pour le plan d'execution c'est ici ou là
A+
Soazig

[Christianbt]

Bonsoir,

il est en effet nécessaire de lire tous ces articles, plus la documentation en ligne de SQL Server si nécessaire. Merci Soazig pour tous ces liens
Si vous regardez le plan d'exécution, déjà vous aurez une indication sur les index manquants, il faut apprendre à lire un plan d'exécution, d'où les aides en ligne. Sinon le DTA le fera pour vous si vous ne maîtrisez pas encore la création d'index en fonction de votre code. Examinez bien ce qu'il vous propose comme solution.

Vous pouvez en effet nous envoyer votre code et votre schéma de données afin que l'on puisse mieux vous aider là dessus.

Christian

[Invité]

J'ajouterais aussi que l'emploi de curseur est souvent très pénalisante dans un procédure...

[Matt_NewDev]

Bonjour,

Merci de votre aide a tous.
en reponse un peu a soazig

Est-ce que les colonnes sur lesquels portent les filtres sont indéxés.
-> non
Est-ce que les foreigns key sont indéxés.
-> je ne pense pas
Est-ce que les critères de ton where sont sargable
-> si j'ai bien compris les articles, non plus.

en effet je n'ai pas creer la base de donnée, ni les table sur lesquelles je m'attaque.
Et je ne pense pas pouvoir modifier quoique ce soit sur ces table.

concernant mes nombreuse clause where, elle se constitu generalement sur un substring. en plus de ca, je fait un count sur toute mes requete.
pour plus de precision, je vais (essayer de) faire une explication sur ma requete.

- ma premiere table est composer (sur ce qui m'interresse) d'une colonne idclient puis plusieurs colonnes sur des resultat de questionnaire. ces resultat sont interpreter par des 0 et 1 (oui ou non)

1 : ma premiere requete consiste a compter le nombre de 0 et sur chaque ligne et prendre le max :
select Max (len (col1 + col2 ....+ colx))

le nombre donnée me donne le nombre d'iteration (nb de question) pour lequel je vais compter le nombre de reponses de chaque.

2 : sur chaque question, je fait un comte de ceux qui on repondu a la question

vaguement ca fait un
select count(idclient)
from db_client inner join db_questionnaire
on db_client.idclient = db_questionnaire.idclient
Where (une condition)

3 : j'insere (ou plutot update) une table temporaire pour exploiter le resultat plus tard.

4 : apres la fin de literation je fait juste un select sur la table temporaire

Voila, j'espere que mes explication ont ete clair.
et que quelqu'un pourras m'aider.

Merci d'avance.

[Invité]

ça serait pas plus simple de nous copier la procédure au lieu de nous l'expliquer?

[Matt_NewDev]

Je comprend et je m'attendais a cette demande.
Mais je n'ai pas le droit de sortir d'information de ma boite.
Et comme je viens de commencer mon nouvelle emploi, je ne veux pas m'y risquer.
Merci de votre compréhension.
plus ou moins graphiquement j'ai la table suivante :

idclient | quesiton | age | ville
client1 | 0100101110101101 | 12 |
client2 | 0100010011001101 | 32 |
client3 | 1001010110101101 | 25 |
client4 | 0101001101011101 | 45 |

et je veux savoir combien on un 1 en debut de chaine, combien on un 1 en 2e position de chaine ...
sachant que je faire des Where sur l'age, puis la ville ...

[soazig]

Bonjour,
Vu les réponses aux questions relatives aux index.
Avec une volumétrie plus importantes, les performances vont se dégrader et ta PS mettra plus de temps. Puisque tu as lu les tuto, tu as compris pourquoi

Si on t'interdit de créer des index sur les tables, alors comme nous ne sommes pas magicien nous ne pouvons pas t'aider. Cela dit commence par demander si c'est possible ou pas, car cela me surprend grandement.

Cordialement
Soazig

[Matt_NewDev]

Merci pour vos réponses.

Je vais voir pour les index, mais je ne suis pas sur qu'il vont modifier les structures des tables pour une PS qui va servir que pour 1 mois ou 2.
Je pense qu'ils vont se débrouiller avec que je leurs ai fourni et il vont attende que la BDD crache le résultat.
Ma seule crainte est un time out du server de BDD.
On verra bien.
si quelqu'un pense a une solution miracle, je vais consulter mon poste régulièrement.
en tout cas ca m'a fait découvrir les plan d'exécution, reste plus qu'a comprendre les résultats.
Merci encore.

[elsuket]

Bonjour,

J'ajouterais aussi que l'emploi de curseur est souvent très pénalisante dans un procédure...

Moi je dirai que ça l'est dans tous les cas

Je vais voir pour les index, mais je ne suis pas sur qu'il vont modifier les structures des tables pour une PS qui va servir que pour 1 mois ou 2.

Justement : l'avantage des index est qu'ils ne nécessitent aucune modification de code ni de structure des tables.
Ils aident le moteur de base de données à trouver les lignes qui répondent à votre requête de façon très rapide.

Pour vous donner une idée, il y a quelques temps j'ai ajouté un index sur une base de données pour un jeu de requêtes, dont l'une d'entre-elles lisait plus de 20 millions de pages.
Après ajout de l'index et sans modifier la requête ni la valeur de ses paramètres, elle en lit 400

Ensuite si comme vous le dites les colonnes qui servent de filtre à votre requête ne sont pas indexées, ni les colonnes supportant les clés étrangères, il n'est pas étonnant que vous ayez de mauvaises performances.
Les clés étrangères doivent toujours être indexées, car cela facilite les jointures
Les autres colonnes doivent être indexées suivant les requêtes, et plus particulièrement les filtres spécifiés dans celles-ci
Lisez donc le tutoriel de SQLPro, il est très bien fait et couvre votre problème.

Ma seule crainte est un time out du server de BDD.

Il n'y a pas de time-out de requête.
Si votre requête doit mettre 5 ans à s'exécuter, SQL Server l'exécutera pendant 5 ans

concernant mes nombreuse clause where, elle se constitu generalement sur un substring

Cela ne peut pas être rapide.
L'optimiseur de requêtes de SQL Server est un optimiseur basé sur les coûts.
Donc lorsqu'il compile une requête, il recherche le plan d'exécution qui coûte le moins cher en ressources physiques.
Ce calcul est en grande partie basé sur les statistiques de répartition des valeurs dans les colonnes, de telle sorte que le moteur de base de données "sait" approximativement combien de valeurs dans les colonnes dont se sert votre requête correspondent à ce que vous cherchez.
Or en ce qui concerne les chaînes de caractère, et si vous êtres sous SQL Server 2005 ou ultérieur, ces statistiques sont collectées sur les 40 premiers et 40 derniers caractères de chaînes qui sont contenues dans une colonne ... mais pas sur les caractères qu'elles contiennent ! (imaginez le boulot !)

Nous ne savons pas encore comment les valeurs de votre colonnes question sont insérées dans votre table, mais il vous reste quelques possibilités :

- Exploser cette colonne en autant de colonnes que de chiffres (utilisez pour ce faire le type bit, très petit, coût d'indexation très faible), et dans la procédure d'import, prévoir ce changement qui peut être assez coûteux.

- Extraire les chiffres de cette chaîne à l'aide de colonnes calculées que vous indexerez par la suite, ce qui facilitera l'accès aux données répondant à vos requêtes

- Développer un package SQL Server Integration Services pour importer les données dans votre table et exploser la colonne question en autant de colonnes que de chiffres

- Créer une vue indexée qui extraie les valeurs pour vous et calcule vos COUNT. Un simple SELECT sur la vue vous donnera ce dont vous avez besoin, mais il faut considérer le coût de maintenance d'un tel objet.

sachant que je faire des Where sur l'age, puis la ville ...

Il vous faudrait donc indexer ces colonnes

Je peux vous donner le code pour les colonnes calculées ...

@++

[Matt_NewDev]

Bonjour,

J'ai fini par adopter la méthode suivante qui accélère relativement le traitement.
- Je créer une table temporaire avec toute mes informations nécessaires.
- je re-parcours cette table pour calculer (count, sum...) que j'insère dans une autre table temporaire.

je sais pas si c'est genial, mais ca marche relativement bien.

Merci a tous.