| 

.NET C# Java Javascript Exception

5
Mich würde interessieren, ab welcher Datenmenge sich die Länge eines VARCHAR-Feldes performancetechnisch bemerkbar macht.

Angekommen ich habe 100.000 Datensätze mit jeweils einer Spalte (VARCHAR) in dem z.B. ein Benutzername gespeichert wird. Gehen wir davon aus, dass keiner der Einträge länger als 50 Zeichen lang sein darf.

Meine Frage: Ab welcher Datenmenge oder Abfragehäufigkeit macht es sich bemerkbar, ob der VARCHAR mit 50 oder 255 Zeichen angelegt ist? Hat jemand diesbezüglich bereits einmal Messungen durchgeführt?

Indicies und "Hilfsmittel" dieser Art sollen dabei zunächst unberücksichtigt bleiben.
Auch andere Messungen die in diese Richtung gehen würden mich interessieren ...
10.09.2009
nate
nate
200 ● 2 ● 6
Editiert 10.09.2009
nate
nate
200 ● 2 ● 6
Ich mache ggerade eine Testreihe mit MySQL auf einem Windows-Server 2003. Hoffe das gibt dir wenigstens einen Anhaltspunkt.
– MiW 10.09.2009
4 Antworten
3
Ohne das DBMS zu kennen, auf welches du dich beziehst, ist es schwierig eine fundierte Antwort geben zu können.

Unter MSSQL:
"Die Speichergröße entspricht der tatsächlichen Länge der eingegebenen Daten in Byte." (Zitat aus der SQK Server-Dokumentation). Dh. wenn du ein Varchar-Feld mit einer Länge von 4000 Zeichen hast. Aber nur 10 Zeichen speicherst, werden auch nur 10 Bytes in Anspruch genommen. Beim Char-Datentyp sieht das anderes aus. Von der Perfomence entspricht das einem Varchar(10) Feld. Der SQL-Server wird intern erstmal die Zeichenlänge vergleichen bevor er den eigendlichen Vergleich durchführt. Liegt ein Index auf der Spalte, wird er den Index bemühen was die Performence erheblich steigern kann. Performencbremsen sind Konstrukte die er nicht optimieren kann. z.B.: "where LTRIM(RTRIM([Spalte_varchar_4000])) = LTRIM(RTRIM('UserEingabe'))". Hier reicht der Längenvergleich nicht aus, ein Index kann nicht greifen und der SQL-Server muss jeden Datensatz anfassen statt in den internen Pagetabels eine Vorselektrierung zu machen.
Zu beachten ist auch noch: "char- oder varchar-Daten können ein Zeichen enthalten oder aus einer Zeichenfolge mit maximal 8.000 Zeichen bestehen" dir nur Nicht-Unicode-Zeichendaten enthalten. Für alles andere mus der Text-Datentyp verwendet werden.
Achtung: Eine Tabellenzeile in MSSQL kann nur 8KB Daten enthalten.
Zusammenfassung: Perfomance gut mit Statements die der Optimizer gut optimieren kann. Noch besser mir Indezies. Schlecht wenn beides nicht möglich ist.

UnterMySQL:
"Maximallänge einer VARCHAR-Spalte wird durch die maximale Datensatzgröße und den verwendeten Zeichensatz bestimmt. Die gesamte Maximallänge liegt bei 65.532 Byte." (Zitat MySQL-Dokumentation). Ergo ist es möglich bei MySQL mehr Daten zu speichern und auch UniCode-Zeichen oder andere Zeichensätze zu verwenden. Indezies stehen auch in MySQL zur Verfügung. Wie sich der Optimizer bei MySQL verhällt bzw. obs überhaupt einen gibt, kann ich leider nicht beantworten.

Unter SQLite:
Hier ist die maximallänge auf 500 Zeichen begrenzt und ich glaube das auch UniCode Zeichen verwendet werden könnten. Indizies gibt es hier nicht und die Performance ist hier abhäng davon, ob die Datenbanktabelle komplett in den Arbeitsspeicher passt oder nicht. Und auch dann ist die Perfomance noch abhänge von der Länge wobei es hier um Millisekunden geht.
10.09.2009
Floyd
Floyd
11,0k ● 3 ● 9
Editiert 10.09.2009
Floyd
Floyd
11,0k ● 3 ● 9
5
Hier das Ergebnis eines Tests unter MySQL 5.0.51a auf einem Window-Server 2003.

2 Tabellen mit einer auto_increment id (int)
und 1 feld varchar(50 bzw 255) gefüllt mit jeweils 50 mal "X".

10 Millionen Datensätze ergeben folgendes Bild.

Speicherverbrauch der Tabellen

"50er"-Tabelle:
Typ      Verbrauch
Daten    572,2 MiB
Index    98,0  MiB
Insgesamt  670,2  MiB
Zeilenlänge ø   60
Zeilengröße ø  70 Bytes

