Volltextsuche in Blob möglichst schnell

[Teekeks]

Hallo!
Ich habe eine Tabelle mit einigen Datensätzen (sagen wir einmal 100) in der jeweils in einem Blob (sub_type 1) ein langer Text gespeichert wird.
Jetzt möchte ich möglichst schnell in diesem Blob nach verschiedenen Begriffen suchen und die Anzahl der unterschiedlichen Begriffe die in dem Text vorkommen zurückgeben.

Im Grunde gesehen sollte das doch genau das sein, was in der Suche eines Forums gemacht wird.
Wie wird das dort umgesetzt?
Oder: was könnt ihr mir hier empfehlen?

Vielen Dank schon einmal für eure Hilfe!
Gruß Teekeks

[mkinzler]

Das Problem ist, dass Blobs nicht indiziert werden. Beim Durchsuchen wird jeder Datensatz angefasst.
Btw. 100 Datensätze sind ja nicht viel

[s.h.a.r.k]

Warum speicherst du den Text in einem Blob und nicht in einem Text-Feld?

[mschaefer]

Wenn Du nach mittelständigen Begriffen suchst ist das ein SQL-Like-statement z.B.: 'Like %Begriff%'. Schneller wird das wenn Du dies, wie s.h.a.r.k. anmerkt, in ein Textfeld wandelst. Indizieren dürfte nicht viel bis nichts bringen.

[mkinzler]

Man müsste einen Fulltextindex implementieren. Technisch sollte keine Unterschied zwischen einem langen VARCHAR-Feld und einem Textblob bestehen.

http://www.firebirdfaq.org/faq328/

[Teekeks]

Doch, es gibt einen Unterschied: Ich kann keinen Varchar mit z.B. 10000 Zeichen anlegen! Wenn ich dies Versuche, bekomme ich folgende Nachricht:

Zitat:

Dynamic SQL Error
SQL error code = -204
Data type unknown
Implementation limit exceeded
COLUMN TEST

Ich habe mal mit dem Like rumgespielt (das hatte ich ganz vergessen dass es das gibt ^^)
Dabei herrausgekommen ist folgendes:

Code:

SELECT Count(*) FROM PE_REZEPTE a WHERE a.Text like '%Fenchel%' and a.nummer=2

Kann ich dass jetzt für eine Nummer auch auf mehrere Begriffe ausweiten? Also das ich nicht nur nach "Fenchel" suche sondern auch z.B. nach "Banane"?
Weil jetzt wird ja nur zurück gegeben ob der eine Begriff da ist oder nicht, wobei sich Count ja momentan auf die anzahl der zurückgegebenen Datensätze (also ja Maximal 1 wegen dem eindeutigen Feld Nummer) bezieht.

Liege ich da jetzt mit meinen Gedankenwegen auf dem richtigen Weg oder soll ich doch anders vorgehen?

Danke schon einmal für eure Hilfe bis hierhin!

[mkinzler]

Eigentlich sollte die Beschränkung bei 32768 sein. Ab Firebird 2.1 werden Textblobs unterhalb dieser Grenze sowieso wie ein VARCHAR-Feld behandelt.

[Teekeks]

Hmmm gut. Wir haben schon mal Firebird 2.1 am laufen.
Zeichensatz ist UTF8.
Wie viele sind den dort maximal möglich?
Laut einem Text von 2006 ist da maximal etwa 10000 Zeichen möglich, aber auch schon 4000 schlagen ja fehl.

[mkinzler]

Die Begrenzung ist wie gesagt 32765. Bei Firebird >=2.5 wird dann auch ein Textblob der kürzer ist wie ein VARCHAR-Feld behandelt

Zitat:

BLOB Subtype 1 Compatibility with VARCHAR
A. dos Santos Fernandes
(v.2.1) At various levels of evaluation, the engine now treats text BLOBs that are within the 32,765-byte string size limit as though they were VARCHARs. Operations that now allow text BLOBs to behave like strings are:
• Assignments, conversions and concatenations (|| operator)
• Operators = , <>, >, <, >=, <=, BETWEEN, IS [NOT] DISTINCT FROM
• Functions CAST, BIT_LENGTH, CHAR[ACTER]_LENGTH, OCTET_LENGTH, LEFT, RIGHT, HASH, LOWER, UPPER, LPAD, RPAD, TRIM, OVERLAY, REPLACE, POSITION, REVERSE, MINVALUE, MAXVALUE, SUBSTRING
• Existential predicators IN, ANY/SOME, ALL • Search predicators CONTAINING, STARTING [WITH], LIKE
• A LIST expression. Note that, prior to v.2.1.4, the last part of the result may be truncated, an effect that applies to native VARCHAR columns also.

