Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

[MYSQL] SELECT traag met specifieke value

Pagina: 1
Acties:

Onderwerpen

Vraag


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 08-06 13:16
Oke, om te beginnen een klein beetje context bij de query: Bij het tonen van een lijst met nog-te-pakken-artikelen (looplijst in magazijn) wordt getoond of deze klant het product al eens heeft geretourneerd.
Na klachten van de klant over traagheid bij het laden van de looplijst en wat uitzoekwerk blijkt deze feature de boosdoener te zijn.

Wat ik heb is een SELECT met een subquery die het aantal ophaalt van de laatste retour van dit artikel:
MySQL:
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
        n.*,
    (
        SELECT ar.amount FROM clientorder co
        INNER JOIN clientorderitem coi ON coi.clientorder_id = co.clientorder_id
        INNER JOIN clientpackageitem cpi ON cpi.clientorderitem_id = coi.clientorderitem_id
        INNER JOIN articleretour ar ON ar.clientpackageitem_id = cpi.clientpackageitem_id
        WHERE co.account_id = n.account_id AND coi.artnr = n.artnr
        ORDER BY co.placed DESC LIMIT 1
    ) AS 'retourcount'
FROM needed_articles  n
WHERE n.account_id =  2812

Deze query duurt met het pakken van 100 artikelen al gauw 30 seconden.

Als ik:
code:
1
SELECT ar.amount FROM clientorder co
vervang door
code:
1
SELECT COUNT(*) FROM clientorder co
dan is hij super snel( < 0,05 sec)

De indexes staan volgens allemaal goed ingesteld (ik kan als dat nodig is de table structure posten).
Dus mijn vraag; hoe kan een select-value invloed hebben op de performance van de query en hoe los ik dat op?


De EXPLAIN van de query:
+----+--------------------+--------+-------+----------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------+------+--------------------------+
| id | select_type        | table  | type  | possible_keys                                                                                | key                                                | key_len | ref                        | rows | Extra                    |
+----+--------------------+--------+-------+----------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------+------+--------------------------+
| 1  | PRIMARY            | n      | ref   | account_id_clientorder_id_clientorderitem_id_artnr,fk-dm_nodig-account_id                    | account_id_clientorder_id_clientorderitem_id_artnr | 2       | const                      | 15   |                          |
| 2  | DEPENDENT SUBQUERY | co     | index | PRIMARY,fk-dm_clientorder-account_id,account_id_placed,account_id_clientordertype_id_placed  | ix-dm_clientorder-placed                           | 9       |                            | 1    | Using where              |
| 2  | DEPENDENT SUBQUERY | coi    | ref   | PRIMARY,un-dm_clientorderitem-clientorder_id-artnr                                           | un-dm_clientorderitem-clientorder_id-artnr         | 7       | co.clientorder_id, n.artnr | 1    | Using index              |
| 2  | DEPENDENT SUBQUERY | cpi    | ref   | PRIMARY,fk-dm_clientpackageitem-clientorderitem_id                                           | fk-dm_clientpackageitem-clientorderitem_id         | 4       | coi.clientorderitem_id     | 1    | Using index              |
| 2  | DEPENDENT SUBQUERY | ar     | ref   | clientpackageitem_id_articleretourreason_id,clientpackageitem_id                             | clientpackageitem_id                               | 9       | cpi.clientpackageitem_id   | 1    | Using where; Using index |
+----+--------------------+--------+-------+----------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------+------+--------------------------+

Alle reacties


  • P1nGu1n
  • Registratie: juni 2011
  • Laatst online: 11:55

P1nGu1n

Developer

Op amount staat geen index, dus hij moet voor ieder record de amount opzoeken en lezen.

In het geval van COUNT(*) telt hij het totaal aantal rows, daarvoor hoeft hij geen amounts uit te lezen.

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 08-06 13:16
Oke, duidelijk... dat kan ik begrijpen. Maar ik neem aan dat je dan geen losse index op `amount` bedoelt.

Voor de tabel `ar` gebruikt hij nu key `clientpackageitem_id `, dus ik heb nu een nieuwe key gemaakt `clientpackageitem_id-amount` met een index op kolom `clientpackageitem_id ` en `amount`.

Echter verandert dit niks in de performance. In de EXPLAIN zie ik wel dat hij nu deze key gebruikt voor deze tabel.

