Verwenden des Suchoptimierungsdienstes

Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen erheblich verbessern.

Unter diesem Thema:

Grundlegendes zum Suchoptimierungsdienst

Der Suchoptimierungsdienst zielt darauf ab, die Leistung selektiver Punktsuchabfragen in großen Tabellen erheblich zu verbessern. Eine Punktsuchabfrage gibt nur eine oder eine geringe Anzahl unterschiedlicher Zeilen zurück. Typische Anwendungsfälle sind z. B.:

  • Geschäftsanwender, die schnelle Antwortzeiten bei kritischen Dashboards mit hochselektiven Filtern benötigen.

  • Datenwissenschaftler, die große Datenmengen untersuchen und nach bestimmten Teilmengen von Daten suchen.

Ein Benutzer kann eine oder mehrere Tabellen beim Suchoptimierungsdienst registrieren. Die Suchoptimierung ist eine Eigenschaft auf Tabellenebene und gilt für alle Spalten mit unterstützten Datentypen (siehe Liste der unterstützten Datentypen weiter unten).

Wie funktioniert der Suchoptimierungsdienst?

Um die Leistung bei der Punktsuche zu verbessern, stützt sich der Suchoptimierungsdienst auf eine persistente Datenstruktur, die als optimierter Suchzugriffspfad dient.

Ein im Hintergrund ausgeführter Wartungsdienst ist für die Erstellung und Pflege des Suchzugriffspfads verantwortlich:

  • Wenn Sie einer Tabelle eine Suchoptimierung hinzufügen, erstellt der Wartungsdienst den Suchzugriffspfad und füllt ihn mit den Daten, die zum Ausführen der Suchläufe erforderlich sind.

    Das Auffüllen von Daten kann abhängig von der Größe der Tabelle einige Zeit dauern. Der Dienst führt diese Arbeit im Hintergrund aus und blockiert keine gleichzeitigen Operationen in der Tabelle.

  • Wenn Daten in der Tabelle aktualisiert werden (z. B. durch Laden neuer Datasets oder durch DML-Operationen), aktualisiert der Wartungsdienst automatisch den Suchzugriffspfad, um die Änderungen an den Daten widerzuspiegeln.

    Wenn Abfragen ausgeführt werden, aber der Suchzugriffspfad noch nicht aktualisiert wurde, werden die Abfragen zwar möglicherweise langsamer ausgeführt, aber es werden immer aktuelle Ergebnisse zurückgegeben.

Suchzugriffspfad und Wartungsdienst sind für den Benutzer transparent. Sie müssen kein Warehouse für den Dienst erstellen, der den Suchzugriffspfad verwaltet.

Sie müssen sich jedoch des Dienstes bewusst sein, da für Speicher und Computeressourcen dieses Dienstes Kosten anfallen. Siehe Verwalten der Kosten des Suchoptimierungsdienstes.

Berücksichtigung anderer Lösungen zur Optimierung der Abfrageleistung

Der Suchoptimierungsdienst ist eine von mehreren Möglichkeiten zur Optimierung der Abfrageleistung. Verwandte Techniken sind:

  • Clustering von Tabellen

  • Erstellen von materialisierten Ansichten (gruppiert oder nicht gruppiert)

Jede Methode hat verschiedene Vorteile:

  • Das Clustering einer Tabelle kann eine der folgenden Aktionen beschleunigen, solange sie auf dem Gruppierungsschlüssel ausgeführt werden:

    • Bereichssuche

    • Gleichheitssuche

    Eine Tabelle kann jedoch nur auf einem einzigen Schlüssel gruppiert werden (der eine oder mehrere Spalten oder Ausdrücke umfassen kann).

  • Der Suchoptimierungsdienst beschleunigt nur die Gleichheitssuche. Dies gilt jedoch für alle Spalten unterstützter Typen in einer Tabelle, für die die Suchoptimierung aktiviert ist.

  • Eine materialisierte Ansicht beschleunigt sowohl die Gleichheitssuche als auch die Bereichssuche sowie einige Sortieroperationen, jedoch nur für die Teilmenge der Zeilen und Spalten, die in der materialisierten Ansicht enthalten sind. Materialisierte Ansichten können auch verwendet werden, um verschiedene Gruppierungsschlüssel in derselben Quelltabelle (oder einer Teilmenge dieser Tabelle) oder in Verbindung mit dem Vereinfachen von JSON-/Variant-Daten zu definieren.

