Utilisation du service d’optimisation de la recherche

Le service d’optimisation de la recherche peut améliorer considérablement les performances des requêtes de recherche ponctuelle.

Dans ce chapitre :

Comprendre le service d’optimisation de la recherche

Le service d’optimisation de la recherche vise à améliorer considérablement les performances des requêtes de recherche ponctuelle sélective sur de grandes tables. Une requête de recherche ponctuelle renvoie uniquement une ligne ou un petit nombre de lignes distinctes. Les exemples de cas d’utilisation incluent :

  • Utilisateurs professionnels qui ont besoin de temps de réponse rapides pour les tableaux de bord critiques avec des filtres hautement sélectifs.

  • Scientifiques de données qui explorent de grands volumes de données et recherchent des sous-ensembles spécifiques de données.

Un utilisateur peut inscrire une ou plusieurs tables au service d’optimisation de recherche. L’optimisation de la recherche est une propriété au niveau de la table et s’applique à toutes les colonnes avec les types de données pris en charge (voir la liste des types de données pris en charge ci-dessous).

Comment fonctionne le service d’optimisation de la recherche ?

Pour améliorer les performances des recherches ponctuelles, le service d’optimisation de la recherche s’appuie sur une structure de données persistante qui sert de chemin d’accès de recherche optimisé.

Un service de maintenance qui s’exécute en arrière-plan est responsable de la création et de la maintenance du chemin d’accès de recherche :

  • Lorsque vous ajoutez une optimisation de recherche à une table, le service de maintenance crée et remplit le chemin d’accès à la recherche avec les données nécessaires pour effectuer les recherches.

    Le processus de remplissage des données peut prendre du temps, selon la taille de la table. Le service effectue ce travail en arrière-plan et ne bloque aucune opération simultanée sur la table.

  • Lorsque les données de la table sont mises à jour (par exemple, en chargeant de nouveaux ensembles de données ou via des opérations DML), le service de maintenance met automatiquement à jour le chemin d’accès de recherche pour refléter les modifications apportées aux données.

    Si les requêtes sont exécutées alors que le chemin d’accès à la recherche n’a pas encore été mis à jour, les requêtes peuvent s’exécuter plus lentement, mais renvoient toujours des résultats à jour.

Ce chemin d’accès de recherche et le service de maintenance sont transparents pour l’utilisateur. Vous n’avez pas besoin de créer un entrepôt pour le service qui gère le chemin d’accès à la recherche.

Cependant, vous devez connaître le service, car il y a un coût pour le stockage et les ressources de calcul pour ce service. Voir Gestion des coûts du service d’optimisation de la recherche.

Prise en compte d’autres solutions pour optimiser les performances des requêtes

Le service d’optimisation de la recherche est l’un des nombreux moyens d’optimiser les performances des requêtes. Les techniques liées comprennent :

  • Clustering d’une table.

  • La création d’une ou plusieurs vues matérialisées (en cluster ou non).

Chacun de ces moyens présente différents avantages :

  • Le clustering d’une table peut accélérer l’un des éléments suivants, tant qu’ils se trouvent sur la clé de clustering :

    • Recherches de plages.

    • Recherches d’égalité.

    Cependant, une table peut être mise en cluster sur une seule clé uniquement (qui peut contenir une ou plusieurs colonnes ou expressions).

  • Le service d’optimisation de la recherche accélère uniquement les recherches d’égalité. Cependant, cela s’applique à toutes les colonnes des types pris en charge dans une table pour laquelle l’optimisation de la recherche est activée.

  • Une vue matérialisée accélère à la fois les recherches d’égalité et les recherches de plage, ainsi que certaines opérations de tri, mais uniquement pour le sous-ensemble de lignes et de colonnes incluses dans la vue matérialisée. Les vues matérialisées peuvent également être utilisées afin de définir différentes clés de clustering sur la même table source (ou un sous-ensemble de cette table), ou conjointement avec l’aplatissement des données JSON/variables.

Le tableau suivant montre lesquelles de ces trois optimisations ont des coûts de stockage ou de calcul :

Coût de stockage

Coût de calcul

Service d’optimisation de la recherche

Vue matérialisée

Clustering de la table.

