Discussion :

[KeepItSimple]

Bonjour,

j'ai une table avec 51.000.000 enregistrements.
La clé primaire est un varchar(100) (un hash), collation SQL_Latin1_General_CP1_CS_AS, full text false.

select * from table where pk_id in (N'...', N'...',...) (10 valeurs) prend 30 secondes, ce qui fait 3 secondes par recherche, ce qui me semble très lent.


Y a t'il des astuces pour accélérer une telle recherche en sql server(paramètres, type d'index, ...)?

Merci.

[Invité]

Une piste, peut-être, pourquoi mettre un N'...' pour en faire un NVARCHAR que vous allez ensuite comparer avec un VARCHAR. Y a potentiellement un transtypage qui se fait.

[KeepItSimple]

Effectivement, le transtypage peut causer des soucis.

Quand j'analyse avec le profiler, je vois ceci:

Code :

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

1
2
3
exec sp_executesql N'SELECT * FROM UniqueIdentifier (NOLOCK) WHERE Id IN (@ids1,@ids2,@ids3,@ids4,@ids5,@ids6,@ids7,@ids8,@ids9,@ids10)',
N'@ids1 varchar(100),@ids2 varchar(100),@ids3 varchar(100),@ids4 varchar(100),@ids5 varchar(100),@ids6 varchar(100),@ids7 varchar(100),@ids8 varchar(100),@ids9 varchar(100),@ids10 varchar(100)',
@ids1='KPL2Fncf4SpHcaAjwx21020107',@ids2='KPL2FUyKtmB9ZJAjwx21020107',@ids3='KPL2FbnBdrpUwMAjwx21020107',@ids4='KPL2FYdKdnkxg0Ajwx21020107',@ids5='KPL2FkJRjESfYxAjwx21020107',@ids6='KPL2Fv7H649CszAjwx21020107',@ids7='KPL2FwfYGbh6ldAjwx21020107',@ids8='KPL2FeLlmo6QCAAjwx21020107',@ids9='KPL2FP1ZPtXRjOAjwx21020107',@ids10='KPL2FKdUy9p6ivAjwx21020107'

Les paramètres sont bien passés en varchar mais ça prend 30 secondes quand même.

SELECT * FROM UniqueIdentifier (NOLOCK) WHERE Id IN (N'...', N'...', ...) est lent
et
SELECT * FROM UniqueIdentifier (NOLOCK) WHERE Id IN ('...', '...', ...) est rapide

Mais pourquoi ce select prend-il 30 secondes (surtout avec un nolock)?

[SQLpro]

Un IN avec beaucoup de valeurs correspond à une série de OR.
Hors le OR n'est pas cherchable (à me lire). Ce qui signifie que toute pose d'index est inutile, il fera toujours un SCAN (balayage) de la table.

Pour résoudre ce genre de problème il faut créer une table temporaire avec une seule colonne clé de même type que la clé de votre table, insérer les valeurs dedans et faire une jointure entre cette table temporaire et votre table.

D'autre part vous me dite que cette colonne de clé primaire est une valeur de hachage, donc du binaire... Pourquoi la placer dans une colonne de type littéral ? Il y a deux inconvénients....
1) transtypage du binaire vers le littéral => cout !
2) cout supplémentaire dlié à un tuype littéral du fait de la collation.

A +

[escartefigue]

Si, coup de bol, les valeurs de la liste IN sont contiguës, qu'elles représentent une petite proportion du nombre de valeurs possibles et qu'un index est éligible, alors un BETWEEN (sargable) est préférable.
Je ne crois pas que l'optimiseur soit capable de remplacer de son propre chef un IN(liste de valeurs contiguës) par un BETWEEN dans ce cas.

[SQLpro]

Si, coup de bol, les valeurs de la liste IN sont contiguës, qu'elles représentent une petite proportion du nombre de valeurs possibles et qu'un index est éligible, alors un BETWEEN (sargable) est préférable.
Je ne crois pas que l'optimiseur soit capable de remplacer de son propre chef un IN(liste de valeurs contiguës) par un BETWEEN dans ce cas.

En aucun cas !... Ce n'est pas la même chose...

A +

[KeepItSimple]

@sqlplro:

