array of records mit Daten aus SQL Query füllen

[Gushiken]

In einem aktuellen Programm von mir muss ich Datensätze aus einer Datenbank auslesen und in einem array of record speichern. Das funktioniert soweit auch schon ganz gut.

Sobald ich aber versuche ca. 100000 Datensätze einzulesen dauert das ganze, je nach Laune des Computers und laufenden Programmen, zwischen 4 und 8 Minuten!!!

Da das ganze beim Programmstart ausgeführt werden soll ist das untragbar, es sollte maximal 10-20 Sekunden dauern....
Hier einmal ein kleiner Beispielcode (Das Record ist eigentlich wesentlich umfangreicher):

Delphi-Quellcode:

// Record und Array of Record
TPerson = packed record
  nachname:string[50];
  end;
TPersonArray = array of TPerson;

// Funktion
with query do
begin
  with sql do
  begin
    clear;
    add('<SELECT Anweisung>');
  end;
  open;
  first;
  len:=RecordCount;
  setLength(personenArray,len);
  len:=0;
  while len<RecordCount do
  begin
    with personenArray[len] do
    begin
      nachname:=Fields.Fields[0].AsString;
    end;
    next;
    len:= len+1;
  end;
end;

Vielleicht habt ihr ja eine Idee wie man das ganze schneller machen kann.

Gruß, Gushiken

[DeddyH]

Ist es wirklich notwendig, beim Programmstart 100000 Datensätze einzulesen? An Deinem Code sehe ich nicht viele Optimierungsmöglichkeiten (vielleicht mal das "packed" weglassen).

[dataspider]

Vielleicht bringt Unidirectional := True noch etwas.

Frank

[nahpets]

Hallo,

je nach Datenbank kann ein len:=RecordCount lange dauern, da zuweilen die Datensätze wirklich gezählt werden, d.h. die Datenmenge wird einmal durchlaufen (egal ob auf dem Client oder dem Server). Einige Datenbanken liefern die ersten Sätze der Ergebnismenge ja schon aus, bevor das gesamte Ergebnis der Abfrage feststeht, d. h.: Du liest die ersten Daten schon, während die Datenbank noch mit dem Ermitteln der Ergebnismenge beschäftigt ist. Möchtest Du nun wissen, mit wievielen Sätzen Du zu rechnen hast, muss die Datenbank die Ergebnismenge vollständig "zusammen" haben.
Ob's bei Dir so ist, kann ich nicht sagen.

Versuche mal vorher ein Select Count(*) entsprechend Deinem Select zu machen und nehme für len diesen Wert, statt RecordCount.

Der Record besteht sicherlich nicht nur aus nachname. Wieviel Byte an Daten hat ein Record, wieviel gibt das mal 100000? Reicht der Arbeitsspeicher oder muss ausgelagert werden. Dann wird es immer deutlich langsamer. Wenn's wirklich an der Laune des Computers und der laufenden Programme liegt, dann sorg' mal für mehr Arbeitsspeicher. Schau Dir im Taskmanager mal die "Speicherentwicklung" beim Programmstart an und eventuell auch mal die Grafiken über CPU-Belastung und Speichernutzung.

Warum so?

Delphi-Quellcode:

  len:=0; 
  while len<RecordCount do 
  begin 
    with personenArray[len] do 
    begin 
      nachname:=Fields.Fields[0].AsString; 
    end; 
    next; 
    len:= len+1; 
  end;

Wäre eventuell auch

Delphi-Quellcode:

  first; 
  len:=0; 
  while not EoF do 
  begin 
    with personenArray[len] do 
    begin 
      nachname:=Fields.Fields[0].AsString; 
    end; 
    next; 
    len:= len+1; 
  end;

möglich?
Ich weiß nicht, wie RecordCount intern funktioniert, ob es wirklich nur ein Wert ist, der einmalig zugewiesen wird oder eine Funktion im Getter, die bei jedem Zugriff die Anzahl der Datensätze ermittelt. Eine Alternative könnte also sein:

Delphi-Quellcode:

// Funktion 
with query do 
begin 
  with sql do 
  begin 
    clear; 
    add('<SELECT Anweisung>'); 
  end; 
  open; 
  // first; // bei open sollte man schon auf dem ersten Satz stehen.
  satzzahl := RecordCount; 
  setLength(personenArray,satzzahl); 
  len:=0; 
  while len < satzzahl do 
  begin 
    with personenArray[len] do 
    begin 
      nachname:=Fields.Fields[0].AsString; 
    end; 
    next; 
    Inc(len); 
  end; 
end;

[Gushiken]

Danke schonmal für die schnellen Antworten!!

Zitat:

Der Record besteht sicherlich nicht nur aus nachname. Wieviel Byte an Daten hat ein Record, wieviel gibt das mal 100000? Reicht der Arbeitsspeicher oder muss ausgelagert werden. Dann wird es immer deutlich langsamer. Wenn's wirklich an der Laune des Computers und der laufenden Programme liegt, dann sorg' mal für mehr Arbeitsspeicher. Schau Dir im Taskmanager mal die "Speicherentwicklung" beim Programmstart an und eventuell auch mal die Grafiken über CPU-Belastung und Speichernutzung.

Zur Speicherauslastung kann ich folgendes sagen:
- Beim Programmstart liegt die Auslastung bei ca 4.000K
- Nach der Abfrage von den 100.000 Datensätzen geht die Auslastung hoch auf ca. 40.000K
- Nach erfolgreichem erstellen des Arrays liegt das ganze bei ca 45.000K
- Nach befüllen eines Grids mit den Daten geht das ganze hoch auf 128.000K

Selbst nachdem das Grid befüllt ist sollte das ganze für meinen PC, der relativ lahm ist und 1GB RAM hat, kein Problem darstellen.

Die Zeit die RecordCount benötigt müsste ich mal testen... Das könnte sicherlich eine Bremse sein wenn der Wert jedesmal neu erzeugt wird, was ich mir aber eigentlich nicht vorstellen kann. Aber ich werde das mal testen und das Ergebnis posten.

Die Variante mit EOF hatte ich bereits einmal implementiert, das ganze aber verworfen.. aber mir fällt der Grund gerade nicht ein... Aber es war nicht wirklich performanter.

Die 100.000 Datensätze einzulesen ist nötig um bei einer Suche nach einer Person nicht jedes mal wieder eine SQL Abfrage starten zu müssen. Die Suche im Array geht wesentlich schneller als die Suche in der DB per SQL. (Eine Suche nach 3 Buchstaben innerhalb eines Namens dauert bei 100.000 Datensätzen im Array mit Ausgabe im Grid ca 50ms)

[Hansa]

Ist das Grid beim Befüllen sichtbar ? Falls ja, dann würde mich ein Faktor von 10-100 niccht wundern. :zwinker:

[DeddyH]

Grid? Womöglich TStringGrid? Dann solltest Du mal ein Beginupdate und ein Endupdate einfügen, da hier vermutlich die Bremse liegt, wie Hansa so treffend meint.

[gsh]

Zitat:

Zitat von Gushiken

Die 100.000 Datensätze einzulesen ist nötig um bei einer Suche nach einer Person nicht jedes mal wieder eine SQL Abfrage starten zu müssen. Die Suche im Array geht wesentlich schneller als die Suche in der DB per SQL. (Eine Suche nach 3 Buchstaben innerhalb eines Namens dauert bei 100.000 Datensätzen im Array mit Ausgabe im Grid ca 50ms)

hmm imho ist eine datenbank eigentlich für genau solche abfragen da.
Außerdem glaube ich (Bei einem richtigen SQL Server) ist die Suche in der DB auf jedenfall schneller.
Also bei meiner MySQL Datenbank brauch ich für eine Vergleichbare Abfrage circa 3 ms.

[Gushiken]

Das Grid bremst das ganze zwar schon deutlich, etwas über eine Minute, aber auch ohne die Visualisierung im Grid bemötige ich noch durchschnittlich 3-4 Minuten.
Das Grid wird im übrigen auch später nicht mehr benötigt. Es geht hauptsächlich um die Erstellung des Arrays.