Zo ziet de tabel er nu uit:
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `articleretour` (
    `articleretour_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `clientorder_id` MEDIUMINT(8) UNSIGNED NOT NULL,
    `clientpackageitem_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `articleretourreason_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `amount` INT(10) UNSIGNED NOT NULL,
    `artnr` INT(10) UNSIGNED NULL DEFAULT NULL,
    `comment` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'Possible comment',
    `correction` TINYINT(4) NULL DEFAULT NULL,
    PRIMARY KEY (`articleretour_id`),
    INDEX `fk-articlerectour-artnr` (`artnr`),
    INDEX `clientpackageitem_id_amount` (`clientpackageitem_id`, `amount`),
    CONSTRAINT `fk-articlerectour-artnr` FOREIGN KEY (`artnr`) REFERENCES `article` (`artnr`) ON UPDATE CASCADE,
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

  • Ed Vertijsment
  • Registratie: juli 2014
  • Nu online
Welke database gebruik je? Ik meen mij te herinneren (correct me if I’m wrong) dat MySQL in subqueries en functies niet altijd indexes/caches gebruikt.

  • Kalentum
  • Registratie: juni 2004
  • Nu online
Wilde gok (want je query plain ziet er op zich prima uit): wat gebeurt er als je die ORDER BY weghaalt? Is 'ie dan wel snel? in dat geval zou je kunnen checken of die index wel goed is.

PVoutput


  • CurlyMo
  • Registratie: februari 2011
  • Nu online

CurlyMo

www.pilight.org

Ik vraag me af of je dit niet beter zonder subquery kan schrijven? De mogelijke dubbele rijen die een left join oplevert kan je ook wel weer filteren met een slimme distinct of group by.

Heb je een dummy database opzet voor ons?

[Voor 3% gewijzigd door CurlyMo op 06-10-2018 11:37]

geen vragen via PM die ook op het forum gesteld kunnen worden.


  • DJMaze
  • Registratie: juni 2002
  • Niet online
Wat als je hiermee eens gaat stoeien:
SQL:
1
2
3
4
5
6
7
8
9
SELECT
    n.*,
    SUM(ar.amount) AS retourcount
FROM needed_articles n
LEFT JOIN clientorder co ON (co.account_id = n.account_id)
LEFT JOIN clientorderitem coi ON (coi.clientorder_id = co.clientorder_id AND coi.artnr = n.artnr)
LEFT JOIN clientpackageitem cpi ON (cpi.clientorderitem_id = coi.clientorderitem_id)
LEFT JOIN articleretour ar ON (ar.clientpackageitem_id = cpi.clientpackageitem_id)
WHERE n.account_id = 2812

Maak je niet druk, dat doet de compressor maar


  • GlowMouse
  • Registratie: november 2002
  • Niet online

GlowMouse

wees solidair

De CONSTRAINT voegt ook een index toe, dus de index fk-articlerectour-artnr is dubbel.

Heb je met de nieuwe index de EXPLAIN output van de normale query en die met COUNT(*)?

En kun je statistieken van de query posten, zoals het aantal gelezen rijen?

geeft geen inhoudelijke reacties meer


Acties:
  • +1Henk 'm!

  • ACM
  • Registratie: januari 2000
  • Niet online

ACM

Software Architect

Werkt hier

Ed Vertijsment schreef op vrijdag 5 oktober 2018 @ 23:38:
Welke database gebruik je? Ik meen mij te herinneren (correct me if I’m wrong) dat MySQL in subqueries en functies niet altijd indexes/caches gebruikt.
Dat is uit de tijd van mysql 4 en misschien vroege 5-versies. Het kan ondertussen wel indexen gebruiken in subqueries, mits die er natuurlijk zijn.

Wat hier echter niet helpt is dat er een veld wordt gepakt via 3 joins, wat weer gesorteerd wordt via een kolom van de eerste van de tabellen.

In dit geval lijkt me een index op clientorder(account_id, placed) me zinvol, want dat is uiteindelijk degene die gesorteerd moet worden. Ik kan hier niet zien of 'ix-dm_clientorder-placed' die twee kolommen combineert, of alleen placed als index-veld heeft. In dat laatste geval moet ie steeds alle records sorteren en dan filteren in de hoop dat ie het betreffende record vindt.

Je lijkt trouwens al de benodigde index te hebben 'account_id_placed', het kan dan helpen om die met USE INDEX te forceren om te testen of je query dan wel sneller is. Met een 'analyze table clientorder' kan je verse statistieken genereren, waarme die index hopelijk automatisch pakt.

Het kan nuttig zijn de subquery los te optimaliseren, hoewel dat natuurlijk geen garanties geeft voor de complete query.
RickyHeijnen schreef op vrijdag 5 oktober 2018 @ 13:59:
Dus mijn vraag; hoe kan een select-value invloed hebben op de performance van de query en hoe los ik dat op?
Gebruikt die count-variant dezelfde index op clientorder?

Saai uitzicht in je tuin? Hang er een foto voor!


  • Olaf van der Spek
  • Registratie: september 2000
  • Niet online
RickyHeijnen schreef op vrijdag 5 oktober 2018 @ 13:59:
Deze query duurt met het pakken van 100 artikelen al gauw 30 seconden.
De subquery wordt bij een result van 100 rows 100x uitgevoerd.. waarom dat 30s duurt durf ik echter niet te zeggen.

Je zou eerst "SELECT * FROM needed_articles WHERE account_id = 2812" kunnen doen en daarna 1x de subquery. Of knutselen met een join in plaats van een dependent subquery.

En inderdaad, welke database gebruik je?
RickyHeijnen schreef op vrijdag 5 oktober 2018 @ 13:59:
Bij het tonen van een lijst met nog-te-pakken-artikelen (looplijst in magazijn) wordt getoond of deze klant het product al eens heeft geretourneerd.
Zou je dan niet alle clientorders moeten checken in plaats van alleen de laatste?
Is het überhaupt relevant of de klant het al eens geretourneerd heeft? Krijgt ie dan z'n eigen retour terug of een B-produkt? :p

[Voor 40% gewijzigd door Olaf van der Spek op 08-10-2018 14:16]


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 08-06 13:16
MySQL 5.5.60
CurlyMo schreef op zaterdag 6 oktober 2018 @ 11:34:
Ik vraag me af of je dit niet beter zonder subquery kan schrijven? De mogelijke dubbele rijen die een left join oplevert kan je ook wel weer filteren met een slimme distinct of group by.
DJMaze schreef op zaterdag 6 oktober 2018 @ 12:53:
Wat als je hiermee eens gaat stoeien:
SQL:
1
2
3
4
5
6
7
8
9
SELECT
    n.*,
    SUM(ar.amount) AS retourcount
FROM needed_articles n
LEFT JOIN clientorder co ON (co.account_id = n.account_id)
LEFT JOIN clientorderitem coi ON (coi.clientorder_id = co.clientorder_id AND coi.artnr = n.artnr)
LEFT JOIN clientpackageitem cpi ON (cpi.clientorderitem_id = coi.clientorderitem_id)
LEFT JOIN articleretour ar ON (ar.clientpackageitem_id = cpi.clientpackageitem_id)
WHERE n.account_id = 2812
Dat zou erg complex worden aangezien ik een vereenvoudigde versie van de query gepost. De SELECT zelf is 141 regels lang met veel JOIN's (o.a. op vooraad, artikelinfo, leverancier etc.).
Om het aantal retouren te berekenen leek mij een subquery de handigste manier.
Olaf van der Spek schreef op maandag 8 oktober 2018 @ 14:03:Zou je dan niet alle clientorders moeten checken in plaats van alleen de laatste?
Is het überhaupt relevant of de klant het al eens geretourneerd heeft? Krijgt ie dan z'n eigen retour terug of een B-produkt? :p
De klant wilt graag het aantal dat de laatste keer retour is gekomen zien. Ze gaan dan hun klant bellen om te vragen of ze zeker weten dat ze dat artikel toch weer willen ontvangen. Meestal is het namelijk gewoon een vergissing en zo voorkomen ze dat ze het artikel 2 dagen later weer moeten gaan ophalen.
rutgerw schreef op zaterdag 6 oktober 2018 @ 11:26:
Wilde gok (want je query plain ziet er op zich prima uit): wat gebeurt er als je die ORDER BY weghaalt? Is 'ie dan wel snel? in dat geval zou je kunnen checken of die index wel goed is.
ACM schreef op zondag 7 oktober 2018 @ 09:43:
Wat hier echter niet helpt is dat er een veld wordt gepakt via 3 joins, wat weer gesorteerd wordt via een kolom van de eerste van de tabellen.

In dit geval lijkt me een index op clientorder(account_id, placed) me zinvol, want dat is uiteindelijk degene die gesorteerd moet worden. Ik kan hier niet zien of 'ix-dm_clientorder-placed' die twee kolommen combineert, of alleen placed als index-veld heeft. In dat laatste geval moet ie steeds alle records sorteren en dan filteren in de hoop dat ie het betreffende record vindt.[/query]
Maar de ORDER BY lijkt geen effect te hebben op de query, want die zit ook in mijn voorbeeld met COUNT(*) als SELECT

Je lijkt trouwens al de benodigde index te hebben 'account_id_placed', het kan dan helpen om die met USE INDEX te forceren om te testen of je query dan wel sneller is. Met een 'analyze table clientorder' kan je verse statistieken genereren, waarme die index hopelijk automatisch pakt.

Het kan nuttig zijn de subquery los te optimaliseren, hoewel dat natuurlijk geen garanties geeft voor de complete query.
Duidelijk, zonder de ORDER BY issie wel snel. Natuurlijk skipt hij de ORDER BY als ik een COUNT(*) als SELECT gebruik. Daarom werd ik op het verkeerde been gezet 8)7 Thanks voor dit aanknopingspunt _/-\o_ Ik ga nog even stoeien met de indexes op de clientorder tabel!!

  • Olaf van der Spek
  • Registratie: september 2000
  • Niet online
Waarom zo'n oude versie? Zelfs 5.6 is al meer dan 5 jaar beschikbaar.

  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 08-06 13:16
Olaf van der Spek schreef op dinsdag 9 oktober 2018 @ 11:47:
Waarom zo'n oude versie? Zelfs 5.6 is al meer dan 5 jaar beschikbaar.
Heb ruim een half jaar terug geprobeerd over te stappen van 5.5 naar MariaDB, maar dat gaf zoveel performance issues dat ik snel weer terug ben gegaan naar MySQL en toen 5.6 heb geprobeerd, maar ook dat kwam de performance niet ten goede. Mijn kennis over de (fine)tuning van MySQL is niet super, dus heb besloten toen om weer terug te zakken naar 5.5 dat altijd goed gedraaid heeft, en dat doet het nog steeds.

  • Olaf van der Spek
  • Registratie: september 2000
  • Niet online
Debian? ;)
MySQL 8 of 5.7 ook geprobeerd? Of MariaDB 10.3? Ik zou toch eens uit proberen te zoeken wat de oorzaak van die performance issues is. Meestal is de performance van nieuwere versies beter.

[Voor 21% gewijzigd door Olaf van der Spek op 09-10-2018 12:10]


  • DJMaze
  • Registratie: juni 2002
  • Niet online
RickyHeijnen schreef op dinsdag 9 oktober 2018 @ 11:43:
Dat zou erg complex worden aangezien ik een vereenvoudigde versie van de query gepost. De SELECT zelf is 141 regels lang met veel JOIN's (o.a. op vooraad, artikelinfo, leverancier etc.).
Om het aantal retouren te berekenen leek mij een subquery de handigste manier.
Dan moet je z.s.m. omdenken. Je moet het niet in 1 query willen proppen!
Kijk wat je als resultaat wil en splits het op.
Je zal merken dat MySQL niet 1000'en records in een temp table/geheugen gooit voor 1 regeltje aan resultaat.

Maak je niet druk, dat doet de compressor maar


  • CurlyMo
  • Registratie: februari 2011
  • Nu online

CurlyMo

www.pilight.org

RickyHeijnen schreef op dinsdag 9 oktober 2018 @ 11:43:
[...]
Dat zou erg complex worden aangezien ik een vereenvoudigde versie van de query gepost. De SELECT zelf is 141 regels lang met veel JOIN's (o.a. op vooraad, artikelinfo, leverancier etc.).
Om het aantal retouren te berekenen leek mij een subquery de handigste manier.
Dat snap ik, maar je opent niet voor niks een topic. De handigste manier is dus niet de snelste manier. De hypothese is dat een 141 regels lange join zonder subquery's een stuk sneller is. Aan jou of handig te prefereren is over snelheid.

Kan je je model eens plaatsen, want het klinkt me vrij complex in de oren als je voor dit gegeven zoveel joins moet leggen? De valkuil is dat je in de fase van het ophalen van de laatste order teveel informatie wil weten. Bijvoorbeeld een join op de klant tabel, een join op de artikel beschrijvingstabel enz. Terwijl je voor de laatste order die gegevens (nog) niet nodig hebt.

Een simpel voorbeeld.
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
    *
FROM
    klant AS a
INNER JOIN
    orders AS b
ON
    a.klant_id = b.klant_id
INNER JOIN
    artikelen AS c
ON
    b.artikel_id = c.artikel_id
INNER JOIN
    artikel_beschrijvingen AS d
ON
    d.artikel_id = c.artikel_id
INNER JOIN
    artikel_voorraad AS e
ON
    d.artikel_id = e.artikel_id

Terwijl je voor je vraag alleen de eerste twee tabellen nodig hebt. Het koppelen van bijv. je artikel informatie kan je doen nadat je weet welke artikelen daadwerkelijk relevant zijn. Desnoods maak je er twee queries van.
Meestal is het namelijk gewoon een vergissing en zo voorkomen ze dat ze het artikel 2 dagen later weer moeten gaan ophalen.
Wat ook helpt is een temporele filter toepassen. Je selecteer nu alle historie, maar waarom niet alleen de bestellingen van de laatste maand of de laatste 10 bestellingen. Of de laatste order?

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
SELECT
    n.*,
    SUM(ar.amount) AS retourcount
FROM
    needed_articles n
LEFT JOIN
    (
        SELECT
            MAX(clientorder_id)
            account_id
        FROM
            clientorder
        GROUP BY
            account_id
    ) as co
ON
    (co.account_id = n.account_id)
LEFT JOIN
    clientorderitem coi
ON
    (coi.clientorder_id = co.clientorder_id AND coi.artnr = n.artnr)
LEFT JOIN
    clientpackageitem cpi
ON
    (cpi.clientorderitem_id = coi.clientorderitem_id)
LEFT JOIN
    articleretour ar
ON
    (ar.clientpackageitem_id = cpi.clientpackageitem_id)
WHERE n.account_id = 2812


Als je de query vanuit een programmeertaal vult, dan is dit nog beter:
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
SELECT
    n.*,
    SUM(ar.amount) AS retourcount
FROM
    needed_articles n
LEFT JOIN
    (
        SELECT
            clientorder_id
            account_id
        FROM
            clientorder
        WHERE
            account_id = 2812
        ORDER BY
            placed DESC
        LIMIT 1
    ) as co
ON
    (co.account_id = n.account_id)
LEFT JOIN
    clientorderitem coi
ON
    (coi.clientorder_id = co.clientorder_id AND coi.artnr = n.artnr)
LEFT JOIN
    clientpackageitem cpi
ON
    (cpi.clientorderitem_id = coi.clientorderitem_id)
LEFT JOIN
    articleretour ar
ON
    (ar.clientpackageitem_id = cpi.clientpackageitem_id)
WHERE n.account_id = 2812

Zonder het model te kennen zijn er maar simpele suggesties over hoe het anders kan :)

geen vragen via PM die ook op het forum gesteld kunnen worden.


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 08-06 13:16
DJMaze schreef op dinsdag 9 oktober 2018 @ 12:38:
Dan moet je z.s.m. omdenken. Je moet het niet in 1 query willen proppen!
Kijk wat je als resultaat wil en splits het op.
Je zal merken dat MySQL niet 1000'en records in een temp table/geheugen gooit voor 1 regeltje aan resultaat.
Dat begrijp ik niet. Wat maakt de lengte van de query uit als de performance van de query verder goed is? Ik werk liever met een goed gebouwde query die duizenden records doorgaat en direct alle informatie in een resultset heb dan dat ik 10x een 'SELECT * FROM x WHERE id=1234' moet gebruiken om alle informatie bij elkaar te krijgen.

Dit is de gehele query even voor de helderheid (het is een stored procedure):
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
BEGIN

    SELECT
        a.artnr,
        a.artikel,
        a.aantal1,
        a.aantal2,
        a.klachten_memo,
        a.datum_uit_productie,
        (
            SELECT ps.productsoort FROM algemeen.art_productsoort ps WHERE ps.productsoort_id = a.alternatieve_productsoort_id
        ) AS 'alternatieve_productsoort',
        e.eenheid,
        a.rvg_nummer,
        IF(alt.promote,aa.alt_artnr,null) AS 'alt_artnr',
        IF(alt.promote,aa.alt_artikel,null) AS 'alt_artikel',
        IF(alt.promote,aa.alt_eenheid,null) AS 'alt_eenheid',
        IF(alt.promote,aa.alt_aantal1,null) AS 'alt_aantal1',
        IF(alt.promote,aa.alt_aantal2,null) AS 'alt_aantal2',
        IF(alt.promote,aa.alt_aantal2,null) AS 'alt_aantal2',
        alt.promote,
        a.artnr_fabrikant,
        b.bedrijfsnaam AS 'fabrikant',
        co.clientorder_id,
        n.clientorderitem_id,
        co.reference,
        co.comment,
        co.internalcomment,
        co.deliverdate,
          co.clientordertype_id,
        n.nodig,
        oir.orderitemreceived_id,
        oir.price,
        oir.serialnr,
        oir.expiration,
        oir.stockplace_id,
        v.voorraad,
        (
            SELECT SUM(v2.voorraad)
            FROM voorraad v2
            WHERE v2.artnr = a.artnr
        ) AS 'voorraadtotal',
        (
            SELECT p.price
            FROM price p
            INNER JOIN pricelist pl ON p.pricelist_id = pl.pricelist_id
            WHERE pl.bedrijf_id = 625
            AND pl.startdate < NOW() AND IFNULL(pl.enddate,NOW()) >= NOW()
            AND p.artnr = a.artnr
            AND pl.pricelist LIKE '%LAAG'
            ORDER BY pl.enddate DESC, p.price ASC
            LIMIT 1
        ) AS 'cPrice',
        (
            SELECT p.stock
            FROM price p
            INNER JOIN pricelist pl ON p.pricelist_id = pl.pricelist_id
            WHERE pl.bedrijf_id = 625
            AND pl.startdate < NOW()
            AND p.artnr = a.artnr AND pl.pricelist LIKE '%LAAG%'
            ORDER BY pl.enddate DESC, p.price ASC
            LIMIT 1
        ) AS 'cStock',
        (
            SELECT p.price
            FROM price p
            INNER JOIN pricelist pl
                ON p.pricelist_id = pl.pricelist_id
            WHERE pl.bedrijf_id = 10429
            AND pl.startdate < NOW()
            AND p.artnr = a.artnr
            ORDER BY pl.enddate DESC, p.price ASC
            LIMIT 1
        ) AS 'pPrice',
        (
            SELECT ca.code
            FROM diw.diw_catalogusartikel ca
            INNER JOIN diw.diw_catalogus c ON c.catalogus_id = ca.catalogus_id
            WHERE c.catalogus_id = 215
                AND ca.artnr = a.artnr
                AND ca.code IS NOT NULL
            ORDER BY c.einddatum DESC
            LIMIT 1
        ) AS 'pCode',
        CASE WHEN coi.pcs_id IS NULL THEN coi.saleprice ELSE coi.pcs_price END AS 'saleprice',
        coi.comment AS 'coiComment',
        coi.expiration as 'artExpration',
        avm.minimum,
        IF(orr.bedrijf_id IS NULL,0,orr.orderrestriction) AS 'orderrestriction',
        cbo.amount AS 'amount_backorder',
        (
            SELECT expected FROM orderitem oi
            
            WHERE oi.artnr = a.artnr AND expected IS NOT NULL
            ORDER BY expected ASC
            LIMIT 1
        ) AS 'expected',
        (
            SELECT SUM(oi.amount)-(
                SELECT SUM(oir.amount) FROM orderitemreceived oir
                inner join orderitem oi2 on oi2.orderitem_id = oir.orderitem_id
                WHERE oi2.artnr = oi.artnr
            ) FROM orderitem oi
            
            WHERE oi.artnr = a.artnr
        ) AS 'expected_amount',
        aca.blocked AS 'accountalternative',
        (
            SELECT SUM(ar.amount) FROM clientorderitem coi
            INNER JOIN clientorder co ON coi.clientorder_id = co.clientorder_id
            INNER JOIN clientpackageitem cpi ON cpi.clientorderitem_id = coi.clientorderitem_id
            INNER JOIN articleretour ar ON ar.clientpackageitem_id = cpi.clientpackageitem_id
            WHERE co.account_id = n.account_id AND coi.artnr = a.artnr AND ar.articleretourreason_id IN (1,3)
        ) AS 'retourcount',
        s.stockplace,
        spt.name
    FROM nodig n
    INNER JOIN clientorderitem coi
        ON n.clientorderitem_id = coi.clientorderitem_id
    INNER JOIN clientorder co
        ON coi.clientorder_id = co.clientorder_id
    INNER JOIN algemeen.art_artikel a ON n.artnr = a.artnr
    INNER JOIN algemeen.art_eenheid e ON a.eenheid_id = e.eenheid_id
    INNER JOIN algemeen.bdr_bedrijf b ON a.bedrijf_id = b.bedrijf_id
    LEFT OUTER JOIN alternative alt ON alt.artnr = a.artnr
    LEFT OUTER JOIN vw_articles aa ON aa.artnr = a.artnr
    LEFT OUTER JOIN accountalternative aca ON aca.account_id = co.account_id AND aca.artnr = aa.alt_artnr
    LEFT OUTER JOIN voorraad v ON a.artnr = v.artnr
    LEFT OUTER JOIN orderitemreceived oir
        ON v.orderitemreceived_id = oir.orderitemreceived_id AND IFNULL(oir.stockplace_id,0) != 28617
    LEFT OUTER JOIN stockplace s ON oir.stockplace_id = s.stockplace_id
    LEFT OUTER JOIN stockplacetype spt ON spt.stockplacetype_id = s.stockplacetype_id
    LEFT OUTER JOIN articlevoorraadmin avm ON avm.artnr = a.artnr
    LEFT OUTER JOIN orderrestriction orr ON orr.bedrijf_id = a.bedrijf_id
    LEFT OUTER JOIN clientbackorder cbo ON cbo.clientorderitem_id = coi.clientorderitem_id
    LEFT OUTER JOIN articleexpected ae ON ae.artnr = a.artnr
    WHERE n.account_id = in_account_id
    ORDER BY co.clientorder_id, IFNULL(co.deliverdate, '2000-01-01'), b.bedrijfsnaam, a.artikel,
     ISNULL(oir.expiration) DESC, oir.expiration, -- on expiry
     oir.stockplace_id is null asc,
    spt.rang,
     ISNULL(oir.serialnr),
     oir.serialnr,
     s.stockplace,
     oir.amount,
     oir.price;

END

  • CurlyMo
  • Registratie: februari 2011
  • Nu online

CurlyMo

www.pilight.org

RickyHeijnen schreef op dinsdag 9 oktober 2018 @ 13:00:
[...]

Dat begrijp ik niet. Wat maakt de lengte van de query uit als de performance van de query verder goed is?
Dat losse queries in gevallen zoals je hier laat zien vaak beter presteren dan één mega query.
Ik werk liever met een goed gebouwde query die duizenden records doorgaat en direct alle informatie in een resultset heb dan dat ik 10x een 'SELECT * FROM x WHERE id=1234' moet gebruiken om alle informatie bij elkaar te krijgen.
Het functionele doel is toch belangrijker dan de gebruikte techniek?
Dit is de gehele query even voor de helderheid (het is een stored procedure):
Je zit in een SP. Waarom zou je dit dan doen? Juist in een SP heb je de mogelijkheid om met veel kleinere subsets van data te werken middels kleinere queries. Die subsets kunnen dan tenminste in het geheugen gehouden worden.

Dat niet alleen, een query als dit is totaal niet vriendelijk voor collega's om later te bugfixes, terwijl een mooie SP If / Else structuur met commentaar wel een collegiaal stukje programmatuur oplevert.

Helemaal met OUTER JOINs wordt het een draak qua prestaties, want je tijdelijke tabel is sowieso* alternative * vw_articles * accountalternative * voorraad * orderitemreceived * stockplace * stockplacetype * articlevoorraadmin * orderrestriction * clientbackorder * articleexpected aantal rijen. Daar haal je vervolgens maar een X aantal rijen uit.

*Mits de query planner dit niet tot een beetje zinnig plan ombouwt.

Om dit te laten presteren heb je een paar snelle PCIe SSD's nodig :p

Einde van mijn asynchrone argumentatie :+

geen vragen via PM die ook op het forum gesteld kunnen worden.

Pagina: 1


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True