In der folgenden Tabelle wird zeigt, welche dieser drei Optimierungsverfahren Speicher- oder Computekosten verursachen:

Speicherkosten

Computekosten

Suchoptimierungsdienst

Materialisierte Ansicht

Clustering der Tabelle

Welche Zugriffssteuerungsrechte sind für den Suchoptimierungsdienst erforderlich?

Um eine Suchoptimierung für eine Tabelle hinzuzufügen oder zu entfernen, müssen Sie über die folgenden Berechtigungen verfügen:

  • Berechtigung OWNERSHIP für die Tabelle

  • Berechtigung ADD SEARCH OPTIMIZATION für das Schema, das die Tabelle enthält

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

Um den Suchoptimierungsdienst für eine Abfrage zu verwenden, benötigen Sie nur SELECT-Berechtigungen für die Tabelle.

Sie benötigen keine zusätzlichen Berechtigungen. Da die Suchoptimierung eine Tabelleneigenschaft ist, wird sie beim Abfragen einer Tabelle automatisch erkannt und (falls zutreffend) verwendet.

Identifizieren der Tabellen, die von der Suchoptimierung profitieren

Der Suchoptimierungsdienst wurde entwickelt, um die Leistung bestimmter Arten von Abfragen zu verbessern. In den folgenden Abschnitten wird erläutert, wie Sie die Tabellen und Abfragetypen identifizieren, die von der Suchoptimierung profitieren können.

Abfragen, die von der Suchoptimierung profitieren

Die Suchoptimierung verbessert die Leistung einer Abfrage am besten, wenn die folgenden Bedingungen erfüllt sind:

  • Für die abgefragte Tabelle:

    • Die Tabellengröße beträgt mindestens 100 GB.

      Bei kleineren Tabellen (z. B. mit einer Größe von weniger als 10 GB) verbessert der Suchoptimierungsdienst die Abfrageleistung nicht ausreichend, um die Kosten zu rechtfertigen.

    • Eine der folgenden Aussagen ist gültig:

      • Die Tabelle ist nicht geclustert.

      • Die Tabelle wird häufig über andere Spalten als dem primären Gruppierungsschlüssel abgefragt.

  • Für die Abfrage:

    • Die Ausführung der Abfragen dauert einige Sekunden.

    • Mindestens eine der Spalten, auf die über die Abfragefilteroperation zugegriffen wird, weist mindestens 100.000–200.000 unterschiedliche Werte auf.

      Um die Anzahl der unterschiedlichen Werte zu bestimmen, können Sie eine der folgenden Methoden verwenden:

      • Verwenden Sie APPROX_COUNT_DISTINCT, um die ungefähre Anzahl unterschiedlicher Werte zu erhalten:

        select approx_count_distinct(column1) from table1;
        
      • Verwenden Sie COUNT(DISTINCT <Spaltenname>), um die tatsächliche Anzahl unterschiedlicher Werte zu erhalten:

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

      Da Sie nur eine Annäherung an die Anzahl der unterschiedlichen Werte benötigen, sollten Sie APPROX_COUNT_DISTINCT verwenden, das im Allgemeinen schneller und kostengünstiger als COUNT(DISTINCT <Spaltenname>) ist.

    • Die Abfrage verwendet die folgenden Arten von Prädikaten:

      • Gleichheitsprädikate (z. B. <Spaltenname> = <Konstante>).

      • Prädikate, die IN verwenden (siehe Beispiel).

    • Bei Abfragen, die Konjunktionen von Prädikaten (d. h. AND) verwenden, kann die Abfrageleistung durch Suchoptimierung verbessert werden, wenn eines der Prädikate die oben genannten Bedingungen erfüllt.

      Angenommen, eine Abfrage ist wie folgt definiert:

      where Bedingung_x and Bedingung_y

      Die Suchoptimierung kann die Leistung verbessern, wenn eine der Bedingungen einige Zeilen separat zurückgibt (d. h. Bedingung_x gibt einige Zeilen zurück oder Bedingung_y gibt einige Zeilen zurück).

      Wenn Bedingung_x einige Zeilen zurückgibt, aber Bedingung_y viele Zeilen zurückgibt, kann die Abfrageleistung von der Suchoptimierung profitieren.

      Siehe diese zusätzlichen Beispiele.

    • Bei Abfragen, die Disjunktionen von Prädikaten (d. h. OR) verwenden, kann die Abfrageleistung durch Suchoptimierung verbessert werden, wenn alle Prädikate die oben genannten Bedingungen erfüllt.

      Angenommen, eine Abfrage ist wie folgt definiert:

      where Bedingung_x or Bedingung_y

      Die Suchoptimierung kann die Leistung verbessern, wenn jede Bedingung separat einige Zeilen zurückgibt (d. h. Bedingung_x gibt einige Zeilen zurück und Bedingung_y gibt einige Zeilen zurück).

      Wenn Bedingung_x einige Zeilen zurückgibt, aber Bedingung_y viele Zeilen zurückgibt, profitiert die Abfrageleistung nicht von der Suchoptimierung.

      Bei Disjunktionen ist jedes Prädikat für sich genommen nicht entscheidend für die Abfrage. Die anderen Prädikate müssen ausgewertet werden, bevor festgestellt werden kann, ob die Suchoptimierung die Leistung verbessern kann.

    • Bei Abfragen, die Konjunktionen und Disjunktionen (z. B. AND und OR) verwenden, kann der Suchoptimierungsdienst die Leistung nur verbessern, wenn sich AND auf oberster Ebene befindet.

      Beispielsweise kann der Suchoptimierungsdienst die Leistung einer Abfrage verbessern mit:

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

      aber nicht mit:

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

