Tabelle optimieren???

[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 von DeddyH:

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

[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 von 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.

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 von Sir Rufo:

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.