[Teekeks]

Woran liegt es dann aber das ich bei diesem SQL-Befehl:

Code:

ALTER TABLE PE_REZEPTE ADD 
test Varchar(10000);

Diese Fehlermeldung bekomme?

Zitat:

Dynamic SQL Error
SQL error code = -204
Data type unknown
Implementation limit exceeded
COLUMN TEST

Könnte das vl an Flamerobin liegen (mit diesem Programm lege ich die Felder an).
Irgendeinen Grund muss das doch haben dass es normalerweise geht, aber hier nicht. :gruebel:

Gruß Teekeks

[FredlFesl]

Hmmmm. vielleicht solltest du einen anderen Ansatz wählen. Teile den Text doch in Wörter auf und schmeiss die Wörter in eine separate Tabelle: Jedes Wort bekommt dabei einen Verweis auf den Originaltext, bzw. seinen PK.

Dann kannst Du in der Worttabelle sehr schnell suchen, sofern du nach Wörtern bzw. dem Wortanfang suchst. Wenn Du dann noch die Wortposition mit speicherst (also das wievielte Wort im Text es ist), kannst Du sogar nach Inhalten suchen... Dann dürfen die gesuchten Worte nicht zu weit voneinander entfernt sein, d.h. die Wortpositionen sind z.B. < 10 oder so.

Auf 32k-Begrenzungen würde ich mich nicht einlassen, denn eine Beschränkung auf einer derart atomaren Ebene ist ein Designfehler.

[mkinzler]

Ich hab es mal in IBExpert getestet. Hat problemlos funktioniert. Scheint also ein Problem von FlameRobin zu sein.

Zitat:

Hmmmm. vielleicht solltest du einen anderen Ansatz wählen ...

Nennt man Volltextindex und wurde schon als mögliche Lösung benannt.

Zitat:

Auf 32k-Begrenzungen würde ich mich nicht einlassen, denn eine Beschränkung auf einer derart atomaren Ebene ist ein Designfehler.

Diese Aussage verstehe ich nicht ganz.
Erstens sind die meisten Texte kürzer.
Zweitens kann man bei längerer Texten ja eine Textblob verwenden, dann existiert keine Beschränkung ( höchtens der Größe der Festplatte)
Bei Firebird >=2.1 würde ich auf jeden Fall einen Blob verwenden, weil man dann die Vorteile beider Implementierungen nutzen kann (siehe oben)

[Teekeks]

Eigendlich reicht mir das Blob ja.
Und es wird ja sowieso als VarChar behandelt. In so fern sollte dass ja dann auch egal sein.
Ich habe ja schon weiter oben einen Teilansatz gefunden, aber irgendwie war meine Frage dann untergegangen.
Jetzt also noch mal:

Zitat:

Code:

SELECT Count(*) FROM PE_REZEPTE a WHERE a.Text like '%Fenchel%' and a.nummer=2

Kann ich dass jetzt für eine Nummer auch auf mehrere Begriffe ausweiten? Also das ich nicht nur nach "Fenchel" suche sondern auch z.B. nach "Banane"?
Weil jetzt wird ja nur zurück gegeben ob der eine Begriff da ist oder nicht, wobei sich Count ja momentan auf die anzahl der zurückgegebenen Datensätze (also ja Maximal 1 wegen dem eindeutigen Feld Nummer) bezieht.

Noch einmal danke für eure rege Beteiligung an der Diskussion!

[mkinzler]

Du kannst natürlich per OR mehrere Suchen kombinieren. Aber ohne einen Index wird das dann nicht gerade performant sein.

[Teekeks]

Code:

SELECT Count(*) FROM PE_REZEPTE a WHERE (a.Text like '%Fenchel%' and a.nummer=2) or (a.Text like '%gut%' and a.nummer=2)

Das scheint es nicht zu sein was du meinst.

Noch mal zur verdeutlichung:
Wenn im Text sowol das Wort Fenchel als auch gut vorkommt, soll die zahl 2 zurück gegeben werden.
Bein nur einem der beiden eine 1 und bei keinem eine 0.

Bei meinem oberen Beispiel kommt (obwohl beide Begriffe vorkommen) die zahl 1 zurück.
Wobei ich ja nur die Where-Klausel mit or verknüpft habe und nicht die Abfrage.

[mkinzler]

Z.B. so

SQL-Code:

select 
    sum( wert) 