Zitat:

Zitat von gsh

Zitat:

Zitat von Gushiken

Die 100.000 Datensätze einzulesen ist nötig um bei einer Suche nach einer Person nicht jedes mal wieder eine SQL Abfrage starten zu müssen. Die Suche im Array geht wesentlich schneller als die Suche in der DB per SQL. (Eine Suche nach 3 Buchstaben innerhalb eines Namens dauert bei 100.000 Datensätzen im Array mit Ausgabe im Grid ca 50ms)

hmm imho ist eine datenbank eigentlich für genau solche abfragen da.
Außerdem glaube ich (Bei einem richtigen SQL Server) ist die Suche in der DB auf jedenfall schneller.
Also bei meiner MySQL Datenbank brauch ich für eine Vergleichbare Abfrage circa 3 ms.

Die Abfrage an sich ist schneller. Allerdings muss man die Daten erstmal zur Datenbank hinbekommen, zurückholen und verarbeiten. Ich habs zwar selbst nicht getestet, aber meinem Ausbilder (ja, ich bin noch ein FIT in der Ausbildung *schäm*) meinte das eine Personensuche bei einer Tabelle mit ca 100.000 Datensätzen schnell 20s dauert, inkl. Verarbeitung und Anzeige im Grid.
Zudem verwenden wir einen MSSQL Server der, soweit ich weiß, ne ganze Ecke langsamer ist als MySQL.

Aber wie groß war die Datenbank denn wenn die Abfrage bloß 3ms dauert?? Und hattest du LEFT JOIN's in der Abfrage? Hier mal die 'echte' SQL Abfrage:

Delphi-Quellcode:

query.sql.add('SELECT '+
        ' p.name,'+
        ' p.vorname,'+
        ' p.id,'+
        ' s.bezeichnung as strasse,'+
        ' p.hausnr,'+
        ' o.bezeichnung as ort,'+
        ' o.plz as plz,'+
        ' day(p.geburtsdatum) as tag,'+
        ' month(p.geburtsdatum) as monat,'+
        ' year(p.geburtsdatum) as jahr,'+
        ' p.debitoren_nr as personenktonr'+
        ' FROM personen as p'+
        ' LEFT JOIN strassen as s ON s.id = p.strassen_id'+
        ' LEFT JOIN orte as o ON o.id = p.orte_id'+
        ' WHERE p.sys_delete = '+QuotedStr('F')+
        ' ORDER BY name');

[Jelly]

Ich komm nochmals auf die Ausgangsfrage zurück: Ist es wirklich notwendig 100000 Datensätze lokal auf den Client zu laden. Was machst du damit? Kein Benutzer wird 100000 Datensätze in einem Grid durchscrollen, ohne nach einer Suchfunktion zu schreien.

[Relicted]

ich schließe mich mal meinen vorrednern an zum thema "das ist die aufgabe der DB".

Wenn du den krempel wirklich lokal machen "möchtest" solltest du überlegen das initiale befüllen der records in einen thread auszulagern und im hintergrund zu machen. bis der job fertig ist muss der nutzer mit der "langsamen" datenbank vorlieb nehmen.


hier noch ne info für die anderen:

Zitat:

Zitat von Gushiken, anderes Forum

Die Daten benötige ich zum Programmstart, zudem soll alle 10-15min ein update der Daten gemacht werden.

gruß
reli

[nahpets]

Hallo,

bei 'nem Index für jede der Spalten

Code:

strassen.id
orte.id
personen.strassen_id
personen.orte_id
personen.sys_delete

sollte das auch mit 'nem MSSQL-Server ratzfatz gehen.
Wenn's Stringgrid nur zum Lesen und nicht für die Anzeige gebraucht wird, dann ist doch sicherlich Visible := false?

Und wenn schon die Daten im Arbeitsspeicher, was macht ihr den bei Änderungen an den Daten, jedesmal Programm neu starten und warten. Bevor Du irgend welche Umwege machst, um Geschwindigkeitsprobleme der Datenbank zu umgehen, sorg' erst mal dafür, dass Du die Fähigkeit der Datenbank ausreizt. Wenn's dann noch unerträglich sein sollte, dann überleg' Dir Alternativen.