Auswirkung der Suchoptimierung auf Verknüpfungen (Joins)

Der Suchoptimierungsdienst verbessert die Leistung von Joins nicht direkt. Es kann jedoch die Leistung beim Filtern von Zeilen aus beiden Tabellen vor dem Join verbessern, wenn für die Tabelle die Suchoptimierung aktiviert ist und das Prädikat selektiv ist.

Für beide Tabellen muss die Suchoptimierung nicht aktiviert sein. Die Entscheidung für die Suchoptimierung wird für jede Tabelle unabhängig getroffen.

Auswirkung der Suchoptimierung auf Ansichten (Views)

Wie bei Joins kann der Suchoptimierungsdienst indirekt die Leistung von Ansichten verbessern. Wenn für die Basistabelle einer Ansicht die Suchoptimierung aktiviert ist und die Abfrage ein selektives Prädikat für diese Tabelle verwendet, kann der Suchoptimierungsdienst die Leistung beim Filtern von Zeilen verbessern.

Die Suchoptimierung muss nicht für alle Tabellen in der Ansicht aktiviert sein. Die Suchoptimierung wird für jede Tabelle unabhängig durchgeführt.

Aktuelle Einschränkungen des Suchoptimierungsdienstes

Der Suchoptimierungsdienst unterstützt Folgendes nicht:

  • Externe Tabellen

  • Materialisierte Ansichten

  • Verketten von Spalten

  • Analytische Ausdrücke

  • Umwandlungen auf Tabellenspalten

    Obwohl die Suchoptimierung Prädikate mit impliziten und expliziten Umwandlungen auf Konstantenwerten unterstützt, werden keine Prädikate unterstützt, die Werte in der tatsächlichen Tabellenspalte umwandeln.

    Die folgenden Prädikate werden beispielsweise unterstützt, da sie die implizite und explizite Umwandlung von Konstantenwerten verwenden (nicht Werten in der Tabellenspalte):

    -- 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)
    

    Das folgende Prädikat wird nicht unterstützt, da es eine Umwandlung von Werten in der Tabellenspalte verwendet:

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

    Der Suchoptimierungsdienst berücksichtigt die ursprünglichen Spaltenwerte und nicht die Werte nach der Umwandlung. Daher wird der Suchoptimierungsdienst nicht für Abfragen mit diesen Prädikaten verwendet.

Die Suchoptimierung verbessert nicht die Leistung von Abfragen, die Time Travel verwenden, da die Suchoptimierung nur für aktive Daten funktioniert.

Vom Suchoptimierungsdienst unterstützte Datentypen

Der Suchoptimierungsdienst unterstützt derzeit Suchläufe mit Gleichheitsprädikaten und IN-Listenprädikaten mit folgenden Datentypen (d. h. Datentypen mit fester Größe):

  • Festkommazahlen (z. B. INTEGER, NUMERIC)

  • DATE, TIME und TIMESTAMP

  • VARCHAR

  • BINARY

Derzeit unterstützt der Suchoptimierungsdienst keine Gleitkomma-Datentypen, semistrukturierte Datentypen oder andere Datentypen, die oben nicht aufgeführt sind. Snowflake bietet möglicherweise in Zukunft Unterstützung von weiteren Datentypen.