Quels privilèges de contrôle d’accès sont nécessaires pour le service d’optimisation de la recherche ?

Pour ajouter ou supprimer l’optimisation de la recherche d’une table, vous devez disposer des privilèges suivants :

  • Vous devez avoir le privilège OWNERSHIP sur la table.

  • Vous devez avoir le privilège ADD SEARCH OPTIMIZATION sur le schéma qui contient la table.

    GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>;
    

Pour utiliser le service d’optimisation de la recherche pour une requête, vous avez juste besoin de privilèges SELECT sur la table.

Vous n’avez pas besoin de privilèges supplémentaires. L’optimisation de la recherche étant une propriété de table, elle est automatiquement détectée et utilisée (le cas échéant) lors de l’interrogation d’une table.

Identification des tables qui bénéficient de l’optimisation de la recherche

Le service d’optimisation de la recherche est conçu pour améliorer les performances de certains types de requêtes. Les sections suivantes expliquent comment identifier les tables et les types de requêtes pouvant bénéficier de l’optimisation de la recherche.

Requêtes qui bénéficient de l’optimisation de la recherche

L’optimisation de la recherche fonctionne mieux pour améliorer les performances d’une requête lorsque les conditions suivantes sont réunies :

  • Pour la table interrogée :

    • La taille de la table est d’au moins 100 GB.

      Pour les tables plus petites (par exemple de taille inférieure à 10 GB), le service d’optimisation de la recherche n’améliore pas suffisamment les performances des requêtes pour justifier les coûts.

    • L’une des conditions suivantes s’applique :

      • La table n’est pas mise en cluster.

      • La table est fréquemment interrogée sur des colonnes autres que la clé de cluster principale.

  • Pour la requête :

    • La requête s’exécute généralement pendant au moins des dizaines de secondes.

    • Au moins une des colonnes accessibles par le biais de l’opération de filtrage de requête a au moins 100 000 à 200 000 valeurs distinctes.

      Pour déterminer le nombre de valeurs distinctes, vous pouvez utiliser l’une des méthodes suivantes :

      • Utilisez APPROX_COUNT_DISTINCT pour obtenir le nombre approximatif de valeurs distinctes :

        select approx_count_distinct(column1) from table1;
        
      • Utilisez COUNT(DISTINCT <nom_colonne>) pour obtenir le nombre réel de valeurs distinctes :

        select count(distinct c1), count (distinct c2)  from test_table;
        

      Etant donné que vous n’avez besoin que d’une approximation du nombre de valeurs distinctes, pensez à utiliser APPROX_COUNT_DISTINCT, qui est généralement plus rapide et plus économique que COUNT(DISTINCT <nom_colonne>).

    • La requête utilise les types de prédicats suivants :

      • Prédicats d’égalité (par exemple : <nom_colonne> = <constante>).

      • Prédicats qui utilisent IN (voir exemple).

    • Pour les requêtes qui utilisent des conjonctions de prédicats (c’est-à-dire AND), les performances des requêtes peuvent être améliorées par l’optimisation de la recherche si l’un des prédicats respecte les conditions ci-dessus.

      Par exemple, supposons qu’une requête a :

      where condition_x and condition_y

      L’optimisation de la recherche peut améliorer les performances si l’une des conditions renvoie séparément quelques lignes (c’est-à-dire que condition_x renvoie quelques lignes ou condition_y renvoie quelques lignes).

      Si condition_x renvoie quelques lignes mais que condition_y renvoie de nombreuses lignes, les performances de la requête peuvent bénéficier de l’optimisation de la recherche.

      Voir ces exemples supplémentaires.

    • Pour les requêtes qui utilisent des disjonctions de prédicats (c’est-à-dire OR), les performances des requêtes peuvent être améliorées par l’optimisation de la recherche si tous les prédicats respectent les conditions ci-dessus.

      Par exemple, supposons qu’une requête a :

      where condition_x or condition_y

      L’optimisation de la recherche peut améliorer les performances si chaque condition renvoie séparément quelques lignes (par exemple, condition_x renvoie quelques lignes et condition_y renvoie quelques lignes).

      Si condition_x renvoie quelques lignes mais que condition_y renvoie de nombreuses lignes, les performances de la requête ne bénéficient pas de l’optimisation de la recherche.

      Dans le cas de disjonctions, chaque prédicat isolé n’est pas déterminant dans la requête. Les autres prédicats doivent être évalués avant de pouvoir déterminer si l’optimisation de la recherche peut améliorer les performances.

    • Pour les requêtes qui utilisent des conjonctions et des disjonctions (c’est-à-dire AND et OR), le service d’optimisation de la recherche peut améliorer les performances uniquement si AND est au niveau supérieur.

      Par exemple, le service d’optimisation de la recherche peut améliorer les performances d’une requête avec :

      WHERE a = 1 AND (b = 2 OR c = 3)

      mais pas avec :

      WHERE a = 1 OR (b = 2 AND c = 3)