MSSQL ist sicherlich nicht berühmt für Geschwindigkeit, aber auf einem halbwegs zeitgemäßen Server ist's für die Abfrage von Personendaten durchaus schnell genug. Wie oft suchen die Anwender denn überhaupt? Permanent oder 3 Stück die Stunde, da kann dann eine Wartezeit von 'ner Sekunde durchaus ok sein. Und wenn's mal 2 oder 3 sind, schimpft auch noch kein Anwender. Aber ob's nun 3ms oder 50ms oder 500ms sind, das merkt doch niemand. Und lohnt sich dafür der Aufwand, beim Programmstart alles in den Speicher zu laden und dabei möglichst viel Zeit zu sparen?

Wenn's Dir drum geht, zu lernen, wie man sowas programiert, okay, aber für den dauerhaften Arbeitseinsatz, überleg Dir das noch mal.

[Bernhard Geyer]

Zitat:

Zitat von Gushiken

.. meinte das eine Personensuche bei einer Tabelle mit ca 100.000 Datensätzen schnell 20s dauert, inkl. Verarbeitung und Anzeige im Grid.

Komisch das ich Wildcardsuchen auf ca. 1 Mio. Datensätze in weniger Zeit hinbekomme. Zeiten bei DB-Servern hängen von Faktoren wie Speicher/HD-Ausbau, ... ab. Aber über 100 k Datensätze lacht so ziemlich jede Datenbank.

Zitat:

Zitat von Gushiken

Zudem verwenden wir einen MSSQL Server der, soweit ich weiß, ne ganze Ecke langsamer ist als MySQL.

Quatsch. Ich habe hier ähnliche Datenmengen einmal auf MS SQL Server (2005) und einmal MySQL (5.0.x), jeweils 64-Bit auf der gleichen Maschiene laufen und spielen bei unseren Abfragen in einer ähnlichen Liga. Nur unser Oracle (32-Bit) fällt ewas ab.

Zitat:

Zitat von Gushiken

Aber wie groß war die Datenbank denn wenn die Abfrage bloß 3ms dauert?? Und hattest du LEFT JOIN's in der Abfrage? Hier mal die 'echte' SQL Abfrage:

Existiert auf p.sys_delete ein Index? Falls nein machst du einen Full-Table-Scan welcher längert dauert als wenn ein Index verwendet werden kann.

[Gushiken]

Hm... okay.. das mit dem Index und so müsste man mal testen.
Im Moment braucht der Datenbankserver für das auslesen der Daten für das Array ca 6 Sekunden.
Ein SELECT * braucht 19 Sekuden.

Da man an der Performance beim erstellen des Arrays anscheinend nicht mehr viel machen kann werden wir wohl unsere Datenbankstruktur etwas überarbeiten müssen. Das wird ein Spaß.. -.-

Nunja, trotzdem DANKE für die extrem schnellen und auch guten Antworten, ich war ehrlich gesagt doch etwas überrascht!!

:dp:

[Hansa]

Erkläre mir mal den Zusammenhang zwischen dem hier :

Zitat:

Zitat von Gushiken

Das Grid bremst das ganze zwar schon deutlich, etwas über eine Minute, aber auch ohne die Visualisierung im Grid bemötige ich noch durchschnittlich 3-4 Minuten...

und dem :

Zitat:

Zitat von Gushiken

Im Moment braucht der Datenbankserver für das auslesen der Daten für das Array ca 6 Sekunden.

Wo liegt jetzt der Haken ? Das sieht doch ziemich offensichtlich aus. :shock: Verwunderlich ist dann noch, dass keiner auf TStrings bzw. TStringList verweist. Das ist zum Zusammensuchen von Daten jedenfalls besser geeignet als die visuellen Dinger.

[DeddyH]

Naja, mit TStrings wird es aber schwierig, eine Matrix abzubilden.

[Hansa]

Array of Record schreit ja auch geradezu nach einer TObjectList. Aber meine Zwischenfrage ist damit noch nicht beantwortet.