Rapidité requête avec where ou colonne additionnelle

[Marc_L]

Bonjour,

j'ai une db avec plusieurs millions de lignes avec une colonne nvarchar(max) vide ou remplie sur laquelle je dois faire des centaines de recherches en fulltext/jour.

J'aurai voulu savoir ce qui allait rendre les requêtes plus rapide :

- lancer directement ma requête sans me soucier d'un "where .. is not null"
- ajouter ce "where .. is not null"
- travailler avec une colonne additionelle en booléen qui spécifie si la colonne est vide ou non

Merci.

[Sergejack]

Quels sont vos critères de recherche ?
Tout ceux-ci apparaissent dans la clause WHERE ou vous faites des filtres sur le résultat dans un second temps ?

[iberserk]

Bonjour,

j'ai une db avec plusieurs millions de lignes avec une colonne nvarchar(max) vide ou remplie sur laquelle je dois faire des centaines de recherches en fulltext/jour.

J'aurai voulu savoir ce qui allait rendre les requêtes plus rapide :

- lancer directement ma requête sans me soucier d'un "where .. is not null"
- ajouter ce "where .. is not null"
- travailler avec une colonne additionelle en booléen qui spécifie si la colonne est vide ou non

Merci.

Ce qui va être important c'est le choix de votre clé de recherche(clé définie sur la table pour votre indexation FULLTEXT), l'idéal est un auto incrément.

la recherche FULLTEXT sur votre colonne NVARCHAR(MAX) (au passage le UNICODE est'il nécessaire dans votre cas? avez vous des caractères autres que latin?) sera t'elle votre seul prédicat?


Je ne pense pas que différentier les colonnes NULL sera bénéfique car vous nez requetez pas vraiment sur la colonne mais sur des données indexées par le FULLTEXT.

[Marc_L]

Bonjour, mes requêtes les plus chargées ressemblent à ceci :

Code :

1
2
3
4
UPDATE TABLE SET ... 
WHERE col4 = 1 
AND col3 IS NULL 
AND CONTAINS (col5,'"abcd" AND "efgh" AND NOT "ijkl" AND NOT "mnop"') AND CONTAINS (col6,'"qrst" AND "xyz" ')

Ma clé est sur une colonne id auto-incrémentée et la colonne NVARCHAR(MAX) se doit d'être en unicode sans quoi, je serai contraint de passer par un remplacement obligatoire de tous les caractères diacritiques ... ce qui pourrait, sans doute, alléger le process.

La question concernait donc ces 2 filtres fulltext (CONTAINS). Devrais-je d'abord tester si la col5 et col6 sont nulles ou cela se fait-il indirectement par SQL Server de toute façon avant de passer à la suite?

Aussi, est-ce que la place des clauses a-t-elle une influence ? Mettre les "where is not null" dans début de WHERE ?

Merci.

[iberserk]

Aussi, est-ce que la place des clauses a-t-elle une influence ? Mettre les "where is not null" dans début de WHERE ?

Non aucune, SQL SERVER est basé sur le cout... en gros il décortique votre requète puis détermine la manière la plus rapide de donner un résultat en fonction de la charge de votre server etc.

UPDATE table SET ... WHERE col4 = 1 AND col3 IS NULL AND CONTAINS (col5,'"abcd" AND "efgh" AND NOT "ijkl" AND NOT "mnop"') AND CONTAINS (col6,'"qrst" AND "xyz" ')

est-ce un exemple bidon?

Vous tester si col3 est NULL puis vous faites vos recherches FULLTEXT sur deux autres colonnes?


Ici vous devez clairement avoir un index sur col4 par exemple.

[iberserk]

Code :

1
2
3
 
DECLARE @ASCII VARCHAR(MAX)='ËîôÖœžŸÅÄÆ'
 PRINT @ASCII

Quel caractères diacritiques ne pouvez vous pas gérer en NON UNICODE?

[Marc_L]

Envoyé par iberserk

est-ce un exemple bidon?
Vous tester si col3 est NULL puis vous faites vos recherches FULLTEXT sur deux autres colonnes?

Non, l'exemple est ok.

Le process démarre en fait sur un premier serveur avec SSIS qui flag déjà certaines colonnes en fonction de ...
Ensuite, le tout est injecté sur un autre serveur sql où sera lancé cette requête.

Envoyé par iberserk

Ici vous devez clairement avoir un index sur col4 par exemple.

Y a-il un nombre d'occurences minimum à ne pas prendre en compte pour que cela vaille la peine de créer cet index?
Exemple d'une colonne tinyint avec à peine 4 valeurs différentes.

Pour l'unicode, tu as raison, mais je devrai quand même vérifier car SSIS prend ici des données du monde entier (internet) en dehors de ce qui est éloigné à 100% des caractères latin (asiatique, russe, etc.)

merci iberserk.

[iberserk]

AND CONTAINS (col5,'"abcd" AND "efgh" AND NOT "ijkl" AND NOT "mnop"') AND CONTAINS (col6,'"qrst" AND "xyz" ')

Quand je vois cela.... a quoi sert le FULLTEXT ici?

Vous pourriez obtenir de bonnes performances avec une colonne calculée indexée qui ferait le calcul lors de l'ajout... vous n'auriez plus qu'a filtrer dessus.

Quant à votre colonne avec 4 valeurs distinctes... le mieux est de tester l’intérêt de votre index...

Peut-être pouvez vous penser à un partitionnement de table sur cette valeur?

[Marc_L]

Envoyé par iberserk

Quand je vois cela.... a quoi sert le FULLTEXT ici?

Par exemple, je dois retrouver le modèle d'une voiture parmi des milliers de références dans 2 colonnes dont une en varchar(max) ... utiliser du LIKE prendrait une éternité.

Envoyé par iberserk

Vous pourriez obtenir de bonnes performances avec une colonne calculée indexée qui ferait le calcul lors de l'ajout... vous n'auriez plus qu'a filtrer dessus.

Pourriez-vous m'en dire plus ? Sur quelles colonnes ?

Envoyé par iberserk

Peut-être pouvez vous penser à un partitionnement de table sur cette valeur?

J'y penserai pour la suite quand le tout sera déjà au point de façon "simple" :-)

[elsuket]

Bonjour,

Envoyé par Marc_L

Par exemple, je dois retrouver le modèle d'une voiture parmi des milliers de références dans 2 colonnes dont une en varchar(max) ... utiliser du LIKE prendrait une éternité.

Pas forcément si la recherche est à chaque fois sur un début de chaîne et que la colonne est indexée.

Envoyé par Marc_L

Devrais-je d'abord tester si la col5 et col6 sont nulles ou cela se fait-il indirectement par SQL Server de toute façon avant de passer à la suite?

Aussi, est-ce que la place des clauses a-t-elle une influence ? Mettre les "where is not null" dans début de WHERE ?

Comme vous l'a succinctement décrit iberserk, SQL Server collecte des statistiques de distribution des valeurs dans les colonnes et pour les index.
Il fait cela par échantillonnage.

L'ordre des filtres de la clause WHERE dans une requête n'ayant pas de sous-requête importe peu.
Il peut influer parfois (mais rarement) en tentant de déplacer le filtre du WHERE dans le prédicat d'une équi-jointure (INNER JOIN).

Envoyé par =Marc_L

Mettre les "where is not null" dans début de WHERE ?

Non. En revanche, si vous avez peu de lignes (comparé au nombre total de lignes de la table (sp_spaceused) qui ne sont pas à NULL pour la colonne, alors ajouter un index sur la colonne filtré pas IS NOT NULL peut être avantageux (disponible seulement sous SQL Server 2008)

Envoyé par iberserk

Vous pourriez obtenir de bonnes performances avec une colonne calculée indexée qui ferait le calcul lors de l'ajout... vous n'auriez plus qu'a filtrer dessus.

Même question.

@++

[iberserk]

Vous pourriez obtenir de bonnes performances avec une colonne calculée indexée qui ferait le calcul lors de l'ajout... vous n'auriez plus qu'a filtrer dessus.

Je pensais à tester l'ajout d'une colonne calculée qui ferait déjà le prédicat du WHERE...

[elsuket]

Ça peut marcher si le prédicat du CONTAINS est tout le temps le même ... je doute que ce soit le cas
Peut-être sur col4 ?

Sans la définition de la table et son utilisation, pas facile

@++

[iberserk]

Sans la définition de la table et son utilisation, pas facile

+1

[Marc_L]

Envoyé par elsuket

Sans la définition de la table et son utilisation, pas facile

Je sais bien mais cela serait trop compliqué voir chaotique de tout décortiqué ;-)

utiliser du LIKE prendrait une éternité.

Oui mais dans le cas présent, les infos sont générallement au beau milieu d'un texte (contenu d'une page html)

