Optimisation pour les fonctions

Bonjour,

Je n'ai pas trouvé grand chose sauf un lien en anglais sur un Forum d'une
personne ayant le même soucis.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID130

--
Cordialement

Christian Robert
Consultant - Formateur chez Winwise
MCT - MCDBA - MCSD
MCTS & MCITP SQL Server 2005


"tintin" a écrit :

Bonjour,

Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
parait normal.

Si une requête absolument identique est utilisée par une fonction, et que je
lance cette fonction, je vois qu'elle traite la table séquentiellement
(Constant Scan)

Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
pratiquement impossible d'utiliser les fonctions pour extraire des données.

Avez-vous déja observé cela, ou bien est-ce que je délire ?

Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
paramètre ou autre réglage pour éviter cela ?

Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?

Merci pour vos avis.

Martin.

On Thu, 11 May 2006 03:16:01 -0700, tintin wrote:

Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
parait normal.

Si une requête absolument identique est utilisée par une fonction, et que je
lance cette fonction, je vois qu'elle traite la table séquentiellement
(Constant Scan)

Bonjour,

Pour élargir un peu la réponse, l'utilisation de fonctions peut vous coûter
en performance. Si vous avez créé une fonction, c'est probablement soit que
vous désirez un resultset en retour, soit que vous l'utilisez dans une
colonne d'un SELECT.
Si c'est le deuxième cas, insérer la requête dans la fonction comme
sous-requête de votre SELECT appelant va vous être bénéfique, même si cela
semble heurter vos principes de réutilisation du code. Parfois, encapsuler
du code dans une fonction va empêcher l'optimiseur de réagit en set de
données. Il va falloir appeler la fonction à chaque ligne du résultat du
SELECT appelant.

Plus particulièrement, en ce qui concerne votre cas, êtes-vous sûr que la
requête dans Query Analyzer, et celle dans la fonction sont les mêmes ? Y
a-t-il une clause WHERE ? utilisez-vous des paramètres envoyés à la
fonction dans votre clause WHERE ?
L'optimiseur prend la décision d'utiliser un index ou non selon son
estimation du nombre de lignes à retourner. S'il comprend qu'il doit
retourner un bon nombre d'enregistrements de la table, il peut décider
qu'un scan est moins coûteux que seek + bookmark lookups


---
Rudi Bruchez, MCDBA
http://www.babaluga.com/

tintin a écrit :

Bonjour,

Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
parait normal.

Si une requête absolument identique est utilisée par une fonction, et que je
lance cette fonction, je vois qu'elle traite la table séquentiellement
(Constant Scan)

Tout à fait normal. Mais cela dépend d'ou vous utiliser votre fonction.
Dans quelle clause de quel ordre SQL...

Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
pratiquement impossible d'utiliser les fonctions pour extraire des données.

Avez-vous déja observé cela, ou bien est-ce que je délire ?

Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
paramètre ou autre réglage pour éviter cela ?

Certaines fonctions peuvent d'ailleurs être transformées ou encapsulées
pour pouvoir bénéficier des recherches sur index.

Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?

Il ne s'agit pas d'un problème, mais d'un comportement normal.

Merci pour vos avis.

Martin.

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Non, je ne trouve ni normal, ni évident, qu'on ne puisse pas structurer sa
programmation.

Quand vous avez des centaines de procédures stockées qui recherchent la même
donnée, il est logique de rechercher à mettre en commun le traitement et on
pourait penser que les UDF sont faites pour cela.

Mais si leur efficacité "dépend" (on ne sait de quoi), les UDF sont
inutilisables en pratique.

La documentation devrait au moins avertir de ces restrictions.

Dommage.



"SQLpro [MVP]" a écrit :

tintin a écrit :
> Bonjour,
>
> Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
> dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
> parait normal.
>
> Si une requête absolument identique est utilisée par une fonction, et que je
> lance cette fonction, je vois qu'elle traite la table séquentiellement
> (Constant Scan)

Tout à fait normal. Mais cela dépend d'ou vous utiliser votre fonction.
Dans quelle clause de quel ordre SQL...

>
> Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
> pratiquement impossible d'utiliser les fonctions pour extraire des données.
>
> Avez-vous déja observé cela, ou bien est-ce que je délire ?
>
> Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
> paramètre ou autre réglage pour éviter cela ?

Certaines fonctions peuvent d'ailleurs être transformées ou encapsulées
pour pouvoir bénéficier des recherches sur index.

>
> Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?

Il ne s'agit pas d'un problème, mais d'un comportement normal.

>
> Merci pour vos avis.
>
> Martin.


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

tintin a écrit :

