VARCHAR, VARCHAR(MAX), TEXT: Gibt es guten OnDisk-Space Vergleich verschiedener DB ?

[Rollo62]

Hallo zusammen,

es geht um die Anlage eines neuen Projektes, was eventuell mehrere Datenbanken unterstützen muss.
Ich werde für die Implementierung FireDAC benutzen, und hoffe dass dort schon ein paar Hilfen zum Abfangen von
Multi-DB Projekten eingebaut sind.
Ich habe aber bisher nur jeweils mit einzelnen DB gearbeitet (Sqlite, MySql, Firebird), und musste SQL nicht DB-übergreifend denken.
Mein Ziel wäre möglichst mit dem gleichen Satz SQL auf allen DB effizient zu arbeiten, also nicht 4 verschiedenen SQL-Varianten pflegen zu müssen.
(Da kann FireDAC sicher helfen, und SQL abstrahieren).
Insbesondere das Text speichern, normalerweise bis 255-512 aber teilweise auch viel größerer Textblöcke (BLOB) ist angedacht.

Ich frage mich
ob es einen guten Vergleich der Text Datentypen zum Unicode-String-Speichern der verschiedenen DB's im Web gibt,
insbesondere Speicherverbrauch, Performance Suchen/Indizieren, bei variabler Textlänge.
Welche Strategie / Datentype wäre da am Besten, um möglchst kompatibl und effizient zu bleiben ?

Es geht mir um folgende Punkte
- Texte mit variabler Länge ( die eventuell auch mal eine zu klein vorgegebene Länge sprengen könnten ).
- Z.B.: Namen: VARCHAR(80) - Flummi, Brummi, Zummi //<= Wie groß ist der OnDisk-Speicher versch. DB ?
- Z.B.: Namen: VARCHAR(80) - TextMitMehrAls80Zeichen => Könnte auch mal 90 Zeichen kommen, dann kracht es.
- Wieviel realer Speicher wird in den verschiedenen DB wirklich belegt ?
- Speichern von UniCode (UTF8) Texten muss möglich sein.
- Möglichst per Volltextsuche durchsuchbar, sollte einigermaßen effizient sein.
- Möglichst indizierbar (geht das überhaupt bei TextBlobs, oder muss ich selbst von außen Hashes verwalten ) ?
- Wie kann ich große TEXT Blobs effizient per SQL durchsuchen und in Queries einbinden ?
- Macht es Sinn große TEXT Blobs in separate Tabellen, mit HASH Columns, auszulagern, oder machen die modernen DB sowas automatisch ?
- Gibt es vielleicht sogar unterstützende Funktionen in FireDAC genau für solche Fälle ?

Ist also (Worst-Case) VARCHAR(N) oder VARCHAR(MAX) oder TEXT besser ?
- ich denke VARCHAR(N) N=255, als Angabe einer Worst-Case Größe die niemals überschritten wird, ist die bessere Strategie als VARCHAR(MAX)
- Aber speichern manche DB dann immer 255 Zeichen ab, auch wenn der durchschnittliche Text ca. 40 Zeichen ist ? Was nicht gut wäre.
- TEXT wird bei manchen DB wohl separat gespeichert, ist dann effizient gespeichert aber womöglich nicht performant beim Suchen.

Ich hoffe es gibt gute Vergleichsartikel oder Tipps zu dem Thema, in dem alle Datenbanken mal verglichen werden.

Ich hoffe ein paar DB-Experten können mich hier etwas aufklären.


Als Anregungen mal ein paar Infos, siehe unten:

Zitat:

Database string types comparison: VARCHAR(), VARCHAR(n), TEXT
=> here 20.01.22 varchar MySql
VARCHAR:
=> As the name suggests, varchar means character data that is varying.
# Also known as Variable Character, it is an indeterminate length string data type.
# It can hold numbers, letters and special characters.
# MsSql 2008+ can store up to 8000 characters as the maximum length of the string using varchar data type.
# SQL varchar usually holds 1 byte per character and 2 more bytes for the length information.
# It is recommended to use varchar as the data type when columns have variable length and
the actual data is way less than the given capacity.
VARCHAR(max):
=> How SQL varchar(max) is different from varchar(n)?
# There are times where SQL developers (including myself) usually define varchar datatype without a length,
and subsequently, are failed to insert string records in the SQL table.
This is because SQL Server allocates 1 character space as the default value to the varchar column
that is defined without any length.
# In practical scenarios, varchar(n) is used to store variable length value as a string, here ‘n’ denotes the string length
in bytes and it can go up to 8000 characters (MsSql).









# Example
CREATE TABLE Demovarchar
(
Id int NOT NULL IDENTITY(1,1),
LastName varchar(10),
FirstName varchar(10),
Gender varchar,
DepartmentName varchar(20),
Age int
)
INSERT INTO Demovarchar VALUES('Gilbert', 'Kevin','M','Tool Design',33) => OK

INSERT INTO Demovarchar VALUES('Newton Hamilton', 'Isaac','M','Design Head',69) => ERROR


=> VARCHAR( N ) vs. VARCHAR( MAX ):
=> Difference between the varchar(max) and varchar(n) data type [in MsSql ]
varchar(max) varchar(n)
# store up to 2 GB of data # store up to 8000 bytes data
# <= 8000 bytes it uses allocation unit IN_ROW_Data. # stores data in the standard data page
> 8000 bytes it uses LOB_Data page and
stores its pointer in the IN_ROW_Data page
# cannot create an index on the key column # can create an index on this data type
of the varchar(max) data type
# cannot compress the LOB data # can compress data for this data type
# Data retrieval and updation on the LOB data is slow


We do not face such issue in the varchar(n) data type
=> MsSql 2005+ got around this limitation of 8KB storage size and provided a workaround with varchar(max).
It is a non-Unicode large variable-length character data type and can store a maximum of 2^31-1 bytes (2 GB)
of non-Unicode characters.
CREATE TABLE Demovarcharmax
(
ID INT IDENTITY(1, 1) ,
StringCol VARCHAR(MAX)
)
INSERT INTO Demovarcharmax(StringCol) VALUES(REPLICATE(CAST('B' AS VARCHAR(MAX)), 15000))
SELECT Id, StringCol,len(StringCol) AS LengthOfString FROM Demovarcharmax

=> One limitation of using varchar(max) is we cannot create an index that has a varchar(max) as a key column,
instead, it is advisable to do a Full-text index on that column.
=> Do NOT consider it as the varchar with default value = 1.
=> MsSql 2019+, announced support for UTF-8 character encoding to the existing data types (varchar and char).
Reduction in storage and performance improvements
UTF-8 support for varchar data type provides substantial storage savings depending on the character set in use.
For eg, using an UTF-8 enabled collation, changing the column data type from nvarchar(20) to varchar(20) offers a
significant drop in storage requirements since nvarchar(20) requires 40 bytes for storage and varchar(20) needs 20.
=> Impact on string length of SQL varchar with CAST and CONVERT functions
SQL Server stores long string data in the commonly used varchar data type and it becomes helpful to know the
expected and maximum lengths of the strings to display the results in the UI.
=> MsSql takes 30 as the default length for SQL Varchar (with unspecified varchar length) in the SQL Server
when it is used with CAST and CONVERT functions.
In our case, even though the length of the string was 52, it returned 30 as the length as shown in the last result output.
=> One important point to note here is that when an unspecified length varchar field is created,
the default length of such field is 1 (shown in red color below).
When varchar length is unspecified and is used with CAST or CONVERT functions, the CAST or CONVERT returns
n=30 as the default string length of this conversion (marked in blue color below).
=> Insert 10,000 records, to check the data insertion time. Y
Employee_varchar_2000 insertion time 0.08 Seconds
Employee_varchar_4500 insertion time 0.19 Seconds
Employee_varchar_8000 insertion time 0.31 Seconds
Employee_varchar_Max insertion time 2.72 Seconds


