Toon posts:

[PHP/MySQL] Query in variabele

Pagina: 1
Acties:
  • 109 views sinds 30-01-2008
  • Reageer

Onderwerpen

Php

donderdag 14 juni 2007 10:51

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
Hallo allemaal,

Ik ben momenteel bezig met stored procedures, dit om de performance van onze applicatie te verbeteren.

We hadden dus eerst de volgende functie:

PHP:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

function getExpeditieOrdersByStatus($trefwoord, $orderby, $status, $AfdelingID, $orderregel){ 
        switch($status){ 
            case "order": 
                $where = "O.isOrder = 'true' 
                            AND O.isVerwijderd = 'false' 
                            AND ( 
                                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') > 0 
                                    OR 
                                    (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') > 0 
                                )"; 
                break; 
            case "teplannen": 
                $where = "O.isOrder = 'true' 
                            AND O.isBezorgen = 'true' 
                            AND O.isVerwijderd = 'false' 
                            AND (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') > 0 
                            AND (SELECT DATE_FORMAT(MIN(E.Bezorgdatum), '%d-%m-%Y') FROM exp_bezorgagenda E WHERE E.OrderID = O.OrderID) IS NULL"; 
                break; 
            case "historie": 
                $where = "O.isOrder = 'true' 
                            AND O.isVerwijderd = 'false' 
                            AND ( 
                                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') = 0 
                                    OR 
                                    (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') = 0 
                                )"; 
                break; 
        } 
        
        $qry = "SELECT  O.OrderID, 
                        O.KlantID, 
                        O.AfdelingID, 
                        O.Verkoper, 
                        O.isOfferte, 
                        O.Offertedatum, 
                        O.isOrder, 
                        O.Orderdatum, 
                        O.isVerwijderd, 
                        O.Verwijderdatum, 
                        O.Leverweekgewenst, 
                        O.isBezorgen, 
                        O.isMontage, 
                        O.isMeten, 
                        O.Meetstatus, 
                        O.isAansluitschema, 
                        O.Aansluitschemastatus, 
                        O.isHerinnering, 
                        O.Herinneringdatum, 
                        O.Herinneringtekst, 
                        O.BezorgvoertuigID, 
                    DATE_FORMAT(o.Offertedatum, '%d-%m-%Y') AS strOffertedatum, 
                    IF(o.Orderdatum <> '0000-00-00 00:00:00', DATE_FORMAT(o.Orderdatum, '%d-%m-%Y'), '-') AS strOrderdatum, 
                    DATE_FORMAT(o.Herinneringdatum, '%d-%m-%Y') AS strHerinneringdatum, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBesteld = 'false') Tebestellen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBinnen = 'false') Binnentemelden, 
                    ((SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBetaald = 'false') + (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isBetaald = 'false')) Tebetalen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') Televeren, 
                    (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') Uittevoeren, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isOrderbevestiging = 'true' AND A.Orderbevestigingnr = '') Tebevestigen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGesloten = 'false') Tesluiten, 
                    (SELECT DATE_FORMAT(MIN(E.Bezorgdatum), '%d-%m-%Y') FROM exp_bezorgagenda E WHERE E.OrderID = O.OrderID) Bezorgdatum, 
                    a.Afdelingnaam, 
                    k.Naam, 
                    k.Voorletter, 
                    k.Tussenvoegsel, 
                    k.Adres, 
                    k.Huisnr, 
                    k.Postcode, 
                    k.Plaats  
                FROM ord_order O 
                INNER JOIN bas_klant K 
                  ON O.KlantID = K.KlantID 
                INNER JOIN bas_afdeling A 
                  ON O.AfdelingID = A.AfdelingID 
                ".(($orderregel=='true')?"LEFT JOIN orl_artikel orl_a ON O.OrderID = orl_a.OrderID":"")." 
                WHERE (K.Naam Like '%".$trefwoord."%' 
                        OR K.Postcode Like '%".$trefwoord."%' 
                        OR K.Plaats Like '%".$trefwoord."%' 
                        OR O.OrderID Like '%".$trefwoord."%' 
                        ".(($orderregel=='true')?"OR orl_a.Omschrijving LIKE '%".$trefwoord."%' OR orl_a.Artikelnr LIKE '%".$trefwoord."%' OR orl_a.Magazijnlocatie LIKE '%".$trefwoord."%'":"").") 
                AND ". $where ." 
                AND ". (($AfdelingID != '')?"O.AfdelingID = ". $AfdelingID:"O.AfdelingID != '8' ") ." 
                ".(($orderregel=='true')?"GROUP BY o.OrderID":"")." 
                ORDER BY ".(!empty($orderby)?$orderby:"O.Leverweekgewenst ASC")." 
                LIMIT 500";    
                
        return $this->db->selectQuery($qry);    
    }


Hier was dus niets mis mee alleen dat de query veel tijd in beslag neemt.

Dus gaan we nu over op stored procedures. ik heb de volgende stored procedure gemaakt.

SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

CREATE PROCEDURE sp_em_getExpeditieOrdersByStatus(p_Trefwoord varchar(100), p_OrderBy varchar(100), p_Status varchar(100), p_AfdelingID INT, p_Orderregel varchar(100)) 
BEGIN 
    DECLARE whereStr varchar(100); 
    DECLARE qry varchar(500); 

    CASE p_Status 
    WHEN "order" 
    THEN SET whereStr =     "O.isOrder = 'true' 
                        AND O.isVerwijderd = 'false' 
                        AND ( 
                            (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') > 0 
                            OR 
                            (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') > 0 
                        )"; 

    WHEN "teplannen" 
    THEN SET whereStr =     "O.isOrder = 'true' 
                        AND O.isBezorgen = 'true' 
                        AND O.isVerwijderd = 'false' 
                        AND (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') > 0 
                        AND (SELECT DATE_FORMAT(MIN(E.Bezorgdatum), '%d-%m-%Y') FROM exp_bezorgagenda E WHERE E.OrderID = O.OrderID) IS NULL"; 

    WHEN "historie" 
    THEN SET whereStr =     "O.isOrder = 'true' 
                        AND O.isVerwijderd = 'false' 
                        AND ( 
                            (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') = 0 
                            OR 
                            (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') = 0 
                        )"; 
    END CASE; 

    SET qry = "SELECT  O.OrderID, 
                        O.KlantID, 
                        O.AfdelingID, 
                        O.Verkoper, 
                        O.isOfferte, 
                        O.Offertedatum, 
                        O.isOrder, 
                        O.Orderdatum, 
                        O.isVerwijderd, 
                        O.Verwijderdatum, 
                        O.Leverweekgewenst, 
                        O.isBezorgen, 
                        O.isMontage, 
                        O.isMeten, 
                        O.Meetstatus, 
                        O.isAansluitschema, 
                        O.Aansluitschemastatus, 
                        O.isHerinnering, 
                        O.Herinneringdatum, 
                        O.Herinneringtekst, 
                        O.BezorgvoertuigID, 
                    DATE_FORMAT(o.Offertedatum, '%d-%m-%Y') AS strOffertedatum, 
                    IF(o.Orderdatum <> '0000-00-00 00:00:00', DATE_FORMAT(o.Orderdatum, '%d-%m-%Y'), '-') AS strOrderdatum, 
                    DATE_FORMAT(o.Herinneringdatum, '%d-%m-%Y') AS strHerinneringdatum, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBesteld = 'false') Tebestellen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBinnen = 'false') Binnentemelden, 
                    ((SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isBetaald = 'false') + (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isBetaald = 'false')) Tebetalen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGeleverd = 'false') Televeren, 
                    (SELECT COUNT(-1) FROM orl_dienst D WHERE D.OrderID = O.OrderID AND D.isUitgevoerd = 'false') Uittevoeren, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isOrderbevestiging = 'true' AND A.Orderbevestigingnr = '') Tebevestigen, 
                    (SELECT COUNT(-1) FROM orl_artikel A WHERE A.OrderID = O.OrderID AND A.isGesloten = 'false') Tesluiten, 
                    (SELECT DATE_FORMAT(MIN(E.Bezorgdatum), '%d-%m-%Y') FROM exp_bezorgagenda E WHERE E.OrderID = O.OrderID) Bezorgdatum, 
                    a.Afdelingnaam, 
                    k.Naam, 
                    k.Voorletter, 
                    k.Tussenvoegsel, 
                    k.Adres, 
                    k.Huisnr, 
                    k.Postcode, 
                    k.Plaats 
                FROM ord_order O 
                INNER JOIN bas_klant K 
                  ON O.KlantID = K.KlantID 
                INNER JOIN bas_afdeling A 
                  ON O.AfdelingID = A.AfdelingID"; 

    IF (p_Orderregel = "true") THEN SET qry = CONCAT(qry, "LEFT JOIN orl_artikel orl_a ON O.OrderID = orl_a.OrderID"); END IF; 

    SET qry = CONCAT(qry, "WHERE (K.Naam Like '%" + p_Trefwoord + "%' 
                        OR K.Postcode Like '%" + p_Trefwoord + "%' 
                        OR K.Plaats Like '%" + p_Trefwoord + "%' 
                        OR O.OrderID Like '%" + p_Trefwoord + "%')"); 

    IF (p_Orderregel = "true") THEN SET qry = CONCAT(qry, "OR orl_a.Omschrijving LIKE '%" + p_Trefwoord + "%' OR orl_a.Artikelnr LIKE  '%" + p_Trefwoord + "%' OR orl_a.Magazijnlocatie LIKE  '%" + p_Trefwoord + "%'"); END IF; 

    SET qry = CONCAT(qry, " AND " + whereStr); 

    IF (p_AfdelingID <> 0) THEN SET qry = CONCAT(qry, " AND O.AfdelingID = " + p_AfdelingID); ELSE SET qry = CONCAT(qry, " AND O.AfdelingID <> '8'"); END IF; 

    IF (p_Orderregel = "true") THEN SET qry = CONCAT(qry, "GROUP BY o.OrderID"); END IF; 

    IF (p_OrderBy = "") THEN SET qry = CONCAT(qry, "ORDER BY O.Leverweekgewenst ASC"); ELSE SET qry = CONCAT(qry, "ORDER BY " + p_OrderBy); END IF; 

    SET qry = qry + "LIMIT 500"; 

END;


Maar als ik deze nou wil gaan uitvoeren geeft hij diverse foutmeldingen. Ik weet dat ik nog geen result zal terug krijgen, omdat ik de query alleen nog maar in een variabele zet, maar de foutmeldingen mogen er ook niet zijn.

Zo krijg ik bijvoorbeeld de volgende foutmelding (dit zijn ze niet allemaal, maar hopelijk kom ik er dan achter waar ik moet zoeken)

Truncated incorrect DOUBLE value: '%')'
Truncated incorrect DOUBLE value: '%'
OR O.OrderID Like '%'

Laat ik het hier eerst maar bij laten. Wie ziet er fouten in deze stored procedure, en heeft een paar tips voor mij?

Ook zou ik graag willen weten hoe ik de query dus uitvoer, dus ik heb de query nu in de variabele staan, maar hoe voer ik die dan uit.

Alvast bedankt,

donderdag 14 juni 2007 11:09

Acties:
  • 0 Henk 'm!
  • drm
  • Registratie: Februari 2001
  • Laatst online: 09-06 13:31

drm

f0pc0dert

't zit in de CONCAT() op r. 81:
SQL:
1

SET qry = CONCAT(qry, "WHERE (K.Naam Like '%" + p_Trefwoord + "%'

moet zijn
SQL:
1

SET qry = CONCAT(qry, "WHERE (K.Naam Like '%", p_Trefwoord, "%'
etc.

Overigens heb ik wel een beetje mijn twijfels over deze wijze van query opbouwen en hoe efficient die is, maar er is vast wel iemand met meer tijd om dat even uit te vogelen ;)

Music is the pleasure the human mind experiences from counting without being aware that it is counting
~ Gottfried Leibniz

donderdag 14 juni 2007 11:10

Acties:
  • 0 Henk 'm!
  • Equator
  • Registratie: April 2001
  • Laatst online: 09-09 15:29

Equator

Crew Council

#whisky #barista

In MSSQL is het:
SQL:
1

exec(query)

en ik verwacht dat dit in MySQL gelijk zal zijn :)

@drm: Mij is het gebruik van dergelijke dynamische SQL afgeraden, maar wat nu precies de reden was weet ik niet meer.

Maar alleen al voor de leesbaarheid/beheersbaarheid zou ik het in verschillende queries doen.

[ Voor 52% gewijzigd door Equator op 14-06-2007 11:13 ]

donderdag 14 juni 2007 11:12

Acties:
  • 0 Henk 'm!
  • Creepy
  • Registratie: Juni 2001
  • Laatst online: 15:14

Creepy

Tactical Espionage Splatterer

Overigens.. het is "gewoon" een select? Waarom zou dat in een SP sneller zijn dan m.b.v. een normale query? Naar mijn idee maakt dat in snelheid echt niks uit aangezien de query dynamisch wordt samengesteld.
Je gaat waarschijnlijk meer tijdwinst halen door het execute plan van je query eens te bekijken en daarmee te gaan optimaliseren.

[ Voor 37% gewijzigd door Creepy op 14-06-2007 11:13 ]

"I had a problem, I solved it with regular expressions. Now I have two problems". That's shows a lack of appreciation for regular expressions: "I know have _star_ problems" --Kevlin Henney

donderdag 14 juni 2007 11:18

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
@Creepy:

Kun je dat misschien iets verder uitleggen

donderdag 14 juni 2007 13:10

Acties:
  • 0 Henk 'm!

Verwijderd

Ik ben Creepy niet, maar misschien kan ik je
(een eindje opweg) helpen. Ik heb even gegoogled
op "MySQL" +"Execute Plan". Beetje weinig bruikbare resultaten, maar goed.
Eerste resultaat van MySQL.com verwijst naar een forumthread die over perfomance gaat.

Ze hebben het daar over Explain Extended. Een uitgebreidere vorm van Explain dus.
Misschien heb je daar iets aan.

donderdag 14 juni 2007 13:19

Acties:
  • 0 Henk 'm!
  • Janoz
  • Registratie: Oktober 2000
  • Laatst online: 02:21

Janoz

Moderator Devschuur®

!litemod

Owh, ik dacht dat ik hier al een berichtje geplaatst had, maar blijkbaar niet op submit gedrukt.

In het kort kwam het hier op neer:

Zonder regelnummers kunnen we weinig met de foutmelding, maar hier is al op gereageerd. Het andere deel van mijn reactie ging over dat dit je probleem niet op gaat lossen. Je query wordt niet efficienter doordat je hem in een stored procedure zet. Dit is ook al door Creepy aangestipt. Zeker ook omdat je je query string dynamisch opbouwt is de kans groter dat het hetzelfde of zelfs slechter wordt. Voor mysql valt er helemaal niks te optimaliseren want mysql kent de query pas op het moment dat je hem daadwerkelijk loslaat op de DB.

De traagheid van je query zit in de subqueries. Die worden voor elk record weer opnieuw uitgevoerd. Volgens mij moet dat, zeker in combinatie met een sum(if(boolean,0,1)) en een goede group by, wel zonder een subselect kunnen.

Kortom, steek je energie in het optimaliseren van die query en verdoe het niet met het aanmaken van deze SP.

Ken Thompson's famous line from V6 UNIX is equaly applicable to this post:
'You are not expected to understand this'

Pagina: 1
Reageer

Apple iPhone 16e LG OLED evo G5 Google Pixel 10 Samsung Galaxy S25 Star Wars: Outlaws Nintendo Switch 2 Apple AirPods Pro (2e generatie) Sony PlayStation 5 Pro

Tweakers is onderdeel van DPG Media B.V.
Alle rechten voorbehouden - Auteursrecht © 1998 - 2025 • Hosting door TrueFullstaq