Schwieriger (?) SELECT

am 08.10.2006 11:46:20 von Philipp Taprogge

Hallo!

Ich habe hier ein Problem, bei dem ich langsam an die Grenzen meines SQL
stoße...
Vielleicht kann mir jemand von euch sagen, ob das hier überhauppt geht
und wenn ja ein paar Tips zum entsprechenden Statement geben.

Ich muß eine Alt-Datenbank in ein neues System migrieren. Dazu selecte
ich die entpsrechenden Datensätze, schreibe sie um und speichere sie in
einer neuen Datenbank wieder ab.
Die alte DB ist leider völlig ohne foreign key constraints erstellt
worden und dem entpsrechend durcheinander.
Ich habe (unter anderem) folgende Tabellen:

T_ITEMS
itemid : integer
....

T_PROZESSE
prozessid : integer
prozesstyp : integer
....

T_ITEMS_PROZESSE
itemid : integer
prozessid : integer
....

Es _sollte_ so sein, daß ein Item immer nur in höchstens einem Prozess
pro Prozesstyp steckt. Leider gibt es aber eine ganze Reihe Items, die
in zwei oder mehr Prozessen vom Typ "3" sind.

Was ich möchte, ist ein result set, das folgendes enthält:
itemid, [die itemcolumns], prozessid [numerisch größter Typ-"3"-Prozess
für dieses Item], [die prozesscolumns für diesen Prozess].

Ist das mit SQL allein zu leisten oder muß ich die Datensätze auf
Applikationsebene filtern?

Danke schon mal und bis denne,

Phil

Re: Schwieriger (?) SELECT

am 08.10.2006 12:37:11 von newsgroup

Philipp Taprogge schrieb:
> Hallo!
>
> Ich habe hier ein Problem, bei dem ich langsam an die Grenzen meines SQL
> stoße...
> Vielleicht kann mir jemand von euch sagen, ob das hier überhauppt geht
> und wenn ja ein paar Tips zum entsprechenden Statement geben.
>
> Ich muß eine Alt-Datenbank in ein neues System migrieren. Dazu selecte
> ich die entpsrechenden Datensätze, schreibe sie um und speichere sie in
> einer neuen Datenbank wieder ab.
> Die alte DB ist leider völlig ohne foreign key constraints erstellt
> worden und dem entpsrechend durcheinander.
> Ich habe (unter anderem) folgende Tabellen:
>
> T_ITEMS
> itemid : integer
> ...
>
> T_PROZESSE
> prozessid : integer
> prozesstyp : integer
> ...
>
> T_ITEMS_PROZESSE
> itemid : integer
> prozessid : integer
> ...
>
> Es _sollte_ so sein, daß ein Item immer nur in höchstens einem Prozess
> pro Prozesstyp steckt. Leider gibt es aber eine ganze Reihe Items, die
> in zwei oder mehr Prozessen vom Typ "3" sind.
>
> Was ich möchte, ist ein result set, das folgendes enthält:
> itemid, [die itemcolumns], prozessid [numerisch größter Typ-"3"-Prozess
> für dieses Item], [die prozesscolumns für diesen Prozess].
>

Meinst Du sowas:

Select t_items.*, t_prozesse.*
from t_items, t_prozesse
where t_prozesse.prozessid =
( select max(t_items_prozesse.prozessid)
from t_items_prozesse, t_prozesse
where t_items.itemid = t_items_prozesse.itemid
and t_prozesse.prozessid = t_items_prozesse.prozessid
and t_prozesse.prozesstyp = 3 )

Gruß,
Michael

Re: Schwieriger (?) SELECT

am 08.10.2006 20:34:23 von gandolph

Michael König schrieb:
>> Was ich möchte, ist ein result set, das folgendes enthält:
>> itemid, [die itemcolumns], prozessid [numerisch größter
>> Typ-"3"-Prozess für dieses Item], [die prozesscolumns für diesen
>> Prozess].
>>
>
>
> Meinst Du sowas:
>
> Select t_items.*, t_prozesse.*
> from t_items, t_prozesse
> where t_prozesse.prozessid =
> ( select max(t_items_prozesse.prozessid)
> from t_items_prozesse, t_prozesse
> where t_items.itemid = t_items_prozesse.itemid
> and t_prozesse.prozessid = t_items_prozesse.prozessid
> and t_prozesse.prozesstyp = 3 )
>
> Gruß,
> Michael

