optimierungsmöglichkeiten db-abfrage

prefix · 14.01.2004, 14:17

hi - diesmal werd ich mir diese frage sicher nicht beantworten, da ich nach einer antwort schon wochenlang suche

ich hab eine tabelle mit etwa 17.000 datensätzen, die wie folgt aufgebaut ist:

ich bekomme 1x pro woche ein textfile, dass ich dann importiere (d.h. davor die inhalte der tabelle lösche, die neuen reinschreiben).

an der tabellenstruktur kann ich noch änderungen vornehmen, allerdings muss ich jene felder, die jetzt da sind, auch so lassen (ich kann noch welche hinzufügen)!

ok, da versicherungen orgnummern eingeführt haben, noch bevor es die ersten pcs gab, sind diese alphanumerisch, z.b.:

NG00000 (das wäre jemand, der ein bundesland über hat)

NGA0000 (jemand, der ng00000 unterstellt ist)

usw.

hier nun das riesige performanceproblem: mir geht natürlich der mysql-server in die knie und braucht ewig, diese 17.000 datensätze in 5 hierarchiestufen aufzusplitten.

die ersten beiden geb ich wie folgt aus (die variablen, die darin vorkommen, sind vorher generiert worden):

PHP-Code:


			
// vertriebsweg orga -> hierarchische listung



//echo "<b>VERTRIEBSWEG ORGA</b><hr>";



    // 1. ebene

    

    $vmartorga1 = "'LL' OR vmart LIKE 'OD'";

    $abfrage = mysql_query("SELECT COUNT(*) AS orga1hits FROM fdaw_vmdaten WHERE (orgnr LIKE '%00000') $ldstring");

    $ausgabe = mysql_fetch_object($abfrage);

    $orga1hits = $ausgabe->orga1hits;

    //echo "<b>Hierarchie Stufe 1: $orga1hits Datensätze</b><br><br>";

    

    // 1. stufe ausgeben

    $abfrage = mysql_query("SELECT DISTINCT orgnr,name FROM fdaw_vmdaten WHERE orgnr LIKE '%00000' AND orgnr NOT LIKE '%000000' $ldstring ORDER BY name ASC");

    while($ausgabe = mysql_fetch_object($abfrage)) {

        $orgnrli2 = substr($ausgabe->orgnr,0,2);

        echo "<hr>$ausgabe->orgnr|$ausgabe->name<br><hr>";

        // 2. stufe ausgeben

            //$abfrage2 = mysql_query("SELECT DISTINCT orgnr, name, orgnr REGEXP \"^.{2}[A-Z]\" AS orgnr_isstring FROM fdaw_vmdaten WHERE orgnr_isstring = 1 ORDER BY orgnr ASC");

            $abfrage2 = mysql_query("SELECT DISTINCT orgnr, name FROM fdaw_vmdaten WHERE (LEFT(orgnr,2) = '$orgnrli2') AND (orgnr NOT LIKE '$ausgabe->orgnr') 

            AND (orgnr REGEXP \"^.{2}[A-Z]\" = 1) ORDER BY orgnr ASC");

            while ($ausgabe2 = mysql_fetch_object($abfrage2)) {

                $orgnrli3 = substr($ausgabe2->orgnr,0,3);

                echo "&nbsp;&nbsp;&nbsp;$ausgabe2->orgnr|$ausgabe2->name<br>";



        }

        

        // ende 2. stufe

    }

    // ende 1. stufe

beim einspielen der datensätze wäre es mir nun möglich, ev. neue felder und daten zu generieren, welche die ausgabe beschleunigen könnten! ok, zusatzfelder mit numerischen werten wären dafür ideal, nur hab ich echt keine idee, wie ich die anlegen soll und welche numerischen werte ich zuweisen soll

ich weiß, das ganze ist lang und heavy, aber vielleicht habt ihr ja einen tipp für mich - ich verzweifle daran

Stuck Mojo · 14.01.2004, 14:21

Wie du schon geschrieben hast, solltest du weitere Spalten hinzufügen.
Die Spalten müssen halt so strukturiert sein, dass du dir die Regexe und die LIKE-Abfragen sparst und somit die Struktur sich automatisch in der Tabelle abbildet.

Gruss
Jan

prefix · 14.01.2004, 14:24

d.h. ein datensatz sieht z.b. so aus (den ich bekomme im txt-file):

AHA0003;A000000;1132826;OVB ALLFINANZVERMITTLUNGS GMBH.;19941101;99991231;MAK

ich schreib den rein, wie gehabt (d.h. ich muss zuerst mal alle einfügen). danach mach ich ein update-statement, indem ich zu diesem den vorgesetzten (AH00000) suche und schreib dessen id in ein extra feld! richtig?

dann kann ich danach ja per id runterfragen

mike · 14.01.2004, 14:25

vielleicht solltes du auch andenken, die hierarchiestufen in einer eigenen Tabelle abzubilden. dann bleiben dir die Überraschungen aus, wenn es irgendwann mal eine sechste Stufe geben soll.

prefix · 14.01.2004, 14:25

hast schon recht, michi - aber die 5 stufen gibt es schon seit ca. 10000 jahren oder so harhar - die werden sich nicht ändern

prefix · 14.01.2004, 15:52

so, hab jetzt folendes script, dass bei der datenübernahme schon die richtige hierarchie erzeugt:

PHP-Code:


			
<?php

include("../../includes/rechte.php");

include("../../includes/userlevel_5.php");



// zeitmessung start

$zeitmessung1=microtime(); 

$zeittemp=explode(" ",$zeitmessung1); 

$zeitmessung1=$zeittemp[0]+$zeittemp[1];

// zeitmessung ende



// ausgabe stufe 1



$abfrage1 = mysql_query("SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten WHERE (stufe = '1') AND 

(orgnr LIKE '%00000' AND orgnr NOT LIKE '%000000') ORDER BY name ASC");

while ($ausgabe1 = mysql_fetch_object($abfrage1)) {

    $orgnrstart = substr($ausgabe1->orgnr,0,1); // für 2. ebene

    $orgnrli1 = substr($ausgabe1->orgnr,0,2); // ersten 2 stellen stufe 1

    

    # id auslesen und updaten

    $id1 = $ausgabe1->id;

    $action = mysql_query("UPDATE fdaw_vmdaten SET vorgesetzter = '0' WHERE id = '$id1'");

    

    

    // ausgabe 2. stufe

    

    # hier bestehen nur datensätze zu bank!

    

    $abfrage2 = mysql_query("SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten WHERE 

    ((stufe = '2') AND (orgnr LIKE '$orgnrli1%')) ORDER BY name ASC");

    while ($ausgabe2 = mysql_fetch_object($abfrage2)) {

        $orgnrli2 = substr($ausgabe2->orgnr,0,3);

        //echo "&nbsp;&nbsp;&nbsp;$ausgabe2->orgnr | $ausgabe2->name | Hierarchiestufe: $ausgabe2->stufe | Vertriebsweg: $ausgabe2->vertriebsweg | $ausgabe2->vmart<br>";

        

        #id auslesen

        $id2 = $ausgabe2->id;

        $action = mysql_query("UPDATE fdaw_vmdaten SET vorgesetzter = '$id1' WHERE id = '$id2'");

        

        // ausgabe 3. stufe

        $abfrage3 = mysql_query("SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten WHERE 

        ((stufe = '3') AND (orgnr LIKE '$orgnrli2%')) ORDER BY name ASC");

        while ($ausgabe3 = mysql_fetch_object($abfrage3)) {

            $orgnrli3 = substr($ausgabe3->orgnr,0,4);

            //echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

            //$ausgabe3->orgnr | $ausgabe3->name | Hierarchiestufe: $ausgabe3->stufe | Vertriebsweg: $ausgabe3->vertriebsweg | $ausgabe3->vmart<br>";

            

            # id auslesen

            $id3 = $ausgabe3->id;

            $action = mysql_query("UPDATE fdaw_vmdaten SET vorgesetzter = '$id2' WHERE id = '$id3'");

            

            

            // ausgabe 4. stufe

            $abfrage4 = mysql_query("SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten WHERE 

            ((stufe = '4') AND (orgnr LIKE '$orgnrli3%')) ORDER BY name ASC");

            while ($ausgabe4 = mysql_fetch_object($abfrage4)) {

                $orgnrli4 = substr($ausgabe4->orgnr,0,5);

                //echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

                //$ausgabe4->orgnr | $ausgabe4->name | Hierarchiestufe: $ausgabe4->stufe | Vertriebsweg: $ausgabe4->vertriebsweg | $ausgabe4->vmart<br>";

                

                # id auslesen

                $id4 = $ausgabe4->id;

                $action = mysql_query("UPDATE fdaw_vmdaten SET vorgesetzter = '$id3' WHERE id = '$id4'");

                

                

            

                // ausgabe 5. stufe

                $abfrage5 = mysql_query("SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten WHERE 

                ((stufe = '5') AND (orgnr LIKE '$orgnrli4%')) ORDER BY name ASC");

                while ($ausgabe5 = mysql_fetch_object($abfrage5)) {

                    //echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

                    //$ausgabe5->orgnr | $ausgabe5->name | Hierarchiestufe: $ausgabe5->stufe | Vertriebsweg: $ausgabe5->vertriebsweg | $ausgabe5->vmart<br>";

                    

                    # id ausgeben

                    $id5 = $ausgabe5->id;

                    $action = mysql_query("UPDATE fdaw_vmdaten SET vorgesetzter = '$id4' WHERE id = '$id5'");

                    

                    

                }

                // ende 5. stufe

            }

            // ende 4. stufe

        }

        // ende 3. stufe

    }

    // ende 2. stufe

}

// ende stufe 1



// zeitmessung start

$zeitmessung2=microtime(); 

$zeittemp=explode(" ",$zeitmessung2); 

$zeitmessung2=$zeittemp[0]+$zeittemp[1]; // Timestamp + Nanosek 

$zeitmessung=$zeitmessung2-$zeitmessung1; // Differenz der beiden Zeiten 

$zeitmessung=substr($zeitmessung,0,8); // es wird auf 6 Kommastellen gekürzt

// zeitmessung ende



echo "Vorgesetzte wurden erfolgreich zugewiesen!<br><br>Dauer dieses Scripts: $zeitmessung Sekunden";

//header("Location:[url]http://10.113.68.36/transfer/vmupdate/modul_ende.php[/url]");

?>

<html>

<head>

<meta http-equiv="refresh" content="3; URL=http://10.113.68.36/transfer/vmupdate/modul_ende.php">

</head>

</html>

jetzt noch eine frage:

wie schaff ich es, dass sich das script nach allen 100 datensätzen neu lädt und ich somit einen überblick hab, bei welchem datensatz es gerade ist?

prefix · 14.01.2004, 16:16

so, jetzt hab ich noch 2 kleinere probleme:

1. ich hab ja folgende abfrage (auszug):

PHP-Code:


			
SELECT DISTINCT orgnr, name, stufe, vertriebsweg, vmart, id FROM fdaw_vmdaten

ich muss hier auch "id" ausgeben, weil ich aufgrund dieser ja danach zuweise - allerdings möchte ich nicht "distinct .... id" verwenden, sondern nur eine id unter den gefundenen gleichen orgnr (z.b. die niedrigste) verwenden (leider krieg ich mehrere gleiche datensätze rein, daher die distinct-abfrage).

d.h. problem 1: werden 10 "AC00000" gefunden, soll der mir den niedrigsten nehmen und auch dessen id mitgeben - wie geht das?

2. problem:

das script läuft, ich hab auf der db gesehen, dass alle datensätze bearbeitet waren, aber es hat nicht weitergeleitet

kann das mit problem 1 zusammenhängen?

wie kann ich bitte von 10 gleichen datensätzen 9 löschen?

prefix · 15.01.2004, 09:02

ok, dieses problem mal gelöst - jetzt hab ich nur noch eines:

mit folgender query lösche ich alle datensätze, die "orgnr" und "vmnr" gleich haben (bis auf einen):

PHP-Code:


			
$abfrage = mysql_query("SELECT COUNT(*) AS hits FROM fdaw_vmdaten");

$ausgabe = mysql_fetch_object($abfrage);

$hits1 = $ausgabe->hits;

$i = $ausgabe->hits;



$abfrage = mysql_query("SELECT * FROM fdaw_vmdaten ORDER BY id ASC");

while ($ausgabe = mysql_fetch_object($abfrage)) {

    $id = $ausgabe->id;

    $orgnr = $ausgabe->orgnr;

    $vmnr = $ausgabe->vmnr;

    // übrige datensätze löschen

    $action = mysql_query("DELETE FROM fdaw_vmdaten WHERE orgnr = '$orgnr' AND vmnr = '$vmnr' AND id != '$id'");

    echo "<b>$i</b> noch zu bearbeiten!<br>";

    $i--;

    

}

die query dauert bei rund 17000 datensätzen ewig - kann ich die optimieren, sodass sie ein wenig flotter läuft?

Stuck Mojo · 15.01.2004, 09:24

Was heist ewig? Sind die Indizes richtig gesetzt?

prefix · 15.01.2004, 09:27

ewig heißt ca. 20 minuten für die 17000 datensätze

der index liegt zzt. auf der id!

ah, hier wäre es vmtl. sinnvoll, auch vmnr und orgnr als index zu setzen, oder?

hab die query jetzt mal so abgeändert:

PHP-Code:


			
$action = mysql_query("DELETE FROM fdaw_vmdaten WHERE orgnr = '$ausgabe->orgnr' AND vmnr = '$ausgabe->vmnr' AND id > '$ausgabe->id'");

durch den letzen teil (AND id > '') hab ich schon mal eine besserung erreicht (da es != in mysql ja anscheinend nicht gibt); weiters les ich die MIN(id) vorher aus - daher kann ich sagen, dass nur die gleichen mit höherer id gelöscht werden sollen!

frage: wann baut sich ein index denn auf bzw. wie bau ich den auf, wenn ich ihn definiert hab?

Stuck Mojo · 15.01.2004, 09:34

Index wird automatisch beim anlegen erstellt. Je nachdem wie viele Daten drin sind, dauert das halt einmalig nen kleinen Moment. Danach sollte es aber fixer gehen. Evtl. solltest du auch die ID als erstes WHERE Statement nehmen, da die Reihenfolge auch beachtet wird. Wahrscheinlich sucht er so zu Beginn nach den Datensätzen in organr. Da kein Index vorhanden ist, durchsucht er die komplette Tabelle. Wenn es hier einen Treffer gibt, geht er ins zweite WHERE-Statement -> vmnr ...dort passiert genau das gleiche. Erst zum Schluss kann er einen Index benutzen... da aber nun nurnoch ein paar Daten übrig sind, bringt das an der Stelle nicht viel

17000 x 17000 Datensätze nach orgnr durchsuchen = 289000000

-> gibt bestimmt nen guten overhead

Gruss
Jan

prefix · 15.01.2004, 09:43

danke dir, jan

mal sehen, wieviel zeit das bringt!

gerade die hiobsbotschaft erhalten, dass die platte am intranet-server kaputt ist - somit werd ich das erst morgen testen können

prefix · 15.01.2004, 09:58

hmm, also macht es sinn, wenn ich folgendes statements hinzufüge:

PHP-Code:


			
$action = mysql_query("ALTER TABLE fdaw_vmdaten ADD INDEX (vmnr)");

$action = mysql_query("ALTER TABLE fdaw_vmdaten ADD INDEX (orgnr)");

richtig?! das sollte mir die queries beschleunigen?!

Stuck Mojo · 15.01.2004, 10:02

Du hast doch mit Sicherheit ein Programm, indem du deine Tabellen verwalten kannst (phpmyadmin z.b.), oder? Dort kannst du die Indizes erstellen. Oder halt, per Hand, wie du geschrieben hast. Das brauchst du aber nur einmal! zu machen. Danach werden diese Spalten inidziert.

prefix · 15.01.2004, 10:03

alright - arbeite mit mysql-front

ok, da ich mit den ganzen hier geposteten scripts ja nur einmal wöchentlich die daten übernehme, werd ich das hier drinnenlassen, da es sich ja um neu-daten handelt

danke jan - ich durchschweife grad das mysql-manual nach weiteren optimierungsmöglichkeiten hehe

edit: hab übrigens eine sehr interessante und verständliche erklärung zum setzen von indizes gefunden -> klick

jetzt wird mir einiges klar...