Tabelle optimieren???

[romber]

Hallo!

Seit einigen Tagen beschäftige ich mich mit dem Thema Datenbanken. Aus den ganzen Übungen und Tutorials ist eine Testtabelle erstanden, die ich nun weiter optimieren möchte. In diese Tabelle habe ich über 3,5 Mio. Datensätze aus einer Access-Datei importiert, die ich während meines Praktikums bei Schwacke zu den Testzwecken erhalten habe. Bei den Daten handelt es sich um Fahrzeugdaten, wie z.B. Marke, Modell, Version, Erstzulassung, Kilometerstand, Kraftstoffart usw.

Nun führe ich verschiedene Suchanfragen mit dieser Tabelle aus und die Dauern natürlich ewig lange, weil die Tabelle eben nicht optimiert ist. Und genau hier liegt mein Problem: ich habe bisher nicht viel Ahnung von Datenbanken und weiß nicht, wo ich mit der Optimierung anfangen soll. Anderseits möchte ich meine Testtabelle so optimieren, dass die Suchanfragen genauso schnell funktionieren wie z.B. auf den Seiten von vielen Gebrauchtwagenbörsen im Internet. Ich brauche jetzt Eure Expertenratschläge und würde sehr dankbar, wenn mir jemand ein wenig seiner wertvollen Zeit spenden und ausführlich erklären würde, wie ich in meinem konkreten Fall die Tabelle optimieren kann.

Also, ich habe eine Tabelle bestehend aus 40 Spalten. Der Primärschlüssel ist auf die Spalte "insert_id" gesetzt, die automatisch hochgezählt wird. Bisher gibt es keine Indizies. Von den 40 Spalten können nur bestimmte Spalten bei den Abfragen im WHERE-Teil definiert werden, wie z.B. Marke, Modell, Erstzulassung, Kilometerstand, Kraftstoffart, Getriebeart usw. Andere Spalten, die irrelevante Informationen wie z.B. Hubraum, Anzahl der Zylinder, Schadstoffklasse, Zahl der Sitze usw. werden nie in die Suche einbezogen.

Wie optimiert man so eine Tabelle für schnelle Suche?

[Sir Rufo]

Erstelle die Indizes

[DeddyH]

Zitat:

Zitat von romber (Beitrag 1055775)

Bisher gibt es keine Indizies.

Da hamwas doch schon. Setz doch einmal auf jedes Feld, das als Suchkriterium dienen kann/soll, einen Index. Das sollte bereits einen deutlichen Schub bringen.

[Bummi]

Der billigste Ansatz wäre Indizes auf die für Suchen relevanten Spalten zu legen.
Bei 3,5 Mio Datensätzen wäre auch zu überlegen ob Du nicht durch Normlisierung mehr Perfomance/Wartbarkeit hineinbekommst.

[Sir Rufo]

Da steht wie
http://msdn.microsoft.com/de-de/libr...3(SQL.90).aspx

Da steht was man noch beachten soll
http://www.databasejournal.com/featu...ation-Tips.htm

Kleiner Tip:
Führe eine Abfrage auf der Tabelle durch wo du nur in 1 Feld suchst
Dann für dieses Feld den Index erstellen lassen und die Abfrage nochmals laufen lassen und staunen

[romber]

Vielen Dank für die schnelle Reaktionen!

Über Indizies habe ich bereits einiges gelesen und erfahren, dass es in bestimmten Fällen Sinn macht, ein Index über mehrere Spalten zu setzen. Macht es auch in meinem Fall Sinn? Z.B. bei Marke und Modell oder Erszulassung-Monat und Erszulassung-Jahr? Ehrlich gesagt, würde ich gerne verstehen, wann es sinnvoll ist, eine Index auf mehrere Spalten zu setzen.

Dann habe ich auch irgendwo einen Hinweis gelesen, dass es nicht immer gut ist, viele Indexe in einer Tabelle zu haben. Z.B. wenn in die Tabelle oft geschrieben wird. Bei mir ist es nicht der Fall. Meistens wird es gelesen und nur selten inseriert. Kann ich diesen Hinweis in meinem Fall einfach ignorieren?

Zitat:

Zitat von Bummi (Beitrag 1055779)

Bei 3,5 Mio Datensätzen wäre auch zu überlegen ob Du nicht durch Normlisierung mehr Perfomance/Wartbarkeit hineinbekommst.

Wie normalisiert man eine Tabelle? Worum genau geht es bei der Normalisierung?

[Sir Rufo]

Performance mit Index, Multiindex etc. Steht doch alles hier

Zitat:

Zitat von Sir Rufo (Beitrag 1055780)

Da steht was man noch beachten soll
http://www.databasejournal.com/featu...ation-Tips.htm

[DeddyH]