1. dans les sql server récents, le in est "searchable".
=> Comme écrit plus haut, select * from ... where id in (N'...',N'...'...) fonctionne lentement à cause du transtypage (full scan 33 secondes), mais select * from ... where id in ('...','...'...) renvoie le bon résultat en mois d'une seconde à partir de management studio. Le souci n'est donc pas au niveau du IN.... A moins que le passage d'un array en paramètre via .NET ne soit un souci?

2. Il évite de se préoccuper des verrous et donne donc des résultats incohérent.
=> Je pense que les développeurs l'ont mis en pensant que la lenteur venait des locks, mais malgré cela ca plante=> c'est pas ça.

3. "Pourquoi la placer dans une colonne de type littéral": je suis dba, les développeurs de l'application (appli que je ne connais pas) sont dans un autre pays (Espagne) et ils ont probablement leurs raisons. Mais même si un littéral est un peu moins performant, je ne vois pas de raison valable expliquant que de temps en temps ce "select" prenne 30 secondes pour chercher 10 lignes sur une clé primaire sans ce soucier des locks.... A moins que sql server dans certains cas ne remplace les paramètres varchar en nvarchar pour une raison obscure que j'aimerais connaître?

@escartefigue: les clés sont des hash donc le between ne sert à rien.

Merci quand même.

[Invité]

3. "Pourquoi la placer dans une colonne de type littéral": je suis dba, les développeurs de l'application (appli que je ne connais pas) sont dans un autre pays (Espagne) et ils ont probablement leurs raisons.

La raison principale que je constate régulièrement c'est parce que c'est le seul type de données qu'ils connaissent pour mettre ça...

[escartefigue]

@escartefigue: les clés sont des hash donc le between ne sert à rien.

Ok

La raison principale que je constate régulièrement c'est parce que c'est le seul type de données qu'ils connaissent pour mettre ça...

Je ne compte plus le nombre de BDD dans lesquelles le type varchar règne en maître y compris pour les identifiants et les dates !

[SQLpro]

@sqlplro:

1. dans les sql server récents, le in est "searchable".

Non, pas du tout. En aucune façon !
Il va tout de même essayer transformer certains IN limités à quelques valeurs en plusieurs accès d'index, mais si cela s'avère plus lourd que la scan il abandonne. Avec 10 valeurs il y a très peu de chances....

=> Comme écrit plus haut, select * from ... where id in (N'...',N'...'...) fonctionne lentement à cause du transtypage (full scan 33 secondes), mais select * from ... where id in ('...','...'...) renvoie le bon résultat en mois d'une seconde à partir de management studio. Le souci n'est donc pas au niveau du IN.... A moins que le passage d'un array en paramètre via .NET ne soit un souci?

Bien sur que si le soucis est le IN !

3. "Pourquoi la placer dans une colonne de type littéral": je suis dba, les développeurs de l'application (appli que je ne connais pas) sont dans un autre pays (Espagne) et ils ont probablement leurs raisons. Mais même si un littéral est un peu moins performant, je ne vois pas de raison valable expliquant que de temps en temps ce "select" prenne 30 secondes pour chercher 10 lignes sur une clé primaire sans ce soucier des locks.... A moins que sql server dans certains cas ne remplace les paramètres varchar en nvarchar pour une raison obscure que j'aimerais connaître?

VARCHAR et NVARCHAR sont deux encodages différents. le premier utilise un octets par car. (style ASCII) le second 2 (style UNICODE). Vous dites "varchar(100)" pour la clé donc une chaine de type ASCII. En forçant du N'...' vous décidez de mettre des chaines UNICODE là ou il doit comparer avec l'ASCII dans la table. Or comme c'est votre demande, il doit transtyper toutes les valeurs ASCII des colonnes en UNICODE avant d'effectuer la comparaison.... Voila pourquoi c'est lent. Et voila pourquoi c'est parfaitement imbécile de mettre une données hexadécimale dans du caractère !

A +

[SQLpro]

...
Mais pourquoi ce select prend-il 30 secondes (surtout avec un nolock)?

Un NOLOCK n'accélere pas du tout les requêtes. Il évite de se préoccuper des verrous et donne donc des résultats incohérent.
C'est juste une imbécilité de mettre des NOLOCKs partout. A me lire :
http://mssqlserver.fr/les-dangers-du-nolock/

A +