Non, je ne trouve ni normal, ni évident, qu'on ne puisse pas structurer sa
programmation.

Quand vous avez des centaines de procédures stockées qui recherchent la même
donnée, il est logique de rechercher à mettre en commun le traitement et on
pourait penser que les UDF sont faites pour cela.

Mais si leur efficacité "dépend" (on ne sait de quoi), les UDF sont
inutilisables en pratique.

La documentation devrait au moins avertir de ces restrictions.

L'activation de l'index à de tout temps nécessité que la clause soit
SARGeable (Search ARGument) ! C'est dans les cours sur l'optimisation
des bases de données depuis les années 70 !!!

Extrait de la doc MS :

"
Un argument de recherche (SARG) restreint une recherche, car il spécifie
une concordance exacte, une plage de valeurs ou un ensemble de deux
éléments ou davantage réunis par un opérateur AND. Il peut prendre une
des formes suivantes :

* Colonne opérateur <constante ou variable>

* <constante ou variable> opérateur Colonne

Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois
LIKE (dans les cas des concordances des préfixes, tels que LIKE
'John%'). Un argument SARG peut contenir plusieurs conditions réunies
par un opérateur AND. Il peut s'agir d'une requête qui correspond à une
valeur spécifique, telle que :

* "Customer ID" = 'ANTON'
* 'Doe' = "Last Name"

Il peut également s'agir d'une requête qui correspond à une plage de
valeurs, telle que :

* "Order Date" > '1/1/2002'
* "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
* "Customer ID" IN ('ANTON', 'AROUT')

Une expression qui n'utilise pas d'opérateurs SARG n'améliore en aucun
cas les performances, car le processeur de requêtes doit évaluer chaque
ligne pour déterminer si elle répond à la clause de filtre. Pour cette
raison, un index est inutile sur des expressions qui n'utilisent pas des
opérateurs SARG. Les opérateurs non-SARG comprennent NOT, <>, NOT
EXISTS, NOT IN, NOT LIKE et des fonctions intrinsèques.
"

NOTA : fonctions intrinsèques = UDF.


A +

Dommage.



"SQLpro [MVP]" a écrit :

tintin a écrit :

Bonjour,

Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
parait normal.

Si une requête absolument identique est utilisée par une fonction, et que je
lance cette fonction, je vois qu'elle traite la table séquentiellement
(Constant Scan)

Tout à fait normal. Mais cela dépend d'ou vous utiliser votre fonction.
Dans quelle clause de quel ordre SQL...

Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
pratiquement impossible d'utiliser les fonctions pour extraire des données.

Avez-vous déja observé cela, ou bien est-ce que je délire ?

Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
paramètre ou autre réglage pour éviter cela ?

Certaines fonctions peuvent d'ailleurs être transformées ou encapsulées
pour pouvoir bénéficier des recherches sur index.

Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?

Il ne s'agit pas d'un problème, mais d'un comportement normal.

Merci pour vos avis.

Martin.

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

let Mon, 15 May 2006 08:28:02 -0700, tintin a écrit:

Non, je ne trouve ni normal, ni évident, qu'on ne puisse pas structurer sa
programmation.

Quand vous avez des centaines de procédures stockées qui recherchent la même
donnée, il est logique de rechercher à mettre en commun le traitement et on
pourait penser que les UDF sont faites pour cela.

Mais si leur efficacité "dépend" (on ne sait de quoi), les UDF sont
inutilisables en pratique.

La documentation devrait au moins avertir de ces restrictions.

Dommage.

J'ajoute aux remarques de Frédéric que personne ne vous empêche de
structurer votre programmation, au contraire. Simplement il est sage de le
faire en accord avec les spécificités d'un environnement. En l'occurrence,
un minimum de compréhension de la façon dont SQL Server travaille peut vous
apporter pas mal d'avantages dans votre travail.
Si vous voulez modulariser votre programmation, vous avez la possibilité
d'utiliser des procédures stockées qui retournent des paramètres OUTPUT,
par exemple.
Un des grands avantages du moteur SQL et de son optimisation est la
particularité de travailler en set de données. Vous pouvez décider
d'utiliser une approche séquentielle en utilisant par exemple des curseurs,
dans ce cas vous avez une liberté totale de mouvement. Par contre, ne
cherchez plus à obtenir de bonnes performances de la part du moteur SQL.

Enfin, si vous avez des centaines de procédures stockées qui recherchent la
même donnée, peut-être pouvez-vous essayer de les regrouper en un plus
petit nombre ?

--
Rudi Bruchez - MCDBA
http://www.babaluga.com/

Bonjour,

