![]() |
Datenbank: Advantage • Version: 9 • Zugriff über: SQL
Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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:
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)
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
Habt ihr irgendwelche Vorschläge dazu? mfg, mika |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Index anlegen auf die urzeit-Spalte?
|
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
- 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. |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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. |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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; |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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:
Das geht sicher schnellt, Du sparst die Übertragungzeit zum Client und kannst die Weiterverarbeitung mit SQL erledigen.
insert into [puffertabelle] (x,y,z)
select a,b,c from [haupttabelle] where [Date Expression] |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Zitat:
Die Datums bzw. Zeitübergabe solltest Du mit Parametern bewerkstelligen. Zitat:
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 |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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. |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Hallo,
bin zwar mit Advantage nicht so vertraut, würde es so angehen:
Code:
DA usw als Parameter (Firebird-Systax)
SELECT * FROM ZielLog WHERE zl_date between :DA AND :DE AND zl_time between :TA AND :TE ORDER BY zl_date, zl_time
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. |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
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. |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Die Datenbank kenne ich, wir benutzen die nur für wenige hundert Einträge... dageht´s schnell...
Es ist halt keine Oracle oder so... Kannst du StordProcedures einsetzten? Die sind immer schneller. Da kann man auch mit # schnell mal ne´Temporäre Tabelle erstellen... Ich würd erst nach dem Datum selektieren, alles in eine Temporäre Tabelle schieben und dann weiter mit der Uhrzeit suchen... Und wenn die Datenbank nicht vollig FIX ist : Indiezes können einen select auch langsamer machen... Da würd ich auch mal rum spielen... |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Hallo,
ich teste grade die verschiedenen Zugriffszeiten aus, dabei messe ich die ausführung des sqls ebenso wie die zeiten die ich brauche um die datensätze durchzugehen. Das mit dem Order By muss ich noch ausprobieren aber ich hätte die schon gerne geordnet vorliegen. Soweit wie es jetzt aussieht werde ich die Zeit Klausel im Where wegfallen lassen und das selber prüfen, es scheint recht performant zu sein... Und nur die Ergebnismenge sind 10.000 Datensätze... ^^ In der Tabelle liegen ein paar mehr vor (siehe Titel dieses Threads)... Aber ich glaube ich werde das so lassen das ich einfach die Daten eines Tages hole und die Zeiten dann in der Schleife prüfe...
Code:
Öffnen der ZielLog-Daten: 11,7297 ms.
SELECT * FROM ZielLog WHERE zl_date = '2011-10-01'
Anzahl ZielLog-Daten: 17280 Iterieren der ZielLog-Daten: 836,6813 ms. Davon sind 721 Datensätze in dem von mir gesuchten Zeitraum, die ich in meine Liste gespeichert hab. Sieht mir annehmbar aus bis jetzt.... ca 0,85 Sekunden für die entnahme der mir wichtigen Daten der Tabelle vom öffnen bis zum schliessen :) --edit: unterschied (2x gemessen) mit und ohne order by ^^: Mit Order By Öffnen der ZielLog-Daten: 2334,4217 ms. Öffnen der ZielLog-Daten: 2324,6134 ms.; Ohne Order By Öffnen der ZielLog-Daten: 13,0694 ms.; Öffnen der ZielLog-Daten: 2,8676 ms.; |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Zitat:
Wenn ich mich richtig erinnere steht in der Hilfe, daß .FieldbyName die schnellste Möglichkeit wäre an die Daten zu kommen. (Unterschiede hab ich allerdings noch nicht bemerkt) Gruß K-H |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
|
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Zitat:
Iterieren der ZielLog-Daten: 815,6767 ms.; mit fieldbyname: Iterieren der ZielLog-Daten: 890,5021 ms.; gruß, mika |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Alles klar, ich denke ich lasse das jetzt so. Danke an euch alle für eure Hilfe !
|
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Code:
Update auf 10.1 dürfte erst einmal einiges bringen. Dann fällt mir auf: Wenn Du nur ein Datum hast, warum dann ein BETWEEN?
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
Probiers mal so:
Code:
Übrigens: Indexe auf die einzelnen Felder bringen mehr als ein kombinierter, weil diese dann viel flexibler eingesetzt werden können und mit Sicherheit auch kleiner sind;)
SELECT * FROM ZielLog WHERE zl_date=:datum AND zl_time>:zeit1 AND zl_time<:zeit2 ORDER BY zl_time
|
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
Hallo Herr Dürr :)
Lange ists her... Ich habe den SQL Befehl ja inzwischen verändert auf SELECT zl_date, zl_time, zl_Transpondernr, zl_schlachnummer FROM ZielLog WHERE zl_date = between '2011-10-01' AND '2011-10-01' Von Bis Datum habe ich vorsichtshalber eingebaut falls ich mal Daten exportieren soll von dem Zeitraum 23:00 bis 01:00, da muss ich dann aber auch noch mal nachschauen falls sowas passiert. Mit der Bearbeitungszeit bin ich inzwischen sehr zufrieden, ich liege unter einer Sekunde was auch mein Ziel war. Danke für alle eure Antworten, ich widme mich dann mal dem nächsten Problem :) |
AW: Select Optimierung bei Tabelle mit 15.Mio Datensätzen
An K H:
Habe ich mal irgendwo gelesen, scheit auch logisch da jedes mal das Feld über den Namen gefunden werden muss. Hier eine alte Nachricht von alzaimar dazu: [QUOTE=alzaimar;443250] Zitat:
|
Alle Zeitangaben in WEZ +1. Es ist jetzt 00:11 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz