AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Select Optimierung bei Tabelle mit 15.Mio Datensätzen

Select Optimierung bei Tabelle mit 15.Mio Datensätzen

Ein Thema von mika · begonnen am 15. Sep 2011 · letzter Beitrag vom 15. Sep 2011
Antwort Antwort
Seite 1 von 2  1 2   
Benutzerbild von mika
mika

Registriert seit: 25. Okt 2002
176 Beiträge
 
Delphi 6 Professional
 
#1

Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 10:22
Datenbank: Advantage • Version: 9 • Zugriff über: SQL
Hallo Forum

ich möchte gerne ein Programm schreiben das einen Teilbereich einer Tabelle (nach Datum/Uhrzeit) in eine XML Datei exportiert. Diese Tabelle wird alle 5 Sekunden um einen Datensatz erweitert und hat bei unserem Kunden inzwischen ca 6 Millionen Datensätze. Da ich an diesem Umstand nichts ändern kann habe ich mir eine Testtabelle gebaut, 18 Millionen Datensätze hineingeschrieben, und probiere nun damit herum. Die Tabelle hat eine Struktur in folgender Form:

Datum | Uhrzeit | Int-Feld | Int-Feld | String-Feld | String-Feld | Int-Feld

(Indizes auf jedem einzelnen Feld + ein Index auf Datum+Uhrzeit vorhanden)


Nun möchte ich einen Teilbereich, z.B. alle Datensätze vom aktuellen Datum zwischen 5 und 18 Uhr ermitteln und diese dann in eine XML Datei exportieren. Das alles ist auch nicht das Problem, sondern das ich diese Tabelle nicht lange "stören" möchte Ich benötige aus dieser Tabelle das Datum, die Uhrzeit und ein Integer Feld, und werde damit aus 2-3 anderen Tabellen noch andere Daten dazunehmen.

Meine Idee war das ich folgenden SQL ausführe:
Code:
SELECT * FROM ZielLog WHERE zl_date between '2011-10-01' AND '2011-10-01' AND zl_time between '05:00:00' AND '06:00:00' ORDER BY zl_date, zl_time
dann die Datensätze durchgehe und mir meine Werte in eine Liste schreibe, damit ich die Tabelle nicht lange offenhalte und dann mit der Liste meine restlichen Werte hole. Allerdings dauert der SQL teilweise recht lange, gibt es da Optimierungsmöglichkeiten? Ich habe zum Beispiel gemerkt das das ausführen des SQLs wesentlich schneller geht wenn ich die Uhrzeit nicht mit einbeziehe, dafür hätte ich dann wesentlich mehr Datensätze die ich durchgehen muss... (Pro Tag kommen ca 10.000 Datensätze da rein zwischen 4 Uhr und 19 Uhr)

Habt ihr irgendwelche Vorschläge dazu?

mfg, mika
:: don't Panic ::
  Mit Zitat antworten Zitat
Benutzerbild von Bernhard Geyer
Bernhard Geyer
Online

Registriert seit: 13. Aug 2002
Ort: Oberreichenbach
16.158 Beiträge
 
Delphi 10.2 Tokyo Professional
 
#2

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 10:47
Index anlegen auf die urzeit-Spalte?
Windows Vista - Eine neue Erfahrung in Fehlern.
  Mit Zitat antworten Zitat
WladiD

Registriert seit: 27. Jan 2006
Ort: Celle
109 Beiträge
 
Delphi XE Professional
 
#3

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 10:53
- Selektiere nur das, was du auch wirklich brauchst und nicht mittels *
- Erstelle einen verschachtelten/mehrdimensionalen Index, der die Felder zl_date und zl_time enthält (genau in der Reihenfolge). Separate Indizies auf die beiden Spalten bringen für diese Abfrage wenig.
Waldemar Derr
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
2.224 Beiträge
 
Delphi 2010 Enterprise
 
#4

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 11:07
Ich würde das Order by weglassen, kannst Du später mit dem "Extrakt" machen.
Wenn die Zeit in einer separaten Spalte steht, würde ich nur auf den Tag einschränken (und nur diesen Indizieren). Das "Extrakt" später noch verfeinern.
Das sollte eigentlich flott gehen.
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von mika
mika

Registriert seit: 25. Okt 2002
176 Beiträge
 
Delphi 6 Professional
 
#5

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 11:16
Hallo und danke schonmal für die Antworten!

Zu dem Index: Wie oben geschrieben, es existieren bereits Indizes für jede einzelne Spalte und ein Index für Datum; Uhrzeit (also auch die richtige Reihenfolge)

Zu dem select *: Das hab ich mir auch schon gedacht, ergab aber im SQL Query Builder keinen Unterschied in der Ausführungszeit, weswegen ich das erstmal so gelassen hab.

Zu dem nur date in der where klausel: das hab ich auch schon probiert, die ausführungszeit des sqls mit datum; uhrzeit betrug ca 6 sekunden, die nur mit datum ca 0,6 sekunden... allerdings bekomme ich dann auch ca 10000 Datensätze statt evtl nur 800. Werden die nachfolgenden Operationen dann nicht die verkürzte Ausführungszeit wieder zunichte machen?

--edit
Hier mal meine methode bis jetzt....
Code:
function TdmSchlacht.GetSchlachtDaten(FromTime, ToTime: TDateTime): Boolean;
Type
  TSchlachtdaten = packed record
    Datum: TDate;
    Transponder,
    Schlachtnummer: String;
  end;
  PSchlachtdaten = ^TSchlachtdaten;

Var

  ndx: Integer;
  Schlachtdaten: TList;
  sDaten: PSchlachtdaten;

begin
  Schlachtdaten := TList.Create;

  sqlZielLog.Close;
  sqlZielLog.SQL.Clear;

  sqlZielLog.SQL.Add('SELECT');
  sqlZielLog.SQL.Add(' *');
  sqlZielLog.SQL.Add('FROM');
  sqlZielLog.SQL.Add(' ZielLog');
  sqlZielLog.SQL.Add('WHERE');
  sqlZielLog.SQL.Add(' zl_date between '''+FormatDateTime('YYYY-MM-DD', FromTime)+''' AND '''+FormatDateTime('YYYY-MM-DD', ToTime)+'''');
  sqlZielLog.SQL.Add(' AND zl_time between '''+FormatDateTime('HH:NN:SS', FromTime)+''' AND '''+FormatDateTime('HH:NN:SS', ToTime)+'''');
  sqlZielLog.SQL.Add('ORDER BY');
  sqlZielLog.SQL.Add(' zl_date, zl_time');

  TRY
    sqlZielLog.Open;
  EXCEPT
    Result := False;
  END;

  if sqlZielLog.Active then
  begin
    ndx := 0;
    sqlZielLog.last;
    while not sqlZielLog.Eof do
    begin
      Inc(ndx);
      New(sDaten);
      sDaten.Datum := sqlZielLog.FieldByName('zl_date').AsDateTime;
      sDaten.Transponder := sqlZielLog.FieldByName('zl_transpondernr').AsString;
      sDaten.Schlachtnummer := sqlZielLog.FieldByName('zl_schlachnummer').AsString;
      Schlachtdaten.Add(sDaten);
      sqlZielLog.Next;
      if (ndx mod 50) = 0 then
        Application.ProcessMessages;
    end;
    sqlZielLog.Close;
    Result := True;
  end
  else
    Result := False;

  If Result then
  begin
    // Liste durchgehen und additionale Daten ermitteln
  end;

  for ndx := 0 to Schlachtdaten.Count - 1 do
  begin
    sDaten := Schlachtdaten[ndx];
    Dispose(sDaten);
  end;

  Schlachtdaten.Free;
end;
:: don't Panic ::

Geändert von mika (15. Sep 2011 um 11:21 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
2.224 Beiträge
 
Delphi 2010 Enterprise
 
#6

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 12:01
Du baust eine Liste aus den Daten. Ich hatte gedacht, Du schiebst die in eine separate Tabelle.
Wenn der Mengenunterschied tatsächlich so groß ist, macht dieser Weg wohl nicht so viel Sinn.

Ich kenne Advantage nicht, vermutlich helfen aber die beiden separaten Indizes (oder auch die Kombination mit "between") nicht viel. Kannst Du Dir einen Optimizer Plan der Query ausgeben?
Wie schon gesagt wurde, ist ein kombinierter Index sicher besser. Aber bei so großen Tabellen wäre ich mit zusätzlichen Indices sparsam.

Würde es helfen, wenn die Daten in einer separaten Tabelle landen?
Also so ungefähr:
Code:
insert into [puffertabelle] (x,y,z)
select a,b,c from [haupttabelle] where [Date Expression]
Das geht sicher schnellt, Du sparst die Übertragungzeit zum Client und kannst die Weiterverarbeitung mit SQL erledigen.
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
5.957 Beiträge
 
Delphi 7 Personal
 
#7

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 12:04
Code:

  sqlZielLog.SQL.Add('SELECT');
  sqlZielLog.SQL.Add(' *');
  sqlZielLog.SQL.Add('FROM');
  sqlZielLog.SQL.Add(' ZielLog');
  sqlZielLog.SQL.Add('WHERE');
  sqlZielLog.SQL.Add(' zl_date between '''+FormatDateTime('YYYY-MM-DD', FromTime)+''' AND '''+FormatDateTime('YYYY-MM-DD', ToTime)+'''');
  sqlZielLog.SQL.Add(' AND zl_time between '''+FormatDateTime('HH:NN:SS', FromTime)+''' AND '''+FormatDateTime('HH:NN:SS', ToTime)+'''');
  sqlZielLog.SQL.Add('ORDER BY');
  sqlZielLog.SQL.Add(' zl_date, zl_time');
Hier würde ich an Deiner Stelle.SQL.Text:='select.......' benutzen, das ist meiner Meinung nach übersichtlicher.
Die Datums bzw. Zeitübergabe solltest Du mit Parametern bewerkstelligen.

Code:

  if sqlZielLog.Active then     {----- Warum? }
  begin
    ndx := 0;
    sqlZielLog.last;   {----- Warum? }
    while not sqlZielLog.Eof do
    begin
      Inc(ndx);
      New(sDaten);
      sDaten.Datum := sqlZielLog.FieldByName('zl_date').AsDateTime;
      sDaten.Transponder := sqlZielLog.FieldByName('zl_transpondernr').AsString;
      sDaten.Schlachtnummer := sqlZielLog.FieldByName('zl_schlachnummer').AsString;
      Schlachtdaten.Add(sDaten);
      sqlZielLog.Next;
      if (ndx mod 50) = 0 then
        Application.ProcessMessages;  {----- Warum? }
    end;
    sqlZielLog.Close;
Und wie vorher schon angemerkt, Du solltest nicht mehr Daten über die Leitung schicken als nötig!

Um "die Tabelle möglichst wenig zu stören?" gäbe es vllt. die Möglichkeit das Ergebnis der Datumsabfrage in eine temp. Tablelle auszulagern?

Gruß
K-H
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector
  Mit Zitat antworten Zitat
Benutzerbild von mika
mika

Registriert seit: 25. Okt 2002
176 Beiträge
 
Delphi 6 Professional
 
#8

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 12:09
Hallo

Das .Active ist dafür das falls der Open Befehl fehlschlägt, das soll schon so...
Das .last war wegen nem test, das ist falsch hier ^^
Das ProcessMessages ist auch nur solange ich teste dadrin

Das auslagern in eine temporäre Tabelle wäre möglich.
:: don't Panic ::
  Mit Zitat antworten Zitat
Neumann
Online

Registriert seit: 6. Feb 2006
Ort: Moers
378 Beiträge
 
Delphi 10.2 Tokyo Enterprise
 
#9

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 12:51
Hallo,

bin zwar mit Advantage nicht so vertraut, würde es so angehen:

Code:
SELECT * FROM ZielLog WHERE zl_date between :DA AND :DE AND zl_time between :TA AND :TE ORDER BY zl_date, zl_time
DA usw als Parameter (Firebird-Systax)

Ein DateTime-Feld anstatt Date und Time würde die Sache übrigens deutlich vereinfachen.

Weiter würde ich für die Query persistente Felder anlegen, ist schneller als FieldByName, was in der Schleife doch oft aufgerufen wird.

Dann habe ich noch die Erfahrung gemacht, dass bei Berechnungen, die Teile von großen Tabellen umfassen, es oft sehr viel schneller geht wenn man den Bereich in eine temporäre Tabelle kopiert und dann die Berechnungen mit der temp. Tabelle macht, wie schon vorgeschlagen.
Ralf
Gruß vom Niederrhein

Geändert von Neumann (15. Sep 2011 um 12:56 Uhr)
  Mit Zitat antworten Zitat
FredlFesl

Registriert seit: 19. Apr 2011
293 Beiträge
 
Delphi 2009 Enterprise
 
#10

AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen

  Alt 15. Sep 2011, 12:53
Du hast noch nicht geschrieben, das Du das 'ORDER BY' weggelassen hast.

Außerdem vermutest Du nur, dass der enorme Zeitgewinn beim weglassen der Zeit-Klausel im WHERE-Teil auf der Clientseite wieder kompensiert wird. Versucht hast Du es offensichtlich noch nicht. Ein TDataset ist zwar nicht sonderlich flott, aber über 10.000 Records in weniger als 6 Sekunde lokal zu iterieren, sollte flott sein.

Bringt es etwas, die beiden WHERE-Klauseln zu vertauschen? Einige RDBMS optimieren das, andere nicht.

Unterm Strich finde ich das Verhalten von dieser Datenbank aber sehr merkwürdig, bei lächterlichene 10.000 vs. 800 Records so lange zu brauchen, zumal ein Index verwendet wird.
Das Bild hängt schief.
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 2  1 2   

Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 10:22 Uhr.
Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2018 by Daniel R. Wolf