Ich persönlich muss sagen, irgendwie is das ein wenig verwirrend. In
dieser Lösung fehlt zumindest ein "ORDER BY prozessid DESC". Dann hast
du die Prozesse schonmal so sortiert, wie du sie haben willst.

Das Ausklammern der nicht gewollten Prozesse wirst du allerdings per
Script machen müssen, da du innerhalb der Abfrage keine Arrays ablaufen
kannst.

Das Script sähe folgendermassen aus:

Du steckst jede Information in das passende Array mit der passenden
Markierung

Kurzbeispiel in PHP (um da was detaillierteres zu verfassen, müsste ich
das Problem besser verstehen, aber mit einigen Grundlagen in PHP und dem
Wissen über Schleifen sollte man das hinkriegen...)

for ($i=0; $i < mysql_num_rows(); $i++)
{
$aItem[$i] = mysql_result(blabla);
etc.
}

for ($i=0; $i < mysql_num_rows(); $i++)
foreach ($aItem as $keks)
{
if ($aItem[$i] == $aProzess[$o])
$aErgebnis[$o][$i] = $aItem[$i];
$o++;
}

Re: Schwieriger (?) SELECT

am 08.10.2006 22:06:07 von Siegfried Schmidt

Hallo Christian,

> Ich persönlich muss sagen, irgendwie is das ein wenig verwirrend. In
> dieser Lösung fehlt zumindest ein "ORDER BY prozessid DESC". Dann hast
> du die Prozesse schonmal so sortiert, wie du sie haben willst.

Die Prozesse werden nicht sortiert, sondern nach der höchsten id
ausgewählt.

> Das Ausklammern der nicht gewollten Prozesse wirst du allerdings per
> Script machen müssen, da du innerhalb der Abfrage keine Arrays ablaufen
> kannst.

Wo ist da ein Array?

Siegfried
--
http://www.schmidt.ath.cx

Re: Schwieriger (?) SELECT

am 08.10.2006 22:38:14 von newsgroup

Christian Hofmann schrieb:
> Michael König schrieb:
>
>>> Was ich möchte, ist ein result set, das folgendes enthält:
>>> itemid, [die itemcolumns], prozessid [numerisch größter
>>> Typ-"3"-Prozess für dieses Item], [die prozesscolumns für diesen
>>> Prozess].
>>>
>>
>>
>> Meinst Du sowas:
>>
>> Select t_items.*, t_prozesse.*
>> from t_items, t_prozesse
>> where t_prozesse.prozessid =
>> ( select max(t_items_prozesse.prozessid)
>> from t_items_prozesse, t_prozesse
>> where t_items.itemid = t_items_prozesse.itemid
>> and t_prozesse.prozessid = t_items_prozesse.prozessid
>> and t_prozesse.prozesstyp = 3 )
>>
>> Gruß,
>> Michael
>
>
> Ich persönlich muss sagen, irgendwie is das ein wenig verwirrend. In
> dieser Lösung fehlt zumindest ein "ORDER BY prozessid DESC". Dann hast
> du die Prozesse schonmal so sortiert, wie du sie haben willst.

Was genau hast Du an der Umsetzung des Satzes
"numerisch größter Typ-"3"-Prozess für dieses Item"
in
select max(t_items_prozesse.prozessid) ...
and t_prozesse.prozesstyp = 3

nicht verstanden? Vielleicht kann ich ja dann etwas Starthilfe geben.

> Das Ausklammern der nicht gewollten Prozesse wirst du allerdings per
> Script machen müssen, da du innerhalb der Abfrage keine Arrays ablaufen
> kannst. [...]

Wieso brichst Du Dir einen ab mit irgendwelchem php-Geschribsel, wenn
der OP doch ausdrücklich schreibt

"Ist das mit SQL allein zu leisten ..." und ich dir Antwort darauf mit
einem klaren JA (es geht mit reinem SQL) beantworten konnte.

Nichts für ungut, aber Dein Beitrag hat MICH verwirrt... ;-)

Michael

Re: Schwieriger (?) SELECT

am 09.10.2006 11:40:50 von Philipp Taprogge

Re: Schwieriger (?) SELECT

am 09.10.2006 11:51:11 von Christian Kirsch

Re: Schwieriger (?) SELECT

am 09.10.2006 12:23:53 von Philipp Taprogge

Hi!

Christian Kirsch wrote:
> \G vermeidet diese, das hatten wir neulich erst hier.