Normalisierung (Wikipedia)

[romber]

Zitat:

Zitat von Sir Rufo (Beitrag 1055780)

Da steht wie
http://msdn.microsoft.com/de-de/libr...3(SQL.90).aspx

Da steht was man noch beachten soll
http://www.databasejournal.com/featu...ation-Tips.htm

Ich habe jetzt das ganze mehrmals durchgelesen und drotzdem nicht alles verstanden. So viele optionale Parameter beim setzen eines Indexes bringen mich einfach durchanander.
Kann mir jemand Schritt für Schritte erkläre, wie ich in meinem konkreten Fall vorgehen soll? Wo ein CLUSTERED INDEX kommt, wo ein Index auf merere Spalten kommt uws. So werde ich es besser verstehen. Danke!

[Sir Rufo]

Du fängst gerade mit dem Thema Index an, gut.

Um bewerten zu können, welche Indizes zu genau benötigst, muss man genau wissen, welche Abfragen wann und wie oft gemacht werden. Auch der Feldtyp ist wichtig für die Index-Entscheidung (Zahlen oder Text). Es gilt hier abzuwägen zwischen einem fetten Index (alle Felder mal rein) und einem leichten (ein Feld) ... usw.

Was möchte ich sagen:

Lege für ein Feld aus der Tabelle einen Index an und überprüfe mit einer Abfrage das Zeitverhalten
Das ist dazu um ein Gefühl dafür zu bekommen.
Den Index ganz einfach ohne großes Brimborium Cluster etc. erstellen.
Und dann weiter ran tasten.

Hast du das SQL Management Studio installiert, dann einfach auf die Tabelle klicken und Create Index

[romber]

Ok, vielen Dank!
Einige Indizies habe ich bereits gesetzt und sie wirken wirklich wunder. Ich probiere noch weiter und melde mich bestimmt noch mit weiteren Fragen!
Danke!

[romber]

Habe noch einige Anfänger-Fragen zu den Indizien.

Ich habe die Indizies gesetzt und die Abfragen laufen nun viel schneller. Trodzdem wenn ich nach langer Pause eine Anfrage ausführe, dauert es wieder sehr lange. Die weitere Anfragen gehen dann ganz schnell. Warum ist das so und was muss ich unternehmen, damit das nicht passiert?

Spielen die Anzahl der Spalten und die Detentypen eine Rolle bei der Geschwindigkeit einer Abfrage? Ich meine, ich habe in meiner Tabelle (nicht normalisiert) 40 Spalten, zwei davon sind vom Typ ntext. Bei meinen Abfragen werden aber höchstens 15 Spalten einbezogen, die alle vom Typ Int, SmallInt oder Tinyint sind. Und ich selectiere nie alle Felder, sondern wähle nur die Felder, die ich wirklich brauche. Soll ich die Tabelle schmaller machen, um bessere Performance zu erreichen?

Bei der Erstellung der Tabelle habe ich für manche Felder den Typ SmallInt gewählt, wobei wir ich es mir jetzt überlege auch ein TinyInt gereicht hätte. Nun habe ich auf diese Spalten Indizies gesetzt. Spielt es für Performace eine Rolle? Werden die Abfragen schneller, wenn in den Datentyp auf TinyInt ändere?

[Sir Rufo]

Ich vermute mal, du hast die MSSQL Express Edition, die ja meist auf Desktop-Rechner eingesetzt wird. Da wird nach einiger Zeit der Index-Cache geleert, sonst könntest du irgendwann der Rechner nicht mehr gebrauchen.
Entzieht sich jetzt aber meiner Kenntnis ob man dieses Verhalten in der EE beeinflussen kann.

Der echte MSSQL macht das imho nicht (nur wenn der RAM-Speicher nicht ausreicht). Darum sollte ein SQL-Server auch mit mind. soviel RAM ausgerüstet sein, dass alle Indizes im RAM Platz haben. Beim Start kann man auch beobachten, dass der SQL-Server die gesamte RAM-Kapazität (die er nehmen darf) an sich reißt.

Je kleiner die Daten im Index umso weniger Daten müsse ja von der Platte gelesen werden, somit ist es schon ein Unterschied, ob man eine Spalt mit 1 Byte / 4 Byte oder x Byte hat.
Messbar wird das natürlich erst ab einer bestimmten Anzahl an Datensätzen (könnte bei dir also schon relevant sein)

[DeddyH]

Zu den ntext-Feldern: ich weiß nicht, ob es sich bei MS SQL auch so verhält, aber in anderen DBMS (AFAIK Firebird z.B.) haben diese auch Einfluss auf die Performance. Hier wirkt es Wunder, diese in eine eigene Tabelle auszulagern und eine 1:1-Relation einzurichten. Vor einiger Zeit gab es mal einen Thread dazu (IIRC von alzaimar), Du kannst ja einmal danach suchen.