Comment l’optimisation de la recherche affecte les jointures

Le service d’optimisation de la recherche n’améliore pas directement les performances des jointures. Cependant, il peut améliorer les performances de filtrage des lignes d’une table avant la jointure, si l’optimisation de la recherche est activée dans la table et si le prédicat est sélectif.

L’optimisation de la recherche n’a pas besoin d’être activée dans les deux tables. La décision d’utiliser l’optimisation de la recherche est prise pour chaque table indépendamment.

Comment l’optimisation de la recherche affecte les vues

Comme pour les jointures, le service d’optimisation de la recherche peut indirectement améliorer les performances des vues. Si l’optimisation de la recherche est activée dans une table de base pour une vue et si la requête utilise un prédicat sélectif pour cette table, le service d’optimisation de la recherche peut améliorer les performances lors du filtrage des lignes.

L’optimisation de la recherche n’a pas besoin d’être activée sur toutes les tables de la vue. L’optimisation de la recherche est effectuée sur chaque table indépendamment.

Limites actuelles liées au service d’optimisation de la recherche

Le service d’optimisation de la recherche ne prend pas en charge les éléments suivants :

  • Tables externes.

  • Vues matérialisées.

  • Concaténation de colonnes.

  • Expressions analytiques.

  • Conversions sur des colonnes de tables.

    Bien que l’optimisation de la recherche prenne en charge les prédicats avec des conversions implicites et explicites sur les valeurs constantes, elle ne prend pas en charge les prédicats qui convertissent des valeurs dans la colonne de table réelle.

    Par exemple, les prédicats suivants sont pris en charge, car ils utilisent des conversions implicites et explicites sur des valeurs constantes (et non des valeurs dans la colonne du tableau) :

    -- Supported predicate
    -- (where the numeric constant 3 is implicitly cast to a varchar)
    WHERE varchar_column = 3
    
    -- Supported predicate
    -- (where the numeric constant 3 is explicitly cast to a varchar)
    WHERE varchar_column = to_varchar(3)
    

    Le prédicat suivant n’est pas pris en charge, car il utilise une conversion sur des valeurs dans la colonne de table :

    -- Unsupported predicate
    -- (where values in a numeric column are cast to a string)
    WHERE cast(numeric_column as varchar) = '2'
    

    Le service d’optimisation de la recherche prend en compte les valeurs de colonne d’origine et non les valeurs après la conversion. Par conséquent, le service d’optimisation de la recherche n’est pas utilisé pour les requêtes avec ces prédicats.

L’optimisation de la recherche n’améliore pas les performances des requêtes qui utilisent Time Travel, car l’optimisation de la recherche ne fonctionne que sur les données actives.

Types de données pris en charge par le service d’optimisation de la recherche

Le service d’optimisation de la recherche prend actuellement en charge les prédicats d’égalité et les recherches de prédicat de liste IN pour les types de données suivants (c’est-à-dire les types de données de taille fixe) :

  • Nombres à virgule fixe (par exemple INTEGER, NUMERIC).

  • DATE, TIME et TIMESTAMP.

  • VARCHAR.

  • BINARY.

Actuellement, le service d’optimisation de la recherche ne prend pas en charge les types de données à virgule flottante, les types de données semi-structurés ou d’autres types de données non répertoriés ci-dessus. Snowflake pourrait prendre en charge d’autres types de données à l’avenir.

Ajout de l’optimisation de la recherche à une table