Envoyé par elsuket

ajouter un index sur la colonne filtré pas IS NOT NULL peut être avantageux

Je ne connaissais pas ... effectivement, après quelques recherches, cela pourra grandement me servir

Ok, je pense avoir eu les réponses dont j'avais besoin pour continuer.
Merci beaucoup.

[elsuket]

http://blog.developpez.com/elsuket/p...-d-u/#more7945

Je sais bien mais cela serait trop compliqué voir chaotique de tout décortiqué ;-)

Donnez simplement la structure de la table, et expliquer ce que vous cherchez à obtenir par votre requête.

Oui mais dans le cas présent, les infos sont générallement au beau milieu d'un texte (contenu d'une page html)

Maintenant je comprend mieux la question d'iberserk (désolé d'être un peu long à la détente ) sur la colonne calculée : je pense que vous pouvez extraire les champs du document HTML au moment de l'insertion.
De cette façon vous devriez même pouvoir vous passer du stockage du document HTML.

@++

[iberserk]

(désolé d'être un peu long à la détente )

Décalage horaire plus chaleur: pas bon ça

Je ne connaissais pas ... effectivement, après quelques recherches, cela pourra grandement me servir

A partir de 2008, vous pouvez ajouter un filtre à vos index (ajoutez simplement WHERE ... à la fin de la création de votre index).

Les gains peuvent-être très intéressant en terme temps de réponse mais permet aussi de ne pas mettre à jour certains index sur les INSERT UPDATE...

Dans tous les cas n’hésitez pas à nous faire un retour sur les gains/solutions que vous avez adopté, le forum sert aussi à çà...