Große Datenmengen "vergleichen"

Hallo

Eine Frage ob es dafür eine Verbesserung gibt, da manchmal die
Auswertungen bis zu 60 sec.
Dauern, was für eine Webseite nicht vertbar ist.

Gegeben:
Eine Tabelle (T1) (1 Mio DS) mit Gebietsangaben bundesland_id, ....
ort_id, ... geo
Eine Tabelle (T2) mit den Inhalten für die ganzen ID's
Dann gibt es noch eine Entfernungstabelle (T3) mit knapp 100 Mio DS.

Aufgabe: gesucht wird z.B. hammburg (bewusst mit 2 m)

suche nun die relevantesten Werte in T2 und schreibe diese in eine TMP-
Tabelle
weise nun diesem Ergebnis anhand der Position (bundesland, ort etc.)
einen Multiplikator zu
und "addiere" die Werte auf.

Bis hier her geht es meist unter einer Sec.

Aber hier hakt es:
Hole nun max. 1000 Einträge aus T3 für die TOP Werte aus TMP und füge
diese
TMP zur "späteren Verwendung" hinzu.

insert into TMP (id,wert)
select nach,wert*1/entfernung
from TOP inner join TMP on von=3Did
where TMP.wert > (90% vom Max)

In TMP können zu diesem Zeitpunkt auch mal 50 Einträge betroffen sein.
Indexe sind natürlich gesetzt.

Lösungsansätze:
T3 auf 10 Tabellen aufsplitten (erste stelle der ID) und je nach
enthaltenen ID's die Schleife durschlaufen.

oder anstelle von insert into ... select
dies für jeden einelnen DS in TMP durchführen

oder ?

Danke fürs Kopfzerbrechen

Gruß n.Olivier

Re: Große Datenmengen "vergleichen"

n.Olivier schrieb:
> Hallo
>
> Eine Frage ob es dafür eine Verbesserung gibt, da manchmal die
> Auswertungen bis zu 60 sec.
> Dauern, was für eine Webseite nicht vertbar ist.
>

Mag sein. Ich weiß aber ohnehin nicht, was vertbar ist.

> Gegeben:
> Eine Tabelle (T1) (1 Mio DS) mit Gebietsangaben bundesland_id, ....
> ort_id, ... geo

Ist SHOW CREATE TABLE in Deiner Installation defekt, oder warum
enthältst Du uns das vor? Wie soll man anhand dieser Beschreibung sagen
können, was "geo" z.B. ist?

> Eine Tabelle (T2) mit den Inhalten für die ganzen ID's

Was sind denn "Inhalte für die ganzen IDs"?

> Dann gibt es noch eine Entfernungstabelle (T3) mit knapp 100 Mio DS.
>
> Aufgabe: gesucht wird z.B. hammburg (bewusst mit 2 m)
>

Lösung: Atlas, Generalkarte.

> suche nun die relevantesten Werte in T2 und schreibe diese in eine TMP-

Was ist denn bitte der "relevanteste Wert" ?

> Tabelle
> weise nun diesem Ergebnis anhand der Position (bundesland, ort etc.)
> einen Multiplikator zu
> und "addiere" die Werte auf.
>

Wie wäre es, wenn Du einfach Deinen SQL-Code posten würdest statt
unverständlicher Prosa?

> Bis hier her geht es meist unter einer Sec.
>
> Aber hier hakt es:
> Hole nun max. 1000 Einträge aus T3 für die TOP Werte aus TMP und füge
> diese
> TMP zur "späteren Verwendung" hinzu.
>
> insert into TMP (id,wert)
> select nach,wert*1/entfernung
> from TOP inner join TMP on von=id
> where TMP.wert > (90% vom Max)
>

Geht doch. Warum Du allerdings Dinge wie wert*1/entfernung schreibst ...
Wo steht da was von 1000 Einträgen? Wo steht was von T3? Was soll TOP
sein? Mon dieu.


> In TMP können zu diesem Zeitpunkt auch mal 50 Einträge betroffen sein.

Von was können die "betroffen sein"?

> Indexe sind natürlich gesetzt.
>
Aha. Und welche? Und was sagt explain insert?

> Lösungsansätze:
> T3 auf 10 Tabellen aufsplitten (erste stelle der ID) und je nach
> enthaltenen ID's die Schleife durschlaufen.
>
> oder anstelle von insert into ... select
> dies für jeden einelnen DS in TMP durchführen
>
> oder ?
>
> Danke fürs Kopfzerbrechen

Du bist doch nicht erst seit gestern hier unterwegs - warum schickst Du
so ein unnötiges Posting ab? Wenn Du Hilfe willst: Du weißt doch, was
die Leute hier dazu brauchen. Wenn Du keine Hilfe willst: Was dann?
Menschliche Wärme? Anderen die Zeit stehlen?