Pour ajouter une optimisation de recherche à une table, procédez comme suit :

  1. Basculez vers un rôle qui a les privilèges permettant d’ajouter l’optimisation de la recherche à la table.

  2. Exécutez la commande suivante :

    ALTER TABLE [IF EXISTS] <table_name> ADD SEARCH OPTIMIZATION;
    

    nom_table est le nom de la table pour laquelle ajouter l’optimisation de la recherche.

    Par exemple :

    alter table test_table add search optimization;
    

    Pour plus d’informations, consultez la section sur l’optimisation de la recherche dans ALTER TABLE.

  3. Exécutez la commande SHOW TABLES pour vérifier que l’optimisation de la recherche a été ajoutée et pour déterminer la proportion de la table qui a été optimisée.

    Par exemple :

    SHOW TABLES LIKE '%test_table%';
    

    Dans la sortie de cette commande :

    • Vérifiez que SEARCH_OPTIMIZATION est ON, ce qui indique que l’optimisation de la recherche a été ajoutée.

    • Vérifiez la valeur de SEARCH_OPTIMIZATION_PROGRESS. Cela spécifie le pourcentage de la table qui a été optimisé jusqu’à présent.

      Lorsque l’optimisation de la recherche est ajoutée pour la première fois à un tableau, les avantages en termes de performances n’apparaissent pas immédiatement. Le service d’optimisation de la recherche commence à remplir les données en arrière-plan. Les avantages apparaissent de plus en plus à mesure que la maintenance rattrape l’état actuel de la table.

      Avant d’exécuter une requête pour vérifier que l’optimisation de la recherche fonctionne, attendez que cela indique que la table a été entièrement optimisée.

  4. Exécutez une requête pour vérifier que l’optimisation de la recherche fonctionne.

    Notez que l’optimiseur Snowflake choisit automatiquement quand utiliser le service d’optimisation de la recherche pour une requête particulière. Les utilisateurs ne peuvent pas contrôler les requêtes pour lesquelles l’optimisation de la recherche est utilisée.

    Choisissez une requête que le service d’optimisation de la recherche est conçu pour optimiser. Voir Identification des tables qui bénéficient de l’optimisation de la recherche.

  5. Sur l’UI Web, affichez le plan de requête pour cette requête et vérifiez que le nœud de requête « Accès optimisation recherche » fait partie du plan de requête.

Gestion des coûts du service d’optimisation de la recherche

Le service d’optimisation de la recherche a un impact sur les coûts des ressources de stockage et de calcul :

  • Ressources de stockage : le service d’optimisation de la recherche crée une structure de données de chemin d’accès à la recherche qui nécessite de l’espace pour chaque table sur laquelle l’optimisation de la recherche est activée. Le coût de stockage du chemin d’accès de recherche dépend de plusieurs facteurs, notamment :

    • Le nombre de valeurs distinctes (NDVs) dans la table. Dans le cas extrême où toutes les colonnes contiennent des types de données qui utilisent le chemin d’accès de recherche et où toutes les valeurs de données dans chaque colonne sont uniques, le stockage requis peut être autant important que la taille de la table d’origine.

      Cependant, la taille correspond généralement à environ 1/4 de la taille de la table d’origine.

  • Ressources de calcul :

    • L’ajout d’une optimisation de recherche à une table consomme des ressources.

    • La maintenance du service d’optimisation de la recherche nécessite également des ressources. La consommation de ressources est plus élevée en cas de roulement élevé (c’est-à-dire lorsque de grands volumes de données changent dans la table). Ces coûts sont à peu près proportionnels à la quantité de données ingérées (ajoutées ou modifiées). Les suppressions ont également un certain coût.

      Snowflake garantit une utilisation efficace du crédit en ne facturant à votre compte que les ressources réellement utilisées. La facturation est calculée par incréments d’une seconde.

      Reportez-vous au « Tableau des crédits de fonctionnalité sans serveur » dans le Tableau de consommation du service Snowflake pour les coûts par heure de calcul.

      Une fois que vous avez activé le service d’optimisation de la recherche, vous pouvez afficher les coûts liés à votre utilisation du service.

Astuce

