[MYSQL] SELECT traag met specifieke value

[RickyHeijnen]

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:

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:

SELECT ar.amount FROM clientorder co

vervang door

code:

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 |
+----+--------------------+--------+-------+----------------------------------------------------------------------------------------------+----------------------------------------------------+---------+----------------------------+------+--------------------------+

[P1nGu1n]

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.

[RickyHeijnen]

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:

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]

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]

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.

[CurlyMo]

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]

[DJMaze]

Wat als je hiermee eens gaat stoeien:

SQL:

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

[GlowMouse]

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?

[ACM]

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?

[Olaf van der Spek]

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?

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

[RickyHeijnen]

Ed Vertijsment schreef op vrijdag 5 oktober 2018 @ 23:38:
Welke database gebruik je?

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:

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?

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 Thanks voor dit aanknopingspunt Ik ga nog even stoeien met de indexes op de clientorder tabel!!

[Olaf van der Spek]

RickyHeijnen schreef op dinsdag 9 oktober 2018 @ 11:43:
MySQL 5.5.60

Waarom zo'n oude versie? Zelfs 5.6 is al meer dan 5 jaar beschikbaar.

[RickyHeijnen]

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]

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]

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.

[CurlyMo]

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:

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:

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:

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

[RickyHeijnen]

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:

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]

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

Einde van mijn asynchrone argumentatie