Je sais, bien entendu, que les index ne seront pas utilisés si les clauses
de sélection contiennent des expressions ou opérations trop complexes.

Mais ce n'est pas ici le problème, les clauses sont absolument identiques
dans les deux cas, avec ou sans fonction. Les fonctions ne s'appliquent qu'au
champs retournés.

Voici un exemple simplifié :
set nocount on
create table dbo.essai ( nom varchar(30) )
create unique index essai_1 on essai (nom)
insert into essai values ('Bernard')
insert into essai values ('Anne')
insert into essai values ('Jean')
insert into essai values ('Marcel')
insert into essai values ('Philippe')


go
create function dbo.test() returns int as
begin
declare @Resultat int
Select @Resultat=count(*) from dbo.essai where nom = 'Marcel' return
@Resultat
end
go

--1 en appellant la fonction : aucun index utilisé
select dbo.test()

--2 en recopiant le code : index normalement utilisé sur la table
Select 1 from essai where nom = 'Marcel'



"SQLpro [MVP]" a écrit :

tintin a écrit :
> Non, je ne trouve ni normal, ni évident, qu'on ne puisse pas structurer sa
> programmation.
>
> Quand vous avez des centaines de procédures stockées qui recherchent la même
> donnée, il est logique de rechercher à mettre en commun le traitement et on
> pourait penser que les UDF sont faites pour cela.
>
> Mais si leur efficacité "dépend" (on ne sait de quoi), les UDF sont
> inutilisables en pratique.
>
> La documentation devrait au moins avertir de ces restrictions.

L'activation de l'index à de tout temps nécessité que la clause soit
SARGeable (Search ARGument) ! C'est dans les cours sur l'optimisation
des bases de données depuis les années 70 !!!

Extrait de la doc MS :

"
Un argument de recherche (SARG) restreint une recherche, car il spécifie
une concordance exacte, une plage de valeurs ou un ensemble de deux
éléments ou davantage réunis par un opérateur AND. Il peut prendre une
des formes suivantes :

* Colonne opérateur <constante ou variable>

* <constante ou variable> opérateur Colonne

Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois
LIKE (dans les cas des concordances des préfixes, tels que LIKE
'John%'). Un argument SARG peut contenir plusieurs conditions réunies
par un opérateur AND. Il peut s'agir d'une requête qui correspond à une
valeur spécifique, telle que :

* "Customer ID" = 'ANTON'
* 'Doe' = "Last Name"

Il peut également s'agir d'une requête qui correspond à une plage de
valeurs, telle que :

* "Order Date" > '1/1/2002'
* "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
* "Customer ID" IN ('ANTON', 'AROUT')

Une expression qui n'utilise pas d'opérateurs SARG n'améliore en aucun
cas les performances, car le processeur de requêtes doit évaluer chaque
ligne pour déterminer si elle répond à la clause de filtre. Pour cette
raison, un index est inutile sur des expressions qui n'utilisent pas des
opérateurs SARG. Les opérateurs non-SARG comprennent NOT, <>, NOT
EXISTS, NOT IN, NOT LIKE et des fonctions intrinsèques.
"

NOTA : fonctions intrinsèques = UDF.


A +


>
> Dommage.
>
>
>
> "SQLpro [MVP]" a écrit :
>
>> tintin a écrit :
>>> Bonjour,
>>>
>>> Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
>>> dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
>>> parait normal.
>>>
>>> Si une requête absolument identique est utilisée par une fonction, et que je
>>> lance cette fonction, je vois qu'elle traite la table séquentiellement
>>> (Constant Scan)
>> Tout à fait normal. Mais cela dépend d'ou vous utiliser votre fonction.
>> Dans quelle clause de quel ordre SQL...
>>
>>> Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
>>> pratiquement impossible d'utiliser les fonctions pour extraire des données.
>>>
>>> Avez-vous déja observé cela, ou bien est-ce que je délire ?
>>>
>>> Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
>>> paramètre ou autre réglage pour éviter cela ?
>> Certaines fonctions peuvent d'ailleurs être transformées ou encapsulées
>> pour pouvoir bénéficier des recherches sur index.
>>
>>> Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?
>> Il ne s'agit pas d'un problème, mais d'un comportement normal.
>>
>>> Merci pour vos avis.
>>>
>>> Martin.
>>
>> --
>> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
>> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
>> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
>> ********************* http://www.datasapiens.com ***********************
>>


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Je sais, bien sûr, qu'une clause where qui utilise des fonctions ou fait des
opérations compliquées ne va pas pouvoir utiliser d'index.

Là n'est pas le problème. Les deux clauses sont simples et identiques avec
ou sans fonction, et il n'y a aucune fonction dans une clause Where.