from (
        select 
            count( *) as wert 
        FROM 
            PE_REZEPTE WHERE Text containing :such1 and a.nummer=:nummer  
    union  
        select 
            count( *) as wert 
        FROM 
            PE_REZEPTE WHERE Text containing :such2 and a.nummer=:nummer);

Wobei dann die Suchwörter nicht öfters im Text vorkommen dürfen

[Teekeks]

hmm, Nein.
Damit:

SQL-Code:

select
    sum( wert)
from (
        select
            count( *) as wert
        FROM
            PE_REZEPTE WHERE Text containing 'Abendessen' and nummer=1
    union
        select
            count( *) as wert
        FROM
            PE_REZEPTE WHERE Text containing 'Abendessen' and nummer=1);

Müsste ich ja auf jeden Fall 2 herausbekommen, es kommt jedoch nur 1 heraus. :(

[mkinzler]

Was liefert der Union allein?

[Teekeks]

Ebenfalls 1, auch bei verschiedenen Begriffen.

Ah, was Passiert denn wenn die Suchbegriffe öfters vorkommen?

[mkinzler]

Komisch, der Union scheint einen Datensatz nicht mehrfach auszugeben

SQL-Code:

execute block
returns (ret smallint )
as
declare variable w smallint;
begin
    w = 0;
    ret = 0;
    select count (*) from PE_REZEPT where text containing 'Abendessen' and nummer = 2 into :w;
    if (w > 0) then ret = 1;
    select count (*) from PE_REZEPT where text containing 'Brot' and nummer = 2 into :w;
    if (w > 0) then ret = :ret + 1;
    Suspend;
end;

[Teekeks]

Hmm, Kann ich an diese SQL-Procedure auch eine Variable Menge von Parametern übergeben?
Es ist nämlich so dass es eine unterschiedliche Menge an Begriffen sein kann.
Bei einer einfachen Abfrage hätte ich diese ja auch noch entsprechend der Parameter zusammensetzen können.
Das geht ja bei einer Procedure nicht.

Und noch ein weiteres mal danke das du dir so eine Mühe mit mir machst!

[mkinzler]

Da das keine Prozedur, sondern ein Execute Block ist, kannst du den natürlich auch variabel machen.

Ich würde es aber als Prozedur mit einer Variablen implementieren und dann diese pro Suchbegriff aufrufen und die Rückgabewerte dann addieren.

[Teekeks]

Hmm, wenn ich das Ausführen möchte (mit FlameRobin) kommt:

Zitat:

Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 4, column 20

Er Bemängelt das ; nach smalint...
Das sollte aber doch richtig sein (sagt mein Tutorial hier ^^)

Zu dem jeweils pro Suchbegriff aufrufen:
Das wären dann auf 100 Datensätze mit 5 Verschiedenen Gruppen zu jeweils 3 Suchbegriffen allerdings 1500 SQL-Abfragen. Das ist glaube ich nicht sehr Performat :(

Ich schreibe mir mal schnell ein testprojekt mit dem ich testen kann ob das an FlameRobin liegt (muss ja).

Ok, Er bemängelt auch bei einer normalen Abfrage per Zeos die selbe Stelle.

[mkinzler]

Ich würde dann einen Volltextindex aufbauen.

Sind die Gruppen/Suchbegriffe fest vorgegeben?

[Teekeks]

Nun, es gibt fest 5 Gruppen.
Die Anzahl der Suchbegriffe variiert jedoch von Gruppe zu Gruppe und auch von Fall zu Fall.

Wie funktioniert sowas mit Volltextindex?
Hast du da vl auch etwas Lektüre damit ich mich da einlesen kann?

[mkinzler]

Beim Volltextindex werden die Wörter in einer Wortliste geführt. Und dann die Zuordnungen jedes Dokumentes zu den Wörtern

Siehe auch #5

[Teekeks]

Ahh!
Das klingt gut (womit wir wieder beim Anfang des Themas wären ^^).
Damit werde ich mich mal Beschäftigen.
Und diese Liste kann ich noch weiter verkleinern indem ich z.B. klein geschriebene Wörter nicht mit Indiziere (es geht immer nur um Nahrungsmittel, die werden ja alle Groß geschrieben) und jedes Wort nur 1x vorkommen lasse je Text.
Ich werde mir das mal genau ansehen!

Danke für deine Hilfe!

Gruß Teekeks

[mkinzler]

Du kannst die Sache aus nicht casesensitiv machen

[FredlFesl]

Zitat:

Zitat von mkinzler (Beitrag 1108424)

Beim Volltextindex werden die Wörter in einer Wortliste geführt. Und dann die Zuordnungen jedes Dokumentes zu den Wörtern

Siehe auch #5

Und ich dachte, ein Volltextindex ist was waahnsinnig komplex-kompliziertes, was entweder fest in einer DB untergebracht ist, oder nicht. Und wenn nicht, guckt man dumm aus der Wäsche.

[mkinzler]

Manche DBMS unterstützen das von Haus aus. Rein technisch wird es aber ähnlich gelöst

[Teekeks]

Ich habe das ganze so verstanden das in einer Tabelle Alle (unterschiedlichen) Wörter aller Texte mit einem Verweis auf den jeweiligen Text gespeichert werden.
Jetzt legt man einen Index auf beides und schon kann man sehr schnell herausfinden ob ein bestimmtes Wort in einem Text vorkommt.
Das ist vermutlich nicht ganz das was normalerweise gemacht wird, aber das wäre genau das was ich benötige.

[mkinzler]

Nein nicht ganz, in einer Tabelle stehen die möglichen Wörter in einer 2. die Zuordnungen zu den Textfeldern mit z.B. der Position im Text.
Willst du nun herausfinden, ob ein Wort in einem Text vorkommt, musst du nur Abfragen, ob mindestens eine Zuordnung wischen dem Text und dem Wort besteht.

[jobo]

Hier ist eine schöne Aufstellung, wann man mit FB BLOB oder Varchar benutzen kann/soll, allerdings auf Englisch.
http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm

Zur max. Länge bei Varchar, es kommt neben der FB-Version vermutlich auf den Charset an und evtl. auch auf 32/64bit System. Gerade bei BLOB und der Anforderung maximaler Größe macht es ja auch Sinn, die "Breite" des Systems auszunutzen. Beim Multibyte Charset wäre die Frage, ob es 2 Byte, 3 oder 4 sein müssen. (UTF8, Unicode usw.)
Aber ein Rezept für 1000 jährige Eier in Kantonesisch hat sicher seinen optischen Reiz. ;)

Noch eine Varchar Variante (eignet sich für "einfache DB" und kombiniert BLOB Vorteil GROSS mit Varchar Vorteil INDEX usw)
Der Text wird in "mundgerechte" Stücke zerteilt. Eine separate Tabelle nimmt dann je Text die ID des Rezeptes sowie die Datensätze auf, die aus einer passenden Stückelung resultieren.
Die Spaltendefinition ist z.B. Varchar(100). Mit einer SP wird der Text angenommen und dann in ca 100 Zeichen Teilen eingefügt, immer abgetrennt nach dem letzten Wort, das noch rein passt. Als Variante könnte man auch eine Trennung nach Absätzen oder so machen (also CR/LF, Satzende, Halbsatz (Komma)..). Dann muss die Varcharspalte natürlich doch möglichst groß sein.
Je nach Verfahren bekommst Du dann bei der Abfrage automatisch noch die Satz oder Absatz Nummer ausgegeben, wenn Du einen entsprechenden Zusatzschlüssel für die Zeilen verwendest. Den kann man evtl. zur Visualisierung verwenden.

[Morphie]

Wie würdet ihr denn so eine Indizierung realisieren?
Ich habe es bis jetzt so gemacht, dass ich zwei Trigger in einer Tabelle habe:

1. wird bei DELETE ausgelöst. Hier werden dann die Datensätze in der Index-Tabelle gelöscht. (könnte man wohl auch anders lösen, z.B. über Fremdschlüssel / Referentielle Integrität)

2. wird beim UPDATE / INSERT geprüft, ob sich der Text im Blob-Feld geändert hat. Wenn ja werden die Einträge aus der Index-Tabelle gelöscht. Danach wird der Text aus dem Blob in einzelne Wörter aufgeteilt und wieder in die Index-Tabelle geschrieben.

Hier würde mich speziell interessieren, mit welchem Code ihr Punkt 2 lösen würdet. Oder ob ihr generell einen anderen Weg gehen würdet...

[Nersgatt]

Zitat:

Zitat von mkinzler (Beitrag 1108406)

Komisch, der Union scheint einen Datensatz nicht mehrfach auszugeben

Hab jetzt auf die Schnelle in dem Thread nicht gefunden, ob das geklärt wurde. Dafür gibts UNION ALL:

Code:

select 1 as wert from rdb$database
union
select 1 as wert from rdb$database

liefert einen Datensatz

Code:

select 1 as wert from rdb$database
union all
select 1 as wert from rdb$database

liefert 2 Datensätze