Übrigens schadet es gar nichts, wenn man Postings erstmal auf
Rechtschreibung etc. kontrolliert, bevor man auf Senden klickt. Vielmehr
erhöht bessere Lesbarkeit die Chance auf Antworten.

Re: Große Datenmengen "vergleichen"

Hi

Christian Kirsch schrieb:
> n.Olivier schrieb:
> > Hallo
> >
> > Eine Frage ob es dafür eine Verbesserung gibt, da manchmal die

[..]

> Du bist doch nicht erst seit gestern hier unterwegs - warum schickst Du
> so ein unnötiges Posting ab? Wenn Du Hilfe willst: Du weißt doch, was

Das "Problem" ist Code in der Größenordungen von fast 50 kb
in vernünftiger weise zu kürzen um es noch halbwegs verständlich
rüber zu bringen.

> > Aufgabe: gesucht wird z.B. hammburg (bewusst mit 2 m)
> Lösung: Atlas, Generalkarte.

Bei diesem Scenario verzichte ich bewust auf Karten,
da diese Suche auch in Gegenden funzen soll, wo
noch keine oder nur schlechte Karten verfügbar sind:
zB.: Acapulco

Dann Versuchen wir es nochmals mit etwas detailierteren Informationen:

Location (T1.ID) die einem Angebot zugewiesen wird:
T1 =3D ID,bundesland_id,region_id, .. ,ort_id,
geolgrad,geobgrad

Auflösung von T1 durch zuweisung in T2
T2 =3D id,name,lang

Entfernungen von T1.ID zu T1.ID+x
T3=3Dvon,nach,entfernung (von,nach sind T1.ID zugewiesen)

So jetzt gibt der User z.B. hammburg (=3Dmit Tippfehler) ein.
Mit einer Mischnung aus like, match und soundex
werden aus T2 die besten übereinstimmungen ausgelesen.

Anhand dieser Übereinstimmungen (ID's) werden die in
Frage kommenden Daten aus T1 geladen und je nach
Feld mit einem Multiplikator bewertet.

Beispiel: id, name, wert
1 Hamburg 39.8
6 Harburg 41.5
7 Heinburg 37.2
9 Hunberg 38.5

Nun mittels T1 werten:
Wenn ID in bundesland dann z.B. wert x 3
Wenn ID in ort dann z.B. wert x 10

Nun alle gefundenen Einträge Summieren anhand der ID in T1
Dann erhält mann eine TMP-Tabelle mit folgendem Inhalt
T1.ID (z.B.17), Name (z.B. Hamburg), Wert (z.B. 625.25)

So, bis hierher geht alles ganz schnell, meist unter 1 Sec.

Jetzt sollen für alle in TMP enthaltenen "TOP" Werte
(=3D mind. 90% vom maximalsten Wert) jene ID's gesucht
werden, die in einem gewissen Umkreis zur aktuellen
ID liegt. Diese Werte sind in T3.
von (ID) nach (ID) entfernung

Insert into TMP (T1_id,entfernung,wert,nachort)
SELECT distinct T3.nach,
T3.entfernung,
wert /50 * ( 47 - T3.entfernung ) AS wert,
TMP.ortname
FROM T3
INNER JOIN T1 ON T3.nach =3D T1.id
INNER JOIN TMP ON TMP.T1_id =3D T3.von
WHERE TMP.wert >=3D 580
GROUP BY T3.nach
having wert >=3D 290
order by wert desc limit 1000

Und diese Abfrage bereitet Probleme. Sie ist zwar "voll" Indiziert und
braucht im
normalfall weniger als 2 Sec. aber alle paar Minuten sind Ausreisser
dabei mit
bis zu 60 Sec. Meist dann wenn zu "viele" Daten gefunden werden bzw.
in TMP enthalten sind. Mit nur 7 Mio DS in T3 kam es so gut wie nie
zu
solchen Ausreissern.

Daher die Frage ob es sinnvoll wäre, T3 auf 10 Tabellen aufzusplitten
(einzig machbar anhand der ersten Stelle der ID) und dann
gegebenenfalls
eben 1x bis zu 10x (je nach enthaltener ID's) die Abfrage auszuführen

Insert into TMP (T1_id,entfernung,wert,nachort)
SELECT distinct T3-1.nach, ....

Insert into TMP (T1_id,entfernung,wert,nachort)
SELECT distinct T3-2.nach, ....

usw.

ODER eben anstelle von
Insert into TMP (T1_id,entfernung,wert,nachort)
SELECT distinct T3-1.nach, ....

Zuerst alle in Frage kommenden Daten aus TMP laden
select T1_ID from ...

und dann mittels
Insert into TMP (T1_id,entfernung,wert,nachort)
SELECT distinct T3-2.nach, ....
[...]
WHERE T3.id =3D
[...]

bzw.
WHERE T3.id in ()
[...]

ODER einfach damit Leben

ODER eine andere Lösung

Jetzt klarer?

Mfg n.Olivier