Discussion :

[Pgs]

Bonjour,

Je m'interroge sur le point suivant : un select avec une condition where champ='...' a-t-il une vitesse d'éxécution différente selon que le champ est un bigint ou un VARCHAR ?

Merci pour toute réponse

Phil

[pacmann]

Ca dépend peut être du SGBD, mais c'est très certainement négligeable.
Je te donne l'exemple d'Oracle où il n'y a aucune différence.

Au départ, on pourrait se dire que la comparaison de nombres est plus rapide, parce que tu passes directement deux valeurs à ton processeur arithmétique, qui du coup te torche ça proprement.

SAUF QUE, sous Oracle, les numériques sont stockés dans une structure qui ressemble assez fortement a du VARCHAR, la donnée brute étant représentée dans du pseudo binaire codé décimal (BCD)

Du coup non, sous Oracle, ça ne change rien et comme dit de toutes façons, le temps d'exécution de ce type de test est vraiment négligeable comparé à toutes les autres manips qui sont faites par le SGBD lors de l'exécution...

[Pgs]

Merci pour ta réponse.

Phil

[CinePhil]

Je n'irai pas contredire pacman sur Oracle vu que je ne connais pas (encore) ce SGBD.
Je n'ai pas non plus de connaissance aussi fine sur les autres SGBD mais il me semble avoir lu ici ou là que si ont fait :

Code :

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

WHERE ColonneNumerique = '1'

Alors le SGBD prendra le temps de faire une conversion de type.

Autant lui éviter ce travail non ?
Ceci est plus propre :

Code :

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

WHERE ColonneNumerique = 1

Et ça fait deux caractères de moins à écrire !

[pacmann]

Tout à fait d'accord avec toi, Cinephil.

Ma réponse concernait juste la comparaison VARChAR vs VARCHAR et NUMERIC vs NUMERIC.

[SQLpro]

NON !!!!

Je ne peut pas laisser passer cela qui est faux !!!!

En effet la vitesse de traitement d'u littéral n'est pas du tout la même que celle d'un INT quelque soit le SGBDR !

En effet :
1) la taille va influencer considérablement

Entre un INT qui est du 32 bits, soit la longueur du mot du processeur et un VARCHAR(256) il va y avoir une sacrée différence de temps d'exécution

2) le travail à faire pour comparer une valeur numérique et une valeur littérale n'est pas du tout le même
a) derrière chaque littéraux, il y a une collation (même sous Oracle ou cela est mal fait avec le NLS), la comparaison peut être sensible à la casse ou aux accents, ce qui induit un "extra overhead" non négligeable
b) le VARCHAR étant par nature de taille variable, il y a un "extra overhead" induit par la lecture même du littérale qui suppose préalablement de lire la longueur réelle de la donné.

Au final, la différence peut être très considérable !!!!!

A +

[pacmann]

Certes SQLPro, mais ce que je disais juste, c'est que sous Oracle, les formats numériques ne sont pas des binaires, mais que leur stockage est similaire à du varchar !

Et que tester MaColonneAlpha = 'Marcel', c'est pas plus long que tester MaColonneNum = 15682...

Je comprends qu'on considère l'info peu fiable du fait que c'est moi qui la donne, donc une référence plus solide :
http://asktom.oracle.com/pls/asktom/...10500346778955

Pourquoi est-ce qu'Oracle ne propose pas de INT32 sur le SQL, je n'en sais rien... mais jusqu'ici on y a survécu

[pacmann]

SQLPro,

J'en remets une petite couche, pour étendre un peu les tests de l'oncle Tom.
Les mesures sont effectuées sur un oracle express 10g, installé sur mon poste.

Créons une table d'un million de lignes avec :
- 1 nombre incrémenté
- une deuxième colonne contenant une chaîne de 255 caractères :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SQL> ed
Wrote file afiedt.sql
 
  1  create table test_cmp_c_i as
  2  select lpad('x', 254, 'x') || to_char(mod(level, 10)) as c, level as i
  3  from dual
  4* connect by level <= 1000000
SQL> /
 
Table created.

Comparons les temps d'exécution d'un filtre. C'est à dire qu'il y aura à chaque fois 1 million de tests.

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
 
SQL> alter session set events '10046 trace name context forever, level 12'
  2  /
 
Session altered.
 
SQL> select count(*)
  2  from test_cmp_c_i
  3  where i = 1
  4  /
 
  COUNT(*)
----------
         1
 
SQL>  select count(*) from test_cmp_c_i where c = lpad('x', 254, 'x') || '1';
 
  COUNT(*)
----------
    100000
 
SQL> select count(*) from test_cmp_c_i where c = lpad('x', 254, 'x')
  2  /
 
  COUNT(*)
----------
         0
SQL> alter session set events '10046 trace name context off';
 
Session altered.

La trace SQL :

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
 
********************************************************************************
select count(*)
from test_cmp_c_i
where i = 1
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.17       0.17          0      38082          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.17       0.17          0      38082          0           1
********************************************************************************
select count(*) 
from
 test_cmp_c_i where c = lpad('x', 254, 'x') || '1'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.45       0.45          0      38082          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.45       0.45          0      38082          0           1
 