Sqlite:
=> What is the maximum size of a VARCHAR ?
# SQLite does not enforce the length of a VARCHAR.
# You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there.
And it will keep all 500-million characters intact.
# Your content is never truncated.
# SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N.
=> TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
# If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column
has TEXT affinity.
# Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
=> Affinity Name Examples
# The following table shows how many common datatype names from more traditional SQL implementations are
converted into affinities by the five rules of the previous section.
# This table shows only a small subset of the datatype names that SQLite will accept.
# Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by
SQLite - SQLite does not impose any length restrictions
(other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.







=> Example Typenames
CREATE TABLE Statement or CAST Expression Resulting Affinity Rule Used To Determine Affinity
CHARACTER(20) TEXT 2
VARCHAR(255)
VARYING CHARACTER(255)
NCHAR(55)
NATIVE CHARACTER(70)
NVARCHAR(100)
TEXT
CLOB

[Union]

Bezüglich der VARCHAR Storage-Erfordernisse:

- Nahezu alle RDBMS speichern den Text + Längeninformation (2 Byte)
- Speicherung ist meistens effizienter bei Feldern die länger als 4 Zeichen sind
- Umgekehrt ist die Performance schlechter, da zusätzliche Umwandlungen stattfinden. Die Länge ist nicht durch DDL sondern Daten definiert
- Bei NVARCHAR gibt es das Problem, dass die Länge nicht der Zeichenanzahl entspricht und eine Berevhnung daher immer nur eine Näherung sein kann

Bezüglich "string truncation":
- Firedac besitzt FormatOptions [fvStrsTrim, fvStrsTrim2Len] "Strings auf Maximallänge kürzen" um gewollten Datenverlust zu unterstützen. Damit vermeidet man Copy beim Setzen der Parameter

Bezüglich BLOB
Für Text BLOB und deren Handling kocht jede DB ihr eigenes Süppchen (bzgl. Maximaler Länge, RegEx, CAST etc.). Dort würde ich ein generisches Dictionary selber aufbauen oder spezifisch mit SP arbeiten, die dann einen Cursor zurückgeben oder entsprechende Views aufbauen.

Allgemein kann man auch sagen, dass DB, die sehr gutes und performantes BLOB Handling anbieten, sowohl teuer als auch Speicherschweine sind.

Bezüglich SQL Abstraktion
Da gibt es viele Fallstricke, aber generell kann man mit FireDAC vieles abstrahieren. Vieles kann aber auch nicht richtig funktionieren, z.b. das Limit() Handling bei Subselects. Sehr schön sind auch die Macros (z.b. für Tabellennamen). Auch Stringzusammensetzungen sind sehr unangenehm.

Firebird SQL:

Code:

Kunden.Land || '-'  || Kunden.Plz || ' ' || Kunden.Ort as Importeur

FireDac SQL:

Code:

{Concat({Concat({Concat({Concat(Trim(Kunden.Land),''-'')}, Trim(Kunden.Plz))}, '' '')}, trim(Kunden.Ort))} as Importeur

[Bernhard Geyer]

- Texte mit variabler Länge ( die eventuell auch mal eine zu klein vorgegebene Länge sprengen könnten ).
ntext (oder nvarchar(max)). Damit schaffen alles DMBS bis 1-2GB

- Z.B.: Namen: VARCHAR(80) - Flummi, Brummi, Zummi //<= Wie groß ist der OnDisk-Speicher versch. DB ?
Wir haben 2022. Das interessiert keinen mehr, solange du jetzt nicht Billionen Datensätze hast.
(Aber i.d.R. speichern DBMS das optimiert ab.

- Z.B.: Namen: VARCHAR(80) - TextMitMehrAls80Zeichen => Könnte auch mal 90 Zeichen kommen, dann kracht es.
Ja

- Wieviel realer Speicher wird in den verschiedenen DB wirklich belegt ?
Wo? Festplatte (Datenmenge + Menge der Indeze die du benötigst

- Speichern von UniCode (UTF8) Texten muss möglich sein.
Gähn. Kann jede DB. Unicode-Problem ist eigentlich sowas von 90iger Jahre.

- Möglichst per Volltextsuche durchsuchbar, sollte einigermaßen effizient sein.
Gibts viele Möglichkeiten. Je Effizienter, desto mehr DB-Eigenheiten von SQL-Syntax bei Abfrage und Anlegen

- Möglichst indizierbar (geht das überhaupt bei TextBlobs, oder muss ich selbst von außen Hashes verwalten ) ?
textblobs können auch Indiziert werden. Da koste (AFAIK) jedes DBMS sein "eigens Süppchen". Also kein SQL-Standard

- Wie kann ich große TEXT Blobs effizient per SQL durchsuchen und in Queries einbinden ?
Abhängig DBMS, wenn effizient sein soll

- Macht es Sinn große TEXT Blobs in separate Tabellen, mit HASH Columns, auszulagern, oder machen die modernen DB sowas automatisch ?
Je nachdem. Alles ist möglich und viele DBMS können das konfiguierbar machen.

Wenn du aber viel mit (großen) Textdaten machst, dann schau dir mal Lucene oder den großen Bruder ElasticSearch an.
GB/TB große Textmengen können sehr schnell durchsucht werden.

[Rollo62]

Dankesehr für die Antworten.

Im Wesentlichen denke ich geht es mir um zwei Anwendungsfälle:

1. Variabler Texte, relativ überschaubarer Länge, welche aber mal das Limit reißen könnten
   ( z.B. Daten im Durchschnitt 40-80, gewählt VARCHAR(120), es könnten aber mal Daten 150 auftauchen.
2. Variabler Text, relativ großer Länge, der möglichst durchsuchbar bleiben sollte.
   ( z.B. Daten im Durchschnitt 1-2K, gewählt VARCHAR(4096), ist das effizient ?).

Zitat von Union:

Bezüglich der VARCHAR Storage-Erfordernisse:
- Nahezu alle RDBMS speichern den Text + Längeninformation (2 Byte)
- Speicherung ist meistens effizienter bei Feldern die länger als 4 Zeichen sind
- Umgekehrt ist die Performance schlechter, da zusätzliche Umwandlungen stattfinden. Die Länge ist nicht durch DDL sondern Daten definiert

Also wäre die Empfehlung bei VARCHAR zu bleiben, weil die DB's mit Sicherheit entsprechend der realen Daten,
und nicht der Kapazität anwachsen werden.

Zitat von Union:

Bezüglich SQL Abstraktion
Da gibt es viele Fallstricke, aber generell kann man mit FireDAC vieles abstrahieren.

Gibt es denn irgendwo schöne Tutorials dazu, die sich dem Abstrahieren annehmen ?
Im Orginal FD ist ja schon Vieles dabei, aber so richtig bin ich noch nicht drauf gekomen wie es am besten angewendet werden möchte.
Es gibt halt jede Menge Wahlmöglichkeiten, aber da habe ich die Qual.

Zitat von Bernhard Geyer:

- Texte mit variabler Länge ( die eventuell auch mal eine zu klein vorgegebene Länge sprengen könnten ).
ntext (oder nvarchar(max)). Damit schaffen alles DMBS bis 1-2GB

Siehe oben, 1.), 2.), also von den Extremen bin ich wohl weit genug entfernt.
Genau deswegen bin ich nicht so sicher ob man sich einfach auf die DB's verlassen sollte, oder versuchen sollte etwas zu optimieren.
Die Anwendung wird sicher nicht kritisch werden, aber das habe ich schon oft gedacht und es ist dann doch passiert

Zitat von Bernhard Geyer:

- Z.B.: Namen: VARCHAR(80) - Flummi, Brummi, Zummi //<= Wie groß ist der OnDisk-Speicher versch. DB ?
Wir haben 2022. Das interessiert keinen mehr, solange du jetzt nicht Billionen Datensätze hast.
(Aber i.d.R. speichern DBMS das optimiert ab.

Genau das wollte ich ja wissen ( sorry, bin auch erst kürzlich aus dem 20ten Jahrhundert hier in 2022 angekommen )
Mein letztes größeres DB-Projekt ist ein paar Jahre her, und ich habe nicht verfolgt was sich seitdem (10-15J) so getan hat.

Mal provokant gefragt, wenn die DB das alles wegoptimieren, warum dann nicht einfach immer VARCHAR( 4K ) oder so setzen,
auch wenn ich real nur ca. 80 Zeichen brauche und ich habe Ruhe und trotzdem ein genauso effizientes, speicherschonendes System ?

Zitat von Bernhard Geyer:

- Wieviel realer Speicher wird in den verschiedenen DB wirklich belegt ?
Wo? Festplatte (Datenmenge + Menge der Indeze die du benötigst

Ja Festplatte, Speicher, ich bin sicher es gibt gutmütige SQL Konfigurationen und welche die eine DB sprengen könnten.
Ich hoffe da für 1.) und 2.) stark auf das wegoptimieren, weil ich mich ja wohl nicht in Extrembereichen aufhalte.
Billiarden Datensätze habe ich auch nicht zu Erwarten.

Zitat von Bernhard Geyer:

- Speichern von UniCode (UTF8) Texten muss möglich sein.
Gähn. Kann jede DB. Unicode-Problem ist eigentlich sowas von 90iger Jahre.

Sorry, wollte es nur erwähnen, weil ich hier in der DP immer noch zig UniCode Threads sehe.
So ganz scheint das ja noch nicht verinnerlicht zu sein, ich erwarte da aber auch keinerlei Probleme seitens moderner DB und mit D11.

Zitat von Bernhard Geyer:

- Möglichst indizierbar (geht das überhaupt bei TextBlobs, oder muss ich selbst von außen Hashes verwalten ) ?
textblobs können auch Indiziert werden. Da koste (AFAIK) jedes DBMS sein "eigens Süppchen". Also kein SQL-Standard

- Wie kann ich große TEXT Blobs effizient per SQL durchsuchen und in Queries einbinden ?
Abhängig DBMS, wenn effizient sein soll

Schade, aber war ja zu Erwarten.
Was würde man denn als "große" textblobs ansehen, fiele Fall 2.) schon darunter, oder bleibt das bei VARCHAR( 4K ) ?
Denn darum geht es mir.

"Echte" TextBlobs, mit 100K oder so, da würde ich ein Durchsuchen auch nicht mehr Erwarten.

Zitat von Bernhard Geyer:

Wenn du aber viel mit (großen) Textdaten machst, dann schau dir mal Lucene oder den großen Bruder ElasticSearch an.
GB/TB große Textmengen können sehr schnell durchsucht werden.

Ja ElasticSearch ist auch mein heimlicher Favorit, allerdings muss ich wohl ohne auskommen.
Es wird auch kein MongoDB o.ä. möglich sein, wel ich auf einem virtuellen Server (ohne Docker) bleiben möchte.
Hochskalieren oder in die Cloud kann man damit ja später immer noch.

Ich habe aber gesehen dass es auch sowas wie MongoSqlite gibt, in dem eine Sqlite DB Mongo "simuliert".
Das ist sehr wahrscheinlich nicht besonders effizient, aber interessant ist es trotzdem.
Warum sollte eine hochoptimierte DB nicht auch sowas unterstützen ?

[jobo]

Die Fragen sind schwer zu beantworten, da alle DB das physikalische Speichern unterschiedlich handhaben. Und es auch unterschiedlich optimieren. Leider ist im Bereich Text BLOBS und Volltextsuche auch kein verbreiteter Standard nutzbar.

Für Postgres
kann man bei kleinen und großen Varchar Feldern sagen, dass es optimiert gespeichert wird und die Nutzung eines zu großen N in Varchar(N) keine Verschwendung ist. Ab einer bestimmten Größe von N werden Daten intern, dynamisch anders gespeichert, ausgelagert in eine Tabelle für große Werte. Dabei geschieht etwas „lustiges“, die Daten werden in dem Fall sogar transparent komprimiert. Sie nehmen dann u.U. noch weniger Platz ein, als rechnerisch zu erwarten wäre.
Es spielt aber keine Rolle, ob Du Varchar(255) oder Varchar(50) deklarierst. Die Zahl ist eigentlich nur als Constraint zu betrachten, also eine regelhafte Limitierung. Ist der gespeicherte Wert > 255 knallt es in beiden Fällen, Werte darunter, aber länger als 50 Zeichen führen nur bei Varchar(50) zu Fehlern.

Ein anderer Effekt bei der ganzen Sache ist die Kodierung. Heute ist es Standard, Multi Byte Encodings zu nutzen. Das Speichern eines Zeichens ergibt dadurch einen Byteverbrauch von 1-4 Byte bei variablen Encodings (auch Standard, es gibt auch Encodings mit fester Länge).

Damit kann sich in Postgres ein Verhalten ergeben, das für normalen Text ungefähr 2 Byte pro Zeichen im Schnitt belegt, für großen Text durch die Kompression nur noch 1 Byte. Das hängt im Einzelfall vom Encoding und der Komprimierbarkeit ab.

Für dieses Verhalten musst Du nichts tun, es geschieht per Default.

Gute Volltextsuche ist wie schon von anderen genannt ebenfalls eine sehr individuelle Sache, aber nicht nur intern, sondern auch im SQL, Formulierung und Umfang. Postgres ist hier sehr mächtig und kann durch verschiedene Indizierungsvarianten sehr effizient und treffsicher suchen.
Elastic Search ist dabei vermutlich noch besser, bedeutet aber auch, dass Du grob gesagt doppelte Datenhaltung bzw. viel Datentransfer hast. Will man Cloudspeicher sparen, ist das also nicht unbedingt eine gute Idee, es zusätzlich einzusetzen.

Besonders die Volltextsuche ist wie ebenfalls schon gesagt sehr individuell in SQL umgesetzt.
Wenn Du DB agnostisch arbeiten willst, kommst Du an der Stelle wahrscheinlich nicht weiter, sobald es um mehr als ein simples „<feldname> like ‘%<parameter>%‘ geht.

Dazu würde ich empfehlen, für spezielle Operationen Delphi Wrapper zu schreiben, die intern je nach DB andere SQL Operationen nutzen. Auf die Art kannst Du wirklich auch die Besonderheiten der jeweiligen Anbieter nutzen. Außerdem braucht m.E. jede DB ein eigenes DDL Script. Problematisch im Sinne der Einheitlichkeit ist ggF. noch die Vergabe/Handhabung von DB generierten PK. Das wird heute anscheinend aber von den DB Komponenten sehr flexibel und gut gehandhabt. Alternativ kann man Client oder Server generierte UUID als Key verwenden.

Angenommen der Text ist mit großer Sicherheit in einer einzigen Sprache, könnte hier durch Nutzung spezifischer single Byte Collations für gewünschte Spalten Speicherplatz im Faktor 2-3 gespart werden. (Das ist dann etwas „zurück in die Vergangenheit“, kann sich aber ggfs. Lohnen)

Weitere Varianten wurden bereits genannt. „Handarbeit“ mittels eigener Volltext Implementierung. Fraglich, ob sich das lohnt angesichts der Funktionen und Effizienz, die man geschenkt bekommt.

[Rollo62]

Zitat von jobo:

Für Postgres
kann man bei kleinen und großen Varchar Feldern sagen, dass es optimiert gespeichert wird und die Nutzung eines zu großen N in Varchar(N) keine Verschwendung ist.

Danke für den Postgres-Einblick.
Damit hatte ich auch schonmal zu tun, ist aber im Moment in der Priorität eher weiter hinten angesiedelt.
Gut zu Wissen dass auch dies keine Probleme damit hat.

Meine Strategie wäre dann schon fast klar, für alle "normalen" Texte 40-80 wie Namen/Bemerkungen, einfach VARCHAR( 255 ) zu nutzen,
und die DB optimieren lassen.

Schwieriger wäre die Entscheidung schon bei Adresse.
Da reichen hier locker 255 aus, aber nimmt man Adressen in China sieht das schon anders aus.
Muss ich mal checken ob 255 reicht, oder besser 512 genommen werden sollte, aber mehr wohl auf keinen Fall.

Ich habe ja immer noch die vage Hoffnung dass die DB an einer 255 Byte Grenze besser optimieren können als bei 512,
deshalb wäre es wohl nicht sinnvoll immer direkt auf 512 zu gehen.

Zitat von jobo:

Damit kann sich in Postgres ein Verhalten ergeben, das für normalen Text ungefähr 2 Byte pro Zeichen im Schnitt belegt, für großen Text durch die Kompression nur noch 1 Byte. Das hängt im Einzelfall vom Encoding und der Komprimierbarkeit ab.

Für dieses Verhalten musst Du nichts tun, es geschieht per Default.

Interessant, eine Unsicherheit weniger.

Zitat von jobo:

Besonders die Volltextsuche ist wie ebenfalls schon gesagt sehr individuell in SQL umgesetzt.
...
Dazu würde ich empfehlen, für spezielle Operationen Delphi Wrapper zu schreiben, die intern je nach DB andere SQL Operationen nutzen.
Auf die Art kannst Du wirklich auch die Besonderheiten der jeweiligen Anbieter nutzen. Außerdem braucht m.E. jede DB ein eigenes DDL Script.

Ja das möchte ich erstmal Vermeiden, und den "größten gemeinsamen Nenner" aller DB suchen.
Ich hoffe da auf FireDAC, da sind bestimmt noch zig Perlen versteckt.

Zitat von jobo:

Wenn Du DB agnostisch arbeiten willst, kommst Du an der Stelle wahrscheinlich nicht weiter, sobald es um mehr als ein simples „<feldname> like ‘%<parameter>%‘ geht.

Im Moment sollte "LIKE" reichen, um Suchbegriffe irgendwo im Text zu finden, aber es sollten schon mehrere "LIKE"
mit AND/OR in einer Abfrage möglich sein.
Das reicht meines Erachtens schonmal in 95% der Fälle aus, viel spezifischere Suchen mache ich ja auch in Google nur sehr selten.

Sowas wie "SoundEx" können ja meines Wissens auch schon viele DB von Haus aus, aber womöglich nicht Alle.

Ich möchte die Suche möglichst auf dem Server belassen, denkbar wäre natürlich auch eine grobe Vorselekion auf dem Server,
und eine "Feinanalyse" dann lokal.
Oder irgendwas mit StoredProcedures was das gleiche erreichen kann, aber das ist wieder sehr DB-spezifisch.

Zitat von jobo:

Alternativ kann man Client oder Server generierte UUID als Key verwenden.

Richtig, das war auch so ein Gedanke die Texte einfach in Zeilen o.ä. aufzuspalten, und dann separat "gehasht" abspeichern, um Redundanzen zu verringern und optimaler zu Suchen.
Dann kommt man aber sicher in die Nähe von ElasticSearch, und es wird entsprechend aufwendig auch im Standard-SQL.
Oder gibt es vielleicht fertige SQL/DDL "Schablonen", wie man sowas optimal anlegen sollte ?

Zitat von jobo:

Angenommen der Text ist mit großer Sicherheit in einer einzigen Sprache, könnte hier durch Nutzung spezifischer single Byte Collations für gewünschte Spalten Speicherplatz im Faktor 2-3 gespart werden. (Das ist dann etwas „zurück in die Vergangenheit“, kann sich aber ggfs. Lohnen)

Nein, gerade das soll international bleiben.
Ich werde aber wohl Texte und deren Übersetzungen in separaten Tabellen halten.
Den Aufwand und das Risiko mit unterschiedlichen Collations möchte ich mir nicht mehr antun, UniCode soll reichen.

Ich habe zwar keine Erfahrung im "Umkodieren" von Tabellen, aber ich denke man könnte notfalls später UTF8 in optimalere Collations konvertieren, über temporäre Tabellen, falls nötig.

Zitat von jobo:

Weitere Varianten wurden bereits genannt. „Handarbeit“ mittels eigener Volltext Implementierung. Fraglich, ob sich das lohnt angesichts der Funktionen und Effizienz, die man geschenkt bekommt.

Das Gute ist man kann ja Handarbeiten wenn es nötig ist.
Im Moment reicht es so aus, wenn alle modernen DB in gleicher Weise damit klarkommen.

[himitsu]

Du kannst die chinesischen Adressen ja in chinesisch speichern und nicht in lateinischer Umschrift.


Nja, eine andere "einfache" Entscheidung, ob CHAR, VARCHAR oder TEXT-Blob.
einzeilig (Edit = VARCHAR) oder mehrzeilig (Memo/RichEdit = TEXT)

Wenn es rein um Speicher geht, kannst für "interne" Texte (Codewörter oder so) auch CHAR/VARCHAR mit einer 1-Byte-Codepage speichern.
Ich glaub einige DBMS (mysql?) reservierten auch schonmal 5 Byte pro CHAR für UTF-8, also VARCHAR(1000) wäre dann schnell mal 5002 Byte klein.



Ich weiß garnicht, ob z.B. zum Speichern von sowas wie HASHs in Binär oder Text ein CHAR(64) viele Vorteile gegenüber VARCHAR(64) bringen würde,
aber hier würde als Hex-Text definitiv eine 1-Byte-Codepage ausreichen. Und Binär nochmal die Hälfte.
Auch der Index würde damit bestimmt kleiner/schneller werden.




Namen nach Chars zu begrenzen ist eh total ungerecht.
Chinesen können ganze Romane schreiben und ich bekomm oftmals nichtmal meinen kompletten Namen da rein.

[jobo]

Also, ich weiß nicht, ob Du Dir da nicht zu viele Gedanken machst.
Was ich geschrieben habe bedeutet inkl. Unicode ca 2 Byte pro Zeichen bei kürzeren Texten und sogar nur 1 Byte pro Zeichen bei längeren. (Durchschnittswerte für PG ohne Garantie)

Darüber hinaus gibt es genug andere Dinge, die Speicherplatz kosten. Overhead Daten für Tabellen Strukturen und Verwaltung.
Platz für Indizierung, Platz für Logfiles, ..

Bevor Du Elastic Search nachbaust, kommst Du wahrscheinlich lange ohne hin und Dein Projekt kommt auch eher in den Genuss, bereits benuttzbar zu sein. Optimieren, Speicherplatz sparen, .. kannst Du auch noch, wenn die Sache läuft.
Konvertieren von Daten ist ebenfalls möglich, temporär erfordert das jedoch mehr Speicherplatz "vor Ort".

Und irgendeinen Tod musst Du sterben. SQLiteMongoDb kling für mich nicht sexy, weil es 2 Dinge kombiniert, die "sehr speziell" sind. Vor allem klingt es sehr exotisch und von daher automatisch wenig erprobt. Dann in Gottes Namen MongoDB selbst (was Du da für VM Probleme siehst, habe ich nicht verstanden)

[jobo]

Zitat von himitsu:

Du kannst die chinesischen Adressen ja in chinesisch speichern und nicht in lateinischer Umschrift.

Das ist eine ziemlich coole Idee

Man könnte auch eine eigene Kodierung entwickeln, die nur wirklich benötigten Zeichen beinhaltet oder eine, die die Zeichen-Häufigkeiten innerhalb der eigenen Daten berücksichtigt und seltene Zeichen dann bei Bedarf mit MultiByte ablegt.

[himitsu]

Drei-Wort-Adressen?

Blöd nur dass alle coolen Wörter schon weg sind.