Voici un exemple très simplifié (et très inutile) pour illustrer. Il serait
surtout utile dans une requête plus complexe avec jointure(s).

set nocount on
create table dbo.essai ( nom varchar(30) )
create unique index essai_1 on essai (nom)
insert into essai values ('Bernard')
insert into essai values ('Anne')
insert into essai values ('Jean')
insert into essai values ('Marcel')
insert into essai values ('Philippe')


go
create function dbo.test() returns int as
begin
declare @Resultat int
Select @Resultat=count(*) from dbo.essai where nom = 'Marcel'
return @Resultat
end
go

--1 en appellant la fonction : aucun index utilisé
select dbo.test()

--2 en recopiant le code : index normalement utilisé sur la table
Select count(*) from essai where nom = 'Marcel'






"SQLpro [MVP]" a écrit :

tintin a écrit :
> Non, je ne trouve ni normal, ni évident, qu'on ne puisse pas structurer sa
> programmation.
>
> Quand vous avez des centaines de procédures stockées qui recherchent la même
> donnée, il est logique de rechercher à mettre en commun le traitement et on
> pourait penser que les UDF sont faites pour cela.
>
> Mais si leur efficacité "dépend" (on ne sait de quoi), les UDF sont
> inutilisables en pratique.
>
> La documentation devrait au moins avertir de ces restrictions.

L'activation de l'index à de tout temps nécessité que la clause soit
SARGeable (Search ARGument) ! C'est dans les cours sur l'optimisation
des bases de données depuis les années 70 !!!

Extrait de la doc MS :

"
Un argument de recherche (SARG) restreint une recherche, car il spécifie
une concordance exacte, une plage de valeurs ou un ensemble de deux
éléments ou davantage réunis par un opérateur AND. Il peut prendre une
des formes suivantes :

* Colonne opérateur <constante ou variable>

* <constante ou variable> opérateur Colonne

Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois
LIKE (dans les cas des concordances des préfixes, tels que LIKE
'John%'). Un argument SARG peut contenir plusieurs conditions réunies
par un opérateur AND. Il peut s'agir d'une requête qui correspond à une
valeur spécifique, telle que :

* "Customer ID" = 'ANTON'
* 'Doe' = "Last Name"

Il peut également s'agir d'une requête qui correspond à une plage de
valeurs, telle que :

* "Order Date" > '1/1/2002'
* "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
* "Customer ID" IN ('ANTON', 'AROUT')

Une expression qui n'utilise pas d'opérateurs SARG n'améliore en aucun
cas les performances, car le processeur de requêtes doit évaluer chaque
ligne pour déterminer si elle répond à la clause de filtre. Pour cette
raison, un index est inutile sur des expressions qui n'utilisent pas des
opérateurs SARG. Les opérateurs non-SARG comprennent NOT, <>, NOT
EXISTS, NOT IN, NOT LIKE et des fonctions intrinsèques.
"

NOTA : fonctions intrinsèques = UDF.


A +


>
> Dommage.
>
>
>
> "SQLpro [MVP]" a écrit :
>
>> tintin a écrit :
>>> Bonjour,
>>>
>>> Sous SQL-SERVER 2000, j'exécute une requête sous Query Analyser et je vois
>>> dans le plan d'exécution qu'elle utilise un index (Index Seek), comme cela
>>> parait normal.
>>>
>>> Si une requête absolument identique est utilisée par une fonction, et que je
>>> lance cette fonction, je vois qu'elle traite la table séquentiellement
>>> (Constant Scan)
>> Tout à fait normal. Mais cela dépend d'ou vous utiliser votre fonction.
>> Dans quelle clause de quel ordre SQL...
>>
>>> Je trouve cela très surprenant et ennuyeux... Cela signifie qu'il est
>>> pratiquement impossible d'utiliser les fonctions pour extraire des données.
>>>
>>> Avez-vous déja observé cela, ou bien est-ce que je délire ?
>>>
>>> Serait-ce systématique avec les fonctions, ou bien est-ce qu'il y a un
>>> paramètre ou autre réglage pour éviter cela ?
>> Certaines fonctions peuvent d'ailleurs être transformées ou encapsulées
>> pour pouvoir bénéficier des recherches sur index.
>>
>>> Est-ce que ce problème est résolu sous SQL-SERVER 2005 ?
>> Il ne s'agit pas d'un problème, mais d'un comportement normal.
>>
>>> Merci pour vos avis.
>>>
>>> Martin.
>>
>> --
>> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
>> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
>> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
>> ********************* http://www.datasapiens.com ***********************
>>


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************