Hinzufügen einer Suchoptimierung zu einer Tabelle

Gehen Sie folgendermaßen vor, um einer Tabelle eine Suchoptimierung hinzuzufügen:

  1. Wechseln Sie zu einer Rolle, die über Berechtigungen zum Hinzufügen der Suchoptimierung zu der Tabelle verfügt.

  2. Führen Sie den folgenden Befehl aus:

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

    Dabei ist Tabellenname der Name der Tabelle, für die die Suchoptimierung hinzugefügt werden soll.

    Beispiel:

    alter table test_table add search optimization;
    

    Weitere Informationen dazu finden Sie im Abschnitt zur Suchoptimierung in ALTER TABLE.

  3. Führen Sie den SHOW TABLES-Befehl aus, um zu überprüfen, ob die Suchoptimierung hinzugefügt wurde, und um festzustellen, in welchem Umfang die Tabelle optimiert wurde:

    Beispiel:

    SHOW TABLES LIKE '%test_table%';
    

    In der Ausgabe dieses Befehls:

    • Prüfen Sie, ob SEARCH_OPTIMIZATION auf ON gesetzt ist, was darauf hinweist, dass die Suchoptimierung hinzugefügt wurde.

    • Überprüfen Sie den Wert von SEARCH_OPTIMIZATION_PROGRESS. Dieser gibt den Prozentsatz der Tabelle an, der bisher optimiert wurde.

      Wenn die Suchoptimierung zum ersten Mal zu einer Tabelle hinzugefügt wird, zeigen sich die Leistungsvorteile nicht sofort. Der Suchoptimierungsdienst beginnt mit dem Auffüllen von Daten im Hintergrund. Die Vorteile zeigen sich nach und nach, bis die Wartung den aktuellen Stand der Tabelle erreicht.

      Bevor Sie eine Abfrage ausführen, um zu überprüfen, ob die Suchoptimierung funktioniert, warten Sie, bis dieser Wert anzeigt, dass die Tabelle vollständig optimiert wurde.

  4. Führen Sie eine Abfrage aus, um zu überprüfen, ob die Suchoptimierung funktioniert.

    Beachten Sie, dass das Snowflake-Optimierungsprogramm automatisch entscheidet, ob der Suchoptimierungsdienst für eine bestimmte Abfrage verwendet wird. Benutzer können nicht steuern, für welche Abfragen die Suchoptimierung verwendet wird.

    Wählen Sie eine Abfrage aus, die der Suchoptimierungsdienst optimieren soll. Siehe Identifizieren der Tabellen, die von der Suchoptimierung profitieren.

  5. Zeigen Sie auf der Web-UI den Abfrageplan für diese Abfrage an, und stellen Sie sicher, dass der Abfrageknoten „Search Optimization Access“-Teil des Abfrageplans ist.

Verwalten der Kosten des Suchoptimierungsdienstes

Der Suchoptimierungsdienst wirkt sich auf die Kosten für Speicher- und Computeressourcen aus:

  • Speicherressourcen: Der Suchoptimierungsdienst erstellt eine Datenstruktur für den Suchzugriffspfad, die Platz bei jeder Tabelle benötigt, für die die Suchoptimierung aktiviert ist. Die Speicherkosten für den Suchzugriffspfad hängen von mehreren Faktoren ab, darunter:

    • Anzahl unterschiedlicher Werte (NDVs) in der Tabelle. Im Extremfall, wenn alle Spalten Datentypen haben, die den Suchzugriffspfad verwenden, und alle Datenwerte in jeder Spalte eindeutig sind, kann der erforderliche Speicher so groß sein wie die Größe der Originaltabelle.

      In der Regel beträgt die Größe jedoch ungefähr 1/4 der ursprünglichen Tabellengröße.

  • Computeressourcen:

    • Das Hinzufügen einer Suchoptimierung zu einer Tabelle verbraucht Ressourcen.

    • Die Wartung des Suchoptimierungsdienstes erfordert ebenfalls Ressourcen. Der Ressourcenverbrauch ist höher, wenn eine hohe Änderungsrate vorliegt (d. h. wenn sich große Datenmengen in der Tabelle ändern). Diese Kosten sind in etwa proportional zur Menge der aufgenommenen (hinzugefügten oder geänderten) Daten. Löschungen verursachen ebenfalls einige Kosten.

      Snowflake stellt jedoch eine effiziente Credit-Nutzung sicher, indem nur die tatsächlich verwendeten Ressourcen in Rechnung gestellt werden. Die Abrechnung erfolgt in Schritten von 1 Sekunde.

      Die Kosten pro Computestunde finden Sie in der Tabelle „Serverless Feature Credits Table“ in der Snowflake Service Consumption Table.

      Sobald Sie den Suchoptimierungsdienst aktiviert haben, können Sie die Kosten für Ihre Nutzung des Dienstes anzeigen.