********************************************************************************
select count(*) 
from
 test_cmp_c_i where c = lpad('x', 254, 'x')
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.12       0.11          0      38082          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.11          0      38082          0           1

Conclusions :
- moins d'une demi seconde pour 1 million de tests
- le temps d'exécution des tests en eux-même sont très peux significatifs : en lançant plusieurs exécutions, il peut varier de 30% alors que rien ne change.
Je dirais donc qu'il y a des paramètres bien plus importants...
- la dernière requête filtre sur une chaîne de longueur 254 : 0,12 secondes. C'est encore plus court que la comparaison d'entiers. On peut donc supposer que la longueur est testée en premier.
Donc la différence de temps de réponse entre le test numérique et le premier test varchar est biaisée : la colonne i a des valeurs bien plus éparses. Et comme je l'ai déjà dit, les entiers sont également stockés sous un format variable (en gros, un demi byte par chiffre si on fait abstraction des subtilités)(donc avec un potentiel test de longueur), alors que les valeurs de la colonne c sont toutes de même longueur.

Bref, je suppose que tu vas à nouveau me sortir le calcul de ce que ça prend comme temps sur 10 ans... mais disons le quand même : cet overhead est bien complètement négligeable par rapport aux autres opérations du SGBD.
A noter que dans cet exemple, on a vraiment limité tous les autres paramètres :
- nous avons tapé des données qui étaient toutes en cache
- des lignes toutes petites donc un nombre de consistent gets limité par rapport à ce qu'on pourrait avoir en général quand on lit 1 million de lignes
- les requêtes étaient déjà parsées (j'ai pas affiché l'info, mais library cache misses = 0)
- activité quasi nulle sur la base, peu de wait events
- et enfin, il faut préciser que souvent, quand on lit 1 million de lignes, c'est dans le cadre soit de batchs, soit de requêtes assez violentes... qui rendent donc la différence plutôt insignifiante.

Voilà voilà...

[SQLpro]

A noter que dans cet exemple, on a vraiment limité tous les autres paramètres :
- nous avons tapé des données qui étaient toutes en cache
[...]
- activité quasi nulle sur la base, peu de wait events
[...]

Vous partez donc de postulats généralement faux pour asséner des vérités !!!!

Aucun DBA sérieux ne mettra une RAM égale à la taille de la base de données, ni une exclusivité d'utilisation de la base, pour satisfaire vos désirs....

A +

[pacmann]

Non, ce n'est ni l'idée, ni l'objectif.
Si la requête en situation réelle est ralentie par d'autres paramètres, la différence relative entre les deux exécutions diminuera !

[SQLpro]

Si la requête en situation réelle est ralentie par d'autres paramètres, la différence relative entre les deux exécutions diminuera !

C'est bien évidemment faux et tout à fait le contraire. En effet d'autres paramètres signifient généralement d'autres process, c'est à dire de la concurrence, donc d'autres accès aux données, donc du ralentissement dus aux verrous. Or plus l'ensemble de données est important plus le verrouillage est complexe et plus il ralentit tous les autres process !

A +

[pacmann]

Or plus l'ensemble de données est important plus le verrouillage est complexe et plus il ralentit tous les autres process !

On a les mêmes lignes, et donc les mêmes verrous !
A moins que SQL Server ne verrouille des colonnes ??

Si on se met à comparer les temps d'exécution entre un filtre sur une table avec des petites lignes et une table avec des grosses lignes, ça n'a plus de sens !

Et d'après la question de départ, je ne pense pas qu'on parle d'ici d'une table à une seule colonne numérique, contre une énorme table blobeuse...

[SQLpro]

Vous oubliez que les comparaisons sur des chaines de caractères obligent à un extra overhead du fait de la collation => durée plus grande, et que le type VARCHAR impose de stocker en sus de la donnée, la longueur => volume plus fort.
malheureusement votre test est biaisé par Oracle qui gère très mal la notion de collation qui fait pourtant partie intégrante de la norme SQL et est implémenté dans tous les SGBDR à l'exception d'Oracle

A +

[skuatamad]

Le test n'est pas du tout biaisé, il est au contraire très bien posé :

Certes SQLPro, mais ce que je disais juste, c'est que sous Oracle, les formats numériques ne sont pas des binaires, mais que leur stockage est similaire à du varchar !

Comme d'habitude vous asséner des certitudes sans amener le moindre argument mesurable.
Le débat aurait eu un intérêt si vous aviez apporté le même genre de test que celui proposé par pacman.
Les lecteurs (notemment ceux habitués à Oracle) auraient alors pu apprendre quelque chose de tangible.
Bien sûr je me doute bien que ce que vous avancez est vrai concernant SqlServer, mais pour moi le but d'un forum n'est pas de simplement dénigrer !
Apprendre les bonnes bonnes pratiques et connaître les spécificités des outils que l'on utilise (ou que l'on utilisera) ça c'est intéressant...