"255er"-Tabelle:
Typ    Verbrauch
Daten    572,2   MiB
Index   98,0  MiB
Insgesamt  670,2  MiB
Zeilenlänge ø   60
Zeilengröße ø  70 Bytes


-> Hier also schon mal keinerlei Unterschied.
Doch nun zur Performance.

Analyse der einzelnen Transaktionen*:
*ich habe natürlich zwischen den Transaktionen immer wieder den DB-Cache geleert.

Update:
 Alle  Einer
50: 149.3374 sek. 0.5129 sek.
255: 160.6706 sek. 0.5219 sek.

Select ohne index (where x like y%):
 Alle  Einer
50: 0.0012 sek. 4.8711 sek.
255: 0.0014 sek. 4.9485 sek.

Insert eines Datensatzes:
50: 0.0004 sek.
255: 0.0004 sek.

Delete:
 Alle  Einer
50: 0.1053 sek. 4.8205 sek.
255: 0.1042 sek. 4.9260 sek.


Wie wir also sehen ist die Performance bei Nutzung des 255er-Feldes ca. 1.8% schlechter, wenn man keine Indizes benutzt.

Ich habe den gleichen Test nochmals mit Indizes gemacht und keinerlei Perfformanceeinbußen festgestellt. Auch der Speicherbedarf für die Indizes weicht nicht signifikant voneinander ab.

FAZIT: für MySQL lässt sich sagen, daß es völlig Wurscht ist, ob man das Feld mit 50 oder 255 definiert, solange man Indizes nutzt.
Sollen auf der Tabelle sehr viele Transaktionen ohne Nutzung eines Index stattfinden, bietet es sich an das Varchar-Feld so klein wie möglich zu halten.
10.09.2009
MiW
MiW
1,0k ● 7
Vielen Dank für deine Mühe! Klasse!
– nate 10.09.2009
1
Hallo,

im MySQL 5.0 Certification Study Guide wurde allerdings gesagt das ein Varchar Feld mit einer länge von z.B. 5 auch 6 Zeichen aufnehmen kann oder soviel wie man möchte. Varchar hat demnach keine echte Beschränkung, daher kann man diese auch weglassen.

Bei Mysql und PGSql ist es aber, wie du schon gezeigt hast, völlig egal wie groß ein Varchar Feld definiert wird, die Performance leidet unmerklich darunter.
– Lord_Pinhead 18.09.2009
1
Unicode-Zeichen werden im SQL Server im Datentyp 
nchar
bzw. 
nvarchar
gespeichert. Will man also mehrsprachige Anwendungen programmieren, sind char und varchar also tabu (oder man muss sich halt selbst um Konversionen kümmern, das hat aber ziemlich viele, nicht unbedingt technische Nachteile). Die maximal zulässige Zeichenlänge halbiert sich gegenüber varchar auf 4000 Zeichen. Man muss für Unicode keine text-Felder verwenden.

Hat man Daten, die einen Fließtext beliebiger Länge darstellen, sollte man den BLOB-Datentyp 
[n]text
verwenden. BLOBs (Binary Large Objects) werden im SQL-Server in Überlauftabellen gespeichert, was sofort erkennen lässt: weniger effizient. Auf die ntext-Felder kann man allerdings Volltext-Indizes legen, die Abfragenotation ist etwas anders.

Ein ganz komischer Zwitter ist ab SQL Server 2005 der Typ 
[n]varchar(MAX)
Dieser Datentyp verhält sich wie ein (n)text, lässt sich aber wie ein normaler (n)varchar ansprechen. Soweit ich mich erinnere, gab es mal Beschwerden, dass in StoredProcedures kein [n]text als Parameter verwendet werden kann. Da dies mit [n]varchar(MAX) geht, schätze ich mal, dass dies die eigentliche Bestimmung ist.
11.09.2009
McZosch
McZosch
31 ● 1 ● 1
0
laut meinen informationen macht es keine unterschied ob die definition des varchars mit 2 oder 4000 zeichen länge definiert wurde, es hat identische performancecharacteristik. das die begrenzung überhaupt existiert hat historische gründe. zu beachten ist ausserdem das "höhere" unicode zeichen oft mehr als 1 zeichen platz in der db brauchen.
die begrenzung macht meisst nur sinn wenn man aus bestimmten gründen weiss, das bestimmte felder nur exakte längen haben (rechnungsnummern, quartalszahlen, postleitzahlen etc..)
10.09.2009
Andreas
Andreas
36 ● 1
kurz zur Anmerkung. Bei MSSQL-Server ist der varchar-Typ Standardmäßig "latin-1" dh. es können keine Unicode-Zeichen abgespeichert werden. Hierfür gibt es dann den NVarchar und NText Datentyp der Unicode-Zeichen speichern kann jedoch aber den dafür nötige Platz auch belegt.
– Floyd 10.02.2011

Stelle deine Sql-Frage jetzt!