Ah, sorry. Wieder was gelernt.

> 300000 und 600000 Zeilen und kein möglicher Schlüssel? Das ist böse.

Ich weiß... drum soll dieses Monster ja auch weg.

> In erster Näherung könnten Indizes auf t_items_prozesse.prozessid,
> t_items.itemid, t_items_prozesse.prozessid und t_items_prozesse.itemid
> helfen.

t_items.itemid und t_prozesse.prozessid sind primary keys. Das Problem
scheint zu sein, daß diese primary keys varchars sind (das war weiß Gott
nicht meine Idee). Indizes auf t_items_prozesse.prozenssid und itemid
hab ich jetzt mal hinzugefügt.
Ein explain sagt jetzt:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t_items
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 333342
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t_prozesse
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 34
ref: func
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t_prozesse
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 4252
Extra: Using where
*************************** 4. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t_items_prozesse
type: ref
possible_keys: prozessid
key: prozessid
key_len: 70
ref: data_old.t_prozesse.processid,data_old.t_items.itemid
rows: 1
Extra: Using where; Using index
4 rows in set (0.00 sec)

Damit dauert es zwar immer noch brutal lange, aber immerhin wird's in
endlicher Zeit fertig. Nach etwa 5 Minuten darf ich mir Ergebnisse
abholen (wen's interessiert: Pentium D 2.66 mit 3 GB RAM).
Damit werd ich leben können.

Danke für die Hilfe,

Phil

Re: Schwieriger (?) SELECT

am 09.10.2006 17:30:28 von gandolph

Michael König schrieb:
> Was genau hast Du an der Umsetzung des Satzes
> "numerisch größter Typ-"3"-Prozess für dieses Item"
> in
> select max(t_items_prozesse.prozessid) ...
> and t_prozesse.prozesstyp = 3
> nicht verstanden? Vielleicht kann ich ja dann etwas Starthilfe geben.

das max(), war mir bis dato unbekannt, daher hab ich auch nicht drauf
geachtet

> Wieso brichst Du Dir einen ab mit irgendwelchem php-Geschribsel, wenn
> der OP doch ausdrücklich schreibt
>
> "Ist das mit SQL allein zu leisten ..." und ich dir Antwort darauf mit
> einem klaren JA (es geht mit reinem SQL) beantworten konnte.

Mangels meiner Kenntnis über max() habe ich die Frage nicht als komplett
beantwortet angesehen...

C. Hofmann

Re: Schwieriger (?) SELECT

am 09.10.2006 19:37:10 von newsgroup

Philipp Taprogge schrieb:
> Hi!
>
>[...]
>
>
> Damit dauert es zwar immer noch brutal lange, aber immerhin wird's in
> endlicher Zeit fertig. Nach etwa 5 Minuten darf ich mir Ergebnisse
> abholen (wen's interessiert: Pentium D 2.66 mit 3 GB RAM).
> Damit werd ich leben können.
>
> Danke für die Hilfe,
>
> Phil

Das kommt mir eingetlich immernoch etwas lang vor.
Also nochmal den Select

Select t_items.*, t_prozesse.*
from t_items, t_prozesse
where t_prozesse.prozessid =
( select max(t_items_prozesse.prozessid)
from t_items_prozesse, t_prozesse
where t_items.itemid = t_items_prozesse.itemid
and t_prozesse.prozessid = t_items_prozesse.prozessid
and t_prozesse.prozesstyp = 3 )

Ohne mir jetzt nochmal anzuschauen, was Du an Indexen hast:
Würde ich für diesen Select folgendes empfehlen:

Da Du alle Zeilen aus t_items haben möchtest, wird da kein Index etwas
helfen (für diesen Select).

Da Du bestimmte Zeilen aus t_prozesse über die prozessid ziehst, sollte
da ein Index drauf sein. Da Du später auch noch auf den Typ einschränkst
wäre gleich ein zusammengesetzter Index auf "prozessid, prozesstyp"
geeignet.

Und auf t_items_prozesse wäre ein zusammengesetzter
auf "itemid, prozessid" gut.

Damit sollte es eigentlich auch bei ein paar tausend Records,
einigermassen flutschen (denn eigentlich sind die Tabellen nicht groß).

Gruß,
Michael

PS: Ich gehe natürlich davon aus, dass die Columntypen von itemid bzw.
prozessid auf den jeweiligen Tabellen jeweils identisch sind.