mySQL (PHP) - Select in Select optimieren
Hei
Für ein momentanes Projekt, befasse ich mich mit SQL, wo ich mich sonst nicht gerade zuhause fühle.
Ich möchte aus den Tabellen:
- Student
- Assignment
- Module
- Prerequisite
jene Module selektieren, welche bestimmte Bedingungen in den anderen Tabellen erfüllen.
Die DB-Struktur ist teilsvorgegeben und entspricht daher nicht den Normalisierungsregeln, wie die meisten von euch sie wohl kennen oder erwarten würden.
Zwar habe ich zwei funktionierende Lösungen gefunden, jedoch sind beide extrem langsam.
Hier die zwei "Lösungen":
Lösung mit Select in Select
Lösung mit View:PHP-Code:/**
* Gets all Modules which the Student has to visit in the next semester.
*
* Possible selection for Term:
* term6, term8 or term9
*
* Possible selection for ascDesc:
* ASC or DESC
*
* @param String $acronyme
* @param String $term The term of the Student
* @param int $nextSemester
* @param String $order The order in which the Modules have to be
* @param String $ascDesc ASC or DESC
* @return Module[] $obj An array of the Module class objects.
*/
public function getModulesMust($acronyme, $term, $nextSemester, $orderBy, $ascDesc){
try {
// Actual selection
$sql = "SELECT tempTable.*
FROM ( SELECT m.*
FROM students s
RIGHT JOIN assignment a
ON '" . $acronyme ."' = a.student_id
RIGHT JOIN modules m
ON m.number = a.module_id
WHERE (
a.module_id IS NULL
AND m.type NOT LIKE 'D%'
AND (
a.state = 0
OR
a.state = 3
)
)
OR (
m.type NOT LIKE 'D%'
AND m.type != 'C'
AND m." . $term . " <= " . $nextSemester . "
)
OR (
m.type = 'C'
AND m.major_id = s.major_id
AND m." . $term . " <= " . $nextSemester . "
)
GROUP BY m." . $orderBy . "
)
AS tempTable,
prerequisite p,
assignment a,
modules m
WHERE ( tempTable.number = p.module_id
AND p.prerequisite_module_id = m.number
AND m.number = a.module_id
AND (a.state = 1 OR a.state = 2)
)
OR(
tempTable.prerequisite = 0
)
GROUP BY tempTable.number
ORDER BY tempTable." . $orderBy . " " . $ascDesc . "
";
$stmt = DB::getInstance()->query($sql);
$obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'Module');
return $obj;
}
catch(Exception $e){
echo $e->getMessage();
}
}
Kann mir jemand von euch einen Tipp geben, wie ich (eine der Beiden) Abfrage so optimieren kann, dass mir nicht das Gesicht einschläft, wärend ich auf die Ausgabe warte?PHP-Code:/**
* Gets all Modules which the Student has to visit in the next semester.
*
* Possible selection for Term:
* term6, term8 or term9
*
* Possible selection for ascDesc:
* ASC or DESC
*
* @param String $acronyme
* @param String $term The term of the Student
* @param int $nextSemester
* @param String $order The order in which the Modules have to be
* @param String $ascDesc ASC or DESC
* @return Module[] $obj An array of the Module class objects.
*/
public function getModulesMust($acronyme, $term, $nextSemester, $orderBy, $ascDesc){
try {
// Drop View if still exist
$swlDrop = "DROP VIEW IF EXISTS tempModules;";
DB::getInstance()->query($swlDrop);
// Create new View
$sqlView = "
CREATE VIEW tempModules
AS (
SELECT
m.type,
m.number,
m.title,
m.prerequisite,
a.student_id,
a.module_id,
a.state,
a.semester
FROM
students s
RIGHT JOIN assignment a
ON '" . $acronyme . "' = a.student_id
RIGHT JOIN modules m
ON m.number = a.module_id
WHERE (
a.module_id IS NULL
AND m.type NOT LIKE 'D%'
AND (
a.state = 0
OR
a.state = 3
)
)
OR (
m.type NOT LIKE 'D%'
AND m.type != 'C'
AND m." . $term . " <= " . $nextSemester . "
)
OR (
m.type = 'C'
AND m.major_id = s.major_id
AND m." . $term . " <= " . $nextSemester . "
)
GROUP BY m.number
)
";
DB::getInstance()->query($sqlView);
// Actual selection
$sql = "SELECT t.*
FROM tempModules t,
prerequisite p,
assignment a,
modules m
WHERE(
t.number = p.module_id
AND p.prerequisite_module_id = m.number
AND m.number = a.module_id
AND (a.state = 1 OR a.state = 2)
)
OR(
t.prerequisite = 0
)
GROUP BY t.number
ORDER BY t." . $orderBy . " " . $ascDesc . "
";
$stmt = DB::getInstance()->query($sql);
$obj = $stmt->fetchALL(PDO::FETCH_CLASS, 'Module');
// Drop View
DB::getInstance()->query($swlDrop);
return $obj;
}
catch(Exception $e){
echo $e->getMessage();
}
}
Gruess
Vitamin-R
Geändert von Vitamin-R (19.12.2008 um 18:08 Uhr) Grund: Wurde verschoben. Danke
Im Zweifelsfalle fragen sie ihren Arzt oder die Suchmaschine ihrer Wahl.
Hi,
trotz dem Code fällt eine Beurteilung schwer, da Eckdaten ja nicht bekannt sind
* welche DB-Engine wird benutzt
* welche DB-Klasse wird benutzt
Die SQL ist mächtig, und wir kennen die Tabellenstruktur nicht. Grossen Einfluss auf Geschwindigkeit sind die Keys und Indizes. Wenn die auf den wichtigen Feldern, die in beziehung stehen, enthalten sind, kann eine Abfrage gut mal um den Faktor 10 beschleunigt werden.
Gib mal relevantere Eckdaten
TYPO3 · MySQLDumper · dislabs
·
manche Mühlen mahlen schneller ...
"Ich habe Rücken"
Horst Schlämmer
Hei steffenk
Nun ich baue meine DB-Verbindung mittels:
auf.PHP-Code:new PDO("mysql:host=xxx;dbname=xxx", 'xxx', 'xxx');
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
die DB-Struktur habe ich mal als jpg angehängt.
Deinem Text zu Folge könnte es ein Problem sein, dass falsche Datentypen für die PKs verwendet werden?
Leider wurde bei den Klassen Studentadvisor und Students der Datentyp VCHAR verwendet. Könnte dies ein solches ausbremsen bewirken?
Im Zweifelsfalle fragen sie ihren Arzt oder die Suchmaschine ihrer Wahl.
Konnte das Problem in einer Nachtschicht doch noch lösen.
Meine Querry-Versuche, waren einfach total ungeeignet.
Läuft nun super flüssig ;]
Das wichtigste in Kürze:
In meinem SELECT verwende ich nun ein NOT IN.
Im Zweifelsfalle fragen sie ihren Arzt oder die Suchmaschine ihrer Wahl.
Prost
TYPO3 · MySQLDumper · dislabs
·
manche Mühlen mahlen schneller ...
"Ich habe Rücken"
Horst Schlämmer
Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
LinkBack URL
About LinkBacks
Zitieren