[shmia]

Zitat:

Zitat von DeddyH (Beitrag 1056313)

Zu den ntext-Feldern ... Einfluss auf die Performance

Nach meinen Messungen (Tabelle mit ~60 Felder; davon 3 text-Felder, 100000 Datensätze holen) verlangsamen text/ntext-Felder die Abfragen um 10 bis 20%.

[DeddyH]

Dann hab ich also keinen Quatsch erzählt, Danke für die Bestätigung :D

[Sir Rufo]

Das sagt aber leider nichts darüber aus, ob die reine Selektion länger braucht.
Erst dann würde es Sinn machen, diese Felder auszulagern.

Wenn die Selektion komplett über den Index laufen kann wird sich das Zeitverhalten nicht ändern.
Kann aber kein Index benutzt werden, dann wird die Tabelle in den Speicher geladen und dort Zeile für Zeile verglichen. Das dauert natürlich länger.

Die Übertragung der Daten an den Client dauert aber immer gleich lang, da die Datenmenge (zu übertragene Bytes) sich nicht ändert.

Das kann man übrigens sehr schön mit Navicat überprüfen, denn der kann auch Statistiken ausgeben, wie lange jeder einzelne Schritt auf dem SQL-Server gedauert hat.
Geht aber nicht für MSSQL

[romber]

Zitat:

Zitat von Sir Rufo (Beitrag 1056310)

Ich vermute mal, du hast die MSSQL Express Edition, die ja meist auf Desktop-Rechner eingesetzt wird. Da wird nach einiger Zeit der Index-Cache geleert, sonst könntest du irgendwann der Rechner nicht mehr gebrauchen.

Nein, ich benutze für die Tests den Testserver von der Firma, dort haben wir mehrere MS SQL Server 2008 Enterprise x64 und 12 GB Arbeitsspeicher. Woran kann das Problem mit der ersten langsamen Abfrage?

Zitat:

Zitat von Sir Rufo (Beitrag 1056310)

Der echte MSSQL macht das imho nicht (nur wenn der RAM-Speicher nicht ausreicht). Darum sollte ein SQL-Server auch mit mind. soviel RAM ausgerüstet sein, dass alle Indizes im RAM Platz haben. Beim Start kann man auch beobachten, dass der SQL-Server die gesamte RAM-Kapazität (die er nehmen darf) an sich reißt.

Genau das beobachte ich beim Start des Servers. Sogar wenn noch gar keine Datenbanken vorhanden waren, nahm sich der SQL Server innerhalb weniger Minuten ganze 96% des Arbeitspeichers. Kann das sein, dass jetzt, wenn ich eine Tabelle mit über 3.5 Mio. Datensätze mir mehrerer indizierten Spalten habe, den Arbeitsspeicher nicht ausreicht? Eigentlich habe ich nur auf SmallInt- und TinyInt-Spalten Indizies gesetzt, die sollten eingenrlich nicht viel Platz belegen, oder?

[shmia]

Zeig' doch mal das SQL-Script deiner Tabelle.
Dazu im Management Studio die Tabelle markieren und im Kontextmenu auswählen:
Script Table as -> CREATE TO -> New Query Editor Window
Das erzeugte Script kannst du dann hier posten.

[romber]

Hier ist meine Tabelle:

Code:

USE [SCHWACKE]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SCHWACKE_TDATA](
   [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [ic_datetime] [datetime] NULL,
   [p_preis] [int] NOT NULL,
   [f_typ] [smallint] NOT NULL,
   [f_zustand] [smallint] NOT NULL,
   [f_art] [smallint] NULL,
   [f_unfall] [bit] NOT NULL,
   [f_marke_modell_variante] [nvarchar](300) NULL,
   [f_marke_id] [smallint] NOT NULL,
   [f_marke] [nvarchar](50) NOT NULL,
   [f_modell_id] [smallint] NULL,
   [f_modell] [nvarchar](100) NULL,
   [f_variante] [nvarchar](100) NULL,
   [f_garantie] [bit] NULL,
   [f_km_stand] [int] NULL,
   [f_kategorie] [smallint] NULL,
   [f_ez_monat] [smallint] NULL,
   [f_ez_jahr] [smallint] NULL,
   [f_vorbesitzer] [smallint] NULL,
   [f_baujahr] [smallint] NULL,
   [f_leistung_kw] [smallint] NULL,
   [f_leistung_ps] [smallint] NULL,
   [f_getriebeart] [smallint] NULL,
   [f_kraftstoffart] [smallint] NULL,
   [f_schadstoffklasse] [smallint] NULL,
   [f_umweltplakette] [smallint] NULL,
   [f_farbe] [smallint] NULL,
   [f_hersteller_farbe] [nvarchar](100) NULL,
   [f_metallic] [bit] NOT NULL,
   [f_interieur_farbe] [nvarchar](100) NULL,
   [f_interieur_stoff] [smallint] NULL,
   [f_tueren] [smallint] NULL,
   [f_klimatisierung] [smallint] NULL,
   [f_hu_monat] [int] NULL,
   [f_hu_jahr] [int] NULL,
   [f_hsn] [nvarchar](7) NULL,
   [f_tsn] [nvarchar](7) NULL,
   [f_beschreibung] [ntext] NULL,
   [f_ausstattungen] [nvarchar](300) NULL,
   [f_xml] [ntext] NOT NULL,
 CONSTRAINT [PK_SCHWACKE_TDATA] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

In den WHERE-Teil werden nur folgende Felder einbezogen:

Code:

[p_preis] [int] NOT NULL,
[f_typ] [smallint] NOT NULL,
[f_zustand] [smallint] NOT NULL,
[f_art] [smallint] NULL,
[f_unfall] [bit] NOT NULL,
[f_marke_id] [smallint] NOT NULL,
[f_modell_id] [smallint] NULL,
[f_km_stand] [int] NULL,
[f_kategorie] [smallint] NULL,
[f_ez_monat] [smallint] NULL,
[f_ez_jahr] [smallint] NULL,
[f_leistung_kw] [smallint] NULL,
[f_getriebeart] [smallint] NULL,
[f_kraftstoffart] [smallint] NULL,
[f_farbe] [smallint] NULL,
[f_klimatisierung] [smallint] NULL

Für folgende Felder habe ich einzelne Indizies erstellt (nonclustered):

Code:

[p_preis] [int] NOT NULL,
[f_typ] [smallint] NOT NULL,
[f_art] [smallint] NULL,
[f_marke_id] [smallint] NOT NULL,
[f_modell_id] [smallint] NULL,
[f_km_stand] [int] NULL,
[f_kategorie] [smallint] NULL,
[f_leistung_kw] [smallint] NULL,
[f_getriebeart] [smallint] NULL,
[f_kraftstoffart] [smallint] NULL,
[f_farbe] [smallint] NULL,
[f_klimatisierung] [smallint] NULL

Den Feldern

Code:

[f_ez_monat] [smallint] NULL,
[f_ez_jahr] [smallint] NULL

habe ich einen gemeinsamen Index verpasst.

[shmia]

Also Indexe auf Bit-Feldern sind relativ witzlos, weil ein Index darauf im Schnitt nur 50% ausfiltern kann. (aber ich sehe gerade, du hast keinen Index auf ein Bit-Feld gesetzt)

Ich würde noch die Reihenfolge der Felder f_ez_monat und f_ez_jahr tauschen und den Index darauf löschen und neu erzeugen.
Dann kann man z.B. auch folgende Abfrage ausführen:

SQL-Code:

SELECT * FROM SCHWACKE_TDATA WHERE f_ez_jahr <= 1980

Weil das Jahr dann am Anfang des zusammengesetzten Index steht, würde der SQL-Server den Index benützen, auch wenn der Monat nicht in der Abfrage enthalten ist.

Ansonsten würde ich den Datentyp "nvarchar" nach "varchar" und "ntext" nach "text" ändern.
Die Datentypen, die mit "n" beginnen sind Unicodefähig und brauchen doppelt so viel Platz, wie die nicht Unicodefähigen Stringtypen.
In deinem Fall kannst du den Platzbedarf erheblich (geschätzte 35%) verringern.
Und ich glaube nicht, dass in den Schwackedaten Zeichen ausserhalb von ASCII verwendet werden. (Ausser die Chinesen werden zum weltgrössten Autoproduzenten und geben ihren Modellen chinesische Namen... ;-)

PS:
Achte darauf, dass alle Stringfelder vor dem Speichern in die Tabelle mit Trim() bearbeitet wurden;
du möchtest ja keine unnötigen Leerzeichen in Millionenstückzahl in der Datenbank haben.

[generic]

Warum die Indizes selbst anlegen, wenn das die Datenbank viel besser kann?!

Du zeichnest mit dem Profiler dein Programm auf.
Dann lässt du die Profilerdaten vom Queryanalyser checken.
Dieser gibt dann Empfehlungen und erzeugt sogar dein ein SQL-Script mit den empfohlenen Indizies und Statistiken.

[romber]

Vielen Dank für diese Tipps!

Nachdem ich alle ntext auf text geändert habe und die Reheinfolge für f_ez_monat und f_ez_jahr geändert habe, läufen die Anfrage nun so, wie ich mir vorgestellt habe.
Mit dem Profiler werde ich mich aber auch auseinandersetzen. Sehr interessant das alles.