Discussion :

[Wells]

Bonjour à tous, j'ai une question à poser au niveau de la construction de requête dynamique.

Imaginer une page de liste avec un formulaire de recherche. Selon ce qui est sélectionné dans ce formulaire, on construit notre WHERE.

On appelle ensuite notre procédure stockée qui fait:

Code :

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

ecexute('SELECT * FROM TOTO WHERE (' + @requete + ')')

Ca marche très bien. Sauf que ca fou en l'air toute l'optimisation des clés et index de SQL. En effet, a cause du exécute, la requête est recalculé a chaque fois.

Résultat on se retrouve avec des performances pourris par ce système pourtant bien pratique.

La seule autre solution consisterais à faire passer à la requête les X paramètres du where et de gruger en faisant qq chose du genre:

Code :

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

WHERE ((toto=@toto or @toto=''))

Si qq'un voit qq chose de mieux, je suis donc à l'écoute car nous sommes actuellement dans une impasse.

[Iruddy]

Salut

j'ai peut être une idée.

On va prendre un exemple comme ca tu me dira si j'ai bien compris ou pas.

Imaginons un Formulaire de recherche avec deux textbox permettant la recherche d'un membre par nom ou prénom.

Ta requète va donc attendre deux paramètre.

@nom et @prenom.

Il n'est pas obligé de renseigner les deux champs.

Moi je procederais de la façon suivante.

Si mon champ nom est vide alors j'afecte -1 a la variable @nom sinon j'affecte la bonne valeur.

je fais la même chose avec le champ prénom et la variable @prenom.


Dans ma requète je procède ainsi :

Code :

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

1
2
3
SELECT * FROM membre
WHERE (nom = @nom OR @nom = -1)
AND (prenom = @prenom OR @prenom = -1)

Peut être il y at'il d'autre solutions, moi je procède ainsi en générale.

[SQLpro]

Beaucoup plus simple :

Code :

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

1
2
3
4
SELECT * 
FROM membre
WHERE nom = COALESCE(@nom, nom)
AND prenom = COALESCE(@prenom, prenom)

etc...

A +

[Iruddy]

En effet, la méthode de SqlPro est beaucoup plus simple. Pourquoi n'y ai je jamais pensé ?

@++

[Wells]

La solution en elle même pourrais être bonne. Le problème c'est que l'on appelle la procédure stockée X à partir de plusieurs pages.

Donc dans la page A on va avoir @nom, @prenom, etc....
Dans la page B on va avoir @adresse,@ville, etc....

Bref vous comprenez le soucis, on va vite se retrouver avec 50 champs possibles.

On ne peut pas s'amuser pour nos 300 procédures stockées à rentrer nos X paramètres possibles (sans compter qu'on peut en ajouter en route)

Donc ma question est: sql est il capable d'optimiser des requêtes lancées depuis le code vb. j'ai essayer de passer dans requêtes texte, avec des add params, mais ça donne rien de concluant.


Un autre soucis également avec cette méthode c'est qu'on à des choses plus complexe que de simples =. Du style:

champ like '%@op'
ou date beetwin @op and @po
ou (toto=@op or toto is null)
etc....

[SQLpro]

Une requête dynamique n'est pas plus ou moins bien optimisée qu'une requête statique.

La seule différence réside dans le fait de la persistance en cache des plans.

En 2005 vous pouvez opter pour une paramétrisation forcée au niveau de la base

Code :

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

1
2
ALTER DATABASE ...
SET PARAMETRIZATION = FORCED

(de mémoire)

A +

[Wells]

Le problème est qu'en faisant un execute('select.......'), aucune mise en cache ou optimisation est possible.
On est donc coincé avec des temps horribles.

J'ai essayer

Code :

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

ALTER DATABASE ... SET PARAMETERIZATION FORCED

mais sans voir aucune différence in fine.

[SQLpro]

aucune mise en cache ou optimisation est possible

Pour la mise en cache oui c'est pas bien bon. Pour l'optimisation c'est la même que la requête en dur !

Pour forcer la mise en cache utilisez sp_executesql.

A +

[Wells]

tu veux dire faire

Code :

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

sp_executesql('SELECT .....')

?