Votre avis et vos expériences sont utils, mais fournir des liens vers de la doc, ou proposer des tests, c'est également nécessaire !
Je ne vois pas pourquoi nous devrions vous lire bouche bée !

[SQLpro]

D'abord je ne dénigre pas je constate. Oracle ne sait pas gérer la collation qui fait partie intégrante de la norme SQL depuis plus de 15 ans. C'est un fait.

Ensuite si vous désirez voir de visu la chose il vous suffit de venir à mon cours d'optimisation à Orsys ou à celui du CNAM Toulon (NFE 113).
Je ne peut pas en effet reproduire la chose dans un forum car pour cette démo j'ai besoin d'une table dont les données dépasse allégrement la RAM du serveur, ce qui se traduit en terme de Go, ce que les FTP° de developpez ne permettent pas.

A +

[pacmann]

Oracle qui gère très mal la notion de collation
A +

Si ce n'est pas indiscret, on peut savoir ce qui est reproché au NLS d'Oracle ?

le type VARCHAR impose de stocker en sus de la donnée, la longueur => volume plus fort.

- Combien faut-il d'octets pour stocker une longueur comprises entre 0 et 65535 ?
- Comme on l'a vu un peu plus haut, cette longueur est certainement testée en premier et permet de conclure immédiatement l'inégalité des chaînes lorsque la longueur est différente

[SQLpro]

Si ce n'est pas indiscret, on peut savoir ce qui est reproché au NLS d'Oracle ?

D'abord de ne pas être à la norme, ce qui ne permet pas la portabilité.
Ensuite une syntaxe absconse qui ne permet pas aisément de s'y retrouver.
Par exemple avec deux données de différentes collation, effectuer un traitement sur une troisème collation se note en Oracle :

Code :

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

NLSSORT(Macolonne, 'NLS_SORT = Latin_CI') = NLSSORT('Mavaleur', 'NLS_SORT = Latin_CI')

Alors que la norme SQL bien plus pratique et logique nous impose ceci :

Code :

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

MaColonne = 'MaValeur' COLLATE French_CI

Et dans la syntaxe SQL le risque est grand de mettre deux colations non coercible !

- Combien faut-il d'octets pour stocker une longueur comprises entre 0 et 65535 ?

Tout dépend du SGBDR.

• Si ce dernier accepte des VARCHAR jusqu'à 256 car max, alors 1 octet suffit,
• Si ce dernier accepte des VARCHAR jusqu'à 65536 car max, alors 2 octets suffisent (cas de SQL Server)

...

- Comme on l'a vu un peu plus haut, cette longueur est certainement testée en premier et permet de conclure immédiatement l'inégalité des chaînes lorsque la longueur est différente

[/QUOTE]

Tout dépend si la comparaison est indexées ou non et si le critère est l'égalité ou un autre (inégalité, différence...)

A +

[pacmann]

norme
syntaxe absconse
le risque est grand de mettre deux colations non coercible !

Ok je suis bien d'accord qu'il est souhaitable de se conformer à la norme... sauf que ce que vous disiez juste précédemment, c'est :

"malheureusement votre test [de performances] est biaisé par Oracle qui gère très mal la notion de collation"

Tout dépend si la comparaison est indexées ou non et si le critère est l'égalité ou un autre (inégalité, différence...)

Selon le titre, l'énoncé, les assertions, on parle bien de test d'égalité...
Cela dit, je suis d'accord sur la différence qu'il peut y avoir au niveau des index, surtout au niveau de la taille de l'index. (Mais donc encore une fois, seulement si on part du principe que du coup les index ne sont pas de même taille, genre deux index mono colonnes !)

Tout dépend du SGBDR.

• Si ce dernier accepte des VARCHAR jusqu'à 256 car max, alors 1 octet suffit,
• Si ce dernier accepte des VARCHAR jusqu'à 65536 car max, alors 2 octets suffisent (cas de SQL Server)

...

Ma question était rhétorique. Comme mon esprit fonctionne très mal en binaire, j'ai retrouvé 65535 en faisant 2^16 - 1.
L'objectif étant d'arriver à la question : 2 octets, est-ce vraiment monstrueux ?
De plus, et c'est le point le plus important : on ne discute pas de l'utilisation où non des varchar dans une base de données, mais bien de la vitesse d'une comparaison ! Si le varchar prend plus de place... soit, mais il sera présent même si on compare des entiers de la même ligne !


J'aimerais préciser que je ne m'acharne pas contre vous : rien de personnel.
Simplement, je pense qu'on n'est pas là pour "gagner à tout pris", mais pour répondre à une question (avec certes des possibilités d'ouverture du débat) de manière constructive et argumentée !

[skuatamad]

Je ne peut pas en effet reproduire la chose dans un forum car pour cette démo j'ai besoin d'une table dont les données dépasse allégrement la RAM du serveur, ce qui se traduit en terme de Go, ce que les FTP° de developpez ne permettent pas.

Argument non retenu, Pacman a juste posté un CTAS (mais un DESC peut suffire) et un extrait du tkprof.
Je ne pense pas que l'accès aux données soit nécessaire, c'est le rapport du travail effectué qui est interessant.