Snowflake recommande de commencer lentement avec cette fonctionnalité (c’est-à-dire d’ajouter l’optimisation de la recherche à seulement quelques tables au début) et de surveiller attentivement les coûts et les avantages.

Estimation des coûts

Pour estimer le coût de l’ajout d’une optimisation de recherche à une table, utilisez la fonction SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS.

En général, les coûts sont proportionnels aux facteurs suivants :

  • Le nombre de tables sur lesquelles la fonction est activée et le nombre de valeurs distinctes dans ces tables.

  • La quantité de données qui change dans ces tables.

Affichage des coûts

Vous pouvez afficher les coûts de facturation du service d’optimisation de la recherche à l’aide de l’interface Web ou de SQL. Voir Présentation de la facturation des fonctionnalités sans serveur.

Réduction des coûts

Vous pouvez contrôler le coût du service d’optimisation de la recherche en sélectionnant soigneusement les tables pour lesquelles activer l’optimisation de la recherche.

De plus, pour réduire le coût du service d’optimisation de la recherche :

  • Snowflake recommande les opérations DML par lots de la table :

    • DELETE: si les tables stockent des données pour la période la plus récente (par exemple, le jour, la semaine ou le mois le plus récent), lorsque vous ajustez votre table en supprimant les anciennes données, le service d’optimisation de la recherche doit prendre en compte ces mises à jour. Dans certains cas, vous pourrez peut-être réduire les coûts en effectuant des suppressions moins fréquemment (par exemple, tous les jours plutôt que toutes les heures).

    • INSERT, UPDATE, et MERGE : le traitement par lots de ces types d’instructions DML sur la table peut réduire le coût de la maintenance par le service d’optimisation de la recherche.

  • Si vous effectuez un reclustering de la table entière, pensez à détruire la propriété d’optimisation de la recherche pour cette table avant d’effectuer le reclustering, puis à rajouter le service d’optimisation de recherche après le reclustering.

Suppression de la propriété d’optimisation de la recherche d’une table

Pour supprimer la propriété d’optimisation de la recherche d’une table :

  1. Basculez vers un rôle qui a les privilèges permettant de supprimer l’optimisation de la recherche de la table.

  2. Exécutez la commande suivante :

    ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
    

    Par exemple :

    alter table test_table drop search optimization;
    

    Pour plus d’informations, consultez la section sur l’optimisation de la recherche dans ALTER TABLE.

Exemples

Le code suivant montre la création et l’utilisation d’un service d’optimisation de la recherche.

Commencez par créer une table avec des données :

create or replace table test_table (id int, c1 int, c2 string, c3 date) as
select * from values
  (1, 3, '4',  '1985-05-11'),
  (2, 4, '3',  '1996-12-20'),
  (3, 2, '1',  '1974-02-03'),
  (4, 1, '2',  '2004-03-09'),
  (5, null, null,  null);

Ajoutez la propriété d’optimisation de la recherche à la table :

alter table test_table add search optimization;

Les requêtes suivantes peuvent utiliser le service d’optimisation de la recherche :

select * from test_table where id = 2;
select * from test_table where c2 = '1';
select * from test_table where c3 = '1985-05-11';
select * from test_table where c1 is null;
select * from test_table where c1 = 4 and c3 = '1996-12-20';

La requête suivante peut utiliser l’optimisation de la recherche car la conversion implicite se trouve sur la constante, pas sur la colonne :

select * from test_table where c2 = 1;

Ce qui suit ne peut pas utiliser l’optimisation de la recherche car la conversion se fait sur la colonne de la table :

select * from test_table where cast(c1 as string) = '2';

Une clause IN est compatible avec l’optimisation de la recherche :

select id, c1, c2, c3
    from test_table
    where id IN (2, 3)
    order by id;

Si les prédicats sont individuellement compatibles avec l’optimisation de la recherche, ils peuvent être joints par la conjonction AND et toujours compatibles avec l’optimisation de la recherche :

select id, c1, c2, c3
    from test_table
    where c1 = 1
       and
          c3 = TO_DATE('2004-03-09')
    order by id;

DELETE et UPDATE (et MERGE) peuvent également utiliser le service d’optimisation de la recherche :

delete from test_table where id = 3;
update test_table set c1 = 99 where id = 4;