Warum dauert das soo lange?

Hi Leute, ich mach dafür mal einen eig. thread auf. Der Folgende Code braucht bei mir nach dem 100.000 datensatz extrem lange. ca. 0.7sec pro datensatz. und für die erste select anfrage ca. 5sec.

Und zwar hab ich drei Tabellen:

tb_kunden
tb_kunden_processed
tb_kunden_stats

tb_kunden hat ca. 1 mio datensätze. Nun möchte ich statistiken für jeden einzelnen kunden in tb_kunden_stats speichern, und den Kunden der schon in stats ist in tb_kunden_processed vermerken als abgearbeitet.

-------------------------------------------------------
SCHLEIFE:

// selektiere 100 kunden die noch nicht in "tb_kunden_processed" vermerkt worden sind und arbeite diese ab
SELECT * FROM tb_kunden
LEFT JOIN tb_kunden_processed
ON tb_kunden.id=tb_kunden_processed.kunden_id
WHERE tb_kunden_processed.kunden_id IS NULL
LIMIT 0 OFFSET 0

// FÜR jeden Kunden dann stats erstellen
INSERT INTO tb_kunden_stats () VALUES ()

// kunden als bearbeitet markieren
INSERT INTO tb_kunden_processed () VALUES ()
----------------------------------------------------

Die Zeit geht definitiv bei den SQL Anfragen drauf. Ich hab bereits MySQL und PostgreSQL getestet. Bei MySQL war das Problem bereits nach 50.000 datensätzen da. PostgreSQL hat sich bis ca. 100.000 zeit gelassen. Evt. kann mir jemand einen Tipp geben, woran das liegt?

Schon die erste Abfrage (select * from ... Limit 100 OFFSET 100 000) dauert schon 1-3 sec kann das sein?

Rechner: Quadcore
4 GB RAM
schnelle festplatte

Gruß JayJay

Cpp_Junky

Ich tippe mal auf die Abfrage über tb_kunden_processed. Für jeden in tb_kunden zu prüfenden Datensatz muss die komplette Tabelle durchsucht werden. Natürlich wird das Problem immer grösser, je mehr Kunden du verarbeitest (1 Mio Sätze, wie du sagst). Nicht unbedingt eleganter, aber schneller wäre es, wenn du ein Zusätzliches Feld in tb_kunden einfügst, das gesetzt wird, sobald der Kunde verarbeitet ist. Das indizierst du dann ordentlich und fragst dann nur noch:

select * from tb_kunden where status is null

*john 0

Hast Du auf tb_kunden_processed.kunden_id einen Index erstellt?
Hast Du beobachtet wieviel RAM verbraucht wird?
Lies Dir die Doku zum Profiling der SQL Anfrage bei PostgreSQL durch. Und dann erstelle notwendige Indizes. Kandidaten dafür sind immer die Vergleichswerte in den Abfragen.

Verbraucht wird 1.5 GB RAM. Index wurd soweit auch erstellt.

Cpp_Junky schrieb:

Ich tippe mal auf die Abfrage über tb_kunden_processed. Für jeden in tb_kunden zu prüfenden Datensatz muss die komplette Tabelle durchsucht werden. Natürlich wird das Problem immer grösser, je mehr Kunden du verarbeitest (1 Mio Sätze, wie du sagst). Nicht unbedingt eleganter, aber schneller wäre es, wenn du ein Zusätzliches Feld in tb_kunden einfügst, das gesetzt wird, sobald der Kunde verarbeitet ist. Das indizierst du dann ordentlich und fragst dann nur noch:
select * from tb_kunden where status is null

Das scheint auch nicht viel besser zu laufen. Die Insert & Update Querys sind ziemlich zeitaufwendig, wenn mal über 50.000 - 100.000 einträge in der tabelle sind.

Wie können denn große Firmen mit einer Datenbank von 100 mio datensätzen arbeiten?

gruß jayjay

CarstenJ

Hallo,

das kann man so nicht sagen, aber eine der ersten Anlaufstellen ist der Ausführungsplan:
http://www.postgresql.org/docs/8.1/static/sql-explain.html

Danach gehts ans Optimieren. Ein Index kann, muss aber nicht zu einem schnelleren Ergebnis verhelfen. Ferner können Statistiken helfen. Sie liefern Informationen über einzelne Tabellen, u.U. auch über die Rechnerperformance (bei Oracle z.B.). Damit kann der Optimizier einen besseren Ausführungsplan erstellen.

http://www.postgresql.org/docs/8.1/static/planner-stats-details.html

Dann ist natürlich entscheidend, um was für eine Art von Datenbank es sich handelt. Eine OLTP DB sieht anders aus als eine OLAP DB.

Werd ich machen. Naja krass finde ich, ich müsse eine 1000 fache verbesserung haben, damit sich das lohnt

Wie ist das dann, ich darf ab sofort bei einer großen Datenbank gar keine JOINTS mehr verwenden?

CarstenJ

Hi,

also der ein oder andere Joint hat noch keinem geschadet.

Werd ich machen. Naja krass finde ich, ich müsse eine 1000 fache verbesserung haben, damit sich das lohnt

Den Satz verstehe ich nicht. Wieso lohnt sich das nur, bei einer 1000fachen Verbesserung?

Natürlich darf man Joins verwenden, egal wie groß die Datenbank ist. Man muss eben nur dafür sorgen, dass die Datenbank so gut wie möglich optimieren kann. Wenn du einen Index erstellst, musst du auch prüfen, oder der tatsächlich genutzt wird.

Post doch einfach mal deinen Ausführungsplan, vielleicht kann man da schon die ein oder andere Schwachstelle sehen.

Hi CarstenJ, steht bereits in meinem ersten Post, komplette Prozedur die ich durchführen will und das für ca. 5 mio Datensätze.

CarstenJ

Hallo JayJay,

das ist leider nicht der Ausführungsplan. Der Ausführungsplan wird vom Optimizer erstellt. Der parst dein SQL Statement, und versucht dann auf die für ihn beste Art und Weise die entsprechenden Tabellen abzufragen. Um festzustellen, wieso eine Abfrage so lange dauert, ist es unerlässlich, sich mit dem Optimizer und dem Ausführungsplan auseinanderzusetzen. Er ist quasi gleichzusetzen mit einem Profiler, der misst, welche Codestelle am meisten Zeit verbraucht.

Ein Index ist nicht per se die beste Lösung, und wenn doch, kommt es immer noch auf den Indextyp und/oder die Spalten an, für die der Index erstellt wurde.

Ob der überhaupt genutzt wird, siehst du auch im Ausführungsplan:

[b]explain[/b] SELECT * FROM 
  tb_kunden
LEFT JOIN 
  tb_kunden_processed
ON 
  tb_kunden.id=tb_kunden_processed.kunden_id
WHERE 
  tb_kunden_processed.kunden_id IS NULL
LIMIT 0 
OFFSET 0

Du wirst dann für jede Zeile eine Dauer sehen. So würde ohne Index die WHERE Klausel einen Full Table Scan der Tabelle tb_kunden_processed verursachen, als quasi die ganze Tabelle vom ersten bis zum letzten Datensatz durchlaufen, was lange dauert. Das kann manchmal richtig und gewünscht sein, aber in deinem Fall würde ein Index helfen.

Da du schreibst, dass du bereits einen Index erstellt hast, wäre nun interessant...
- Wie dieser aussieht
- Ob er überhaupt genutzt wird

Alles klar, danke. Ich werd mal das ganze ausprobieren und hier das Ergebnis posten. Vorweg nochmal danke für deine (euere) Hilfe