Tipp

Snowflake empfiehlt, langsam mit dieser Funktion zu beginnen (d. h. zunächst nur wenigen Tabellen eine Suchoptimierung hinzuzufügen) und Kosten und Nutzen genau zu überwachen.

Abschätzen der Kosten

Verwenden Sie die Funktion SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS, um die Kosten für das Hinzufügen einer Suchoptimierung zu einer Tabelle abzuschätzen.

Im Allgemeinen bewegen sich die Kosten proportional zu:

  • Anzahl der Tabellen, für die die Funktion aktiviert ist, und Anzahl der unterschiedlichen Werte in diesen Tabellen.

  • Datenmenge, die sich in diesen Tabellen ändert.

Anzeigen der Kosten

Sie können die Abrechnungskosten für den Suchoptimierungsdienst über die Weboberfläche oder mit SQL anzeigen. Siehe Erläuterungen zur Abrechnung von serverlosen Funktionen.

Senken der Kosten

Sie können die Kosten des Suchoptimierungsdienstes steuern, indem Sie sorgfältig die Tabellen auswählen, für die die Suchoptimierung aktiviert werden soll.

Außerdem können Sie die Kosten des Suchoptimierungsdienstes wie folgt senken:

  • Snowflake empfiehlt für DML-Operationen auf der Tabelle die Verwendung von Batches:

    • DELETE: Wenn in Tabellen Daten für den letzten Zeitraum (z. B. den letzten Tag, die letzte Woche oder den letzten Monat) gespeichert sind und Sie die Tabelle durch Löschen alter Daten verkleinern, muss der Suchoptimierungsdienst die Aktualisierungen berücksichtigen. In einigen Fällen können Sie Kosten ggf. senken, indem Sie Löschoperationen seltener ausführen (z. B. täglich anstatt stündlich).

    • INSERT, UPDATE und MERGE: Durch Verwenden von Batches für diese DML-Anweisungen auf der Tabelle können die Kosten zur Wartung des Suchoptimierungsdienstes gesenkt werden.

  • Wenn Sie ein Reclustering der gesamten Tabelle durchführen möchten, löschen Sie die Suchoptimierungseigenschaft für diese Tabelle vor dem Reclustering, und fügen Sie den Suchoptimierungsdienst nach dem Reclustering wieder hinzu.

Entfernen der Suchoptimierungseigenschaft aus einer Tabelle

So entfernen Sie die Suchoptimierungseigenschaft aus einer Tabelle:

  1. Wechseln Sie zu einer Rolle mit Berechtigungen zum Entfernen der Suchoptimierung aus der Tabelle.

  2. Führen Sie den folgenden Befehl aus:

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

    Beispiel:

    alter table test_table drop search optimization;
    

    Weitere Informationen dazu finden Sie im Abschnitt zur Suchoptimierung in ALTER TABLE.

Beispiele

Der folgende Code zeigt die Erstellung und Verwendung eines Suchoptimierungsdienstes.

Beginnen Sie, indem Sie eine Tabelle mit Daten erstellen:

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);

Fügen Sie die Suchoptimierungseigenschaft zu der Tabelle hinzu:

alter table test_table add search optimization;

Die folgenden Abfragen können den Suchoptimierungsdienst verwenden:

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';

Die folgende Abfrage kann die Suchoptimierung verwenden, da die implizite Umwandlung auf der Konstante und nicht auf der Spalte erfolgt:

select * from test_table where c2 = 1;

Im Folgenden kann die Suchoptimierung nicht verwendet werden, da die Umwandlung auf der Tabellenspalte erfolgt:

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

Eine IN-Klausel ist in folgenden Fällen mit der Suchoptimierung kompatibel:

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

Wenn die einzelnen Prädikate mit der Suchoptimierung kompatibel sind, können sie durch die Konjunktion AND verbunden werden und sind dennoch mit der Suchoptimierung kompatibel:

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

DELETE und UPDATE (und MERGE) sind ebenfalls mit dem Suchoptimierungsdienst kompatibel:

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