[SQL] Optimaliseren van query (subquery en IN() )

[radem205]

Beste allen,

Voor een website, heb ik onderstaande query om een totaal te krijgen van de kolomwaarde 'area', gebaseerd op een specifieke code.

Voorbeeld inhoud tabel 'bc_parameter_difference':

sequence_id	object_id	code	parameter_name	parameter_value
1	23	200	area	10
2	23	200	area	5
3	23	201	area	15
1	25	200	area	10
2	25	201	area	5
3	25	200	area	15

Een scenario om e.e.a. te verduidelijken:

Stel ik wil per 'object_id' een totaal van 'area' voor de code '201':
Er zal dan gezocht moeten worden naar alle objecten waarvan de laatst ingevoerde code (hoogste sequence_id) '201' is. In de voorbeeldtabel hierboven zal dus alleen object_id = 23 geselecteerd moeten worden (immers is 200 de laatste code van object_id 25.
Vervolgens wil ik van object_id = 23 het totaal van de waarde 'Area' hebben.

Na veel proberen, heb ik dit bereikt met de volgende query.

code:

SELECT
        d0.object_id,
        SUM(d0.parameter_value) AS values_sum,
    FROM
        bc_parameter_difference d0
    WHERE
        d0.object_id IN 
        (
        SELECT 
            d1.object_id
        FROM 
            bc_parameter_difference d1
        WHERE (
        SELECT COUNT(*) FROM bc_parameter_difference d2
        WHERE d2.object_id = d1.object_id AND d1.sequence_id <= d2.sequence_id
        ) <= 1 
        AND 
            d1.project_id = 1
        AND
            d1.code = 201

        ORDER BY
            d1.object_id ASC,
            d1.sequence_id ASC
        ) 
        AND
            d0.parameter_name = 'area'
    GROUP BY
        d0.object_id

Echter, het aantal records kan snel gaan oplopen (tot 100.000+), waardoor ik het idee heb dat de query erg traag gaat worden (met name door het gebruik van een subquery en IN() ).

Hebben jullie tips voor het optimaliseren van de query om de gewenste output snel te kunnen opvragen? Of kan ik de query beter opdelen?

Alvast bedankt voor jullie tips.

[Juup]

Is de tabelstructuur vast of kun je die veranderen?

[radem205]

De tabelstructuur is helaas vast. Wat had je graag veranderd willen zien?

[Laurens-R]

Je geeft al aan dat je een vermoeden hebt. Echter; meten is weten en 100.000+ records is niet bijzonder veel voor een modern dbms. Misschien is het handiger om de query uit te voeren op een representatieve test dataset en dan doormiddel van sql profilers en het analyseren van je execution plan de pijnpunten identificeren. Dan kan je ook gericht oplossen

[cariolive23]

Waarom een ORDER BY in de subquery? Het resultaat wordt er niet anders van.

Ps. Welk merk database gebruik je?

[Jurgle]

Nou ben ik niet supersupersuper into query paths, maar volgens mij maak je met IN() op een subquery geen gebruik van een INDEX en wordt het snel langzaam. Probeer het eens met een JOIN. Werkt dit?

Note1: Leg een INDEX op minstens object_id (als eerste)
Note2: In je query komt een project_id voor, die heb ik hier voor het gemak weggelaten.
Note3: Wat cariolive23 zegt: een ORDER BY in je subquery is nutteloos vertragend

SQL:

# Get all object_ids and their sum(area), see WHERE clause

SELECT a.`object_id`, sum(a.`parameter_value`) as `sum_parameter_value`
FROM `bc_parameter_difference` a
INNER JOIN (

    # Get the object_ids to calculate the sum(area) for (only object_ids are selected which have code = 201 in their highest sequence, see ON clause)

    SELECT c.`object_id`
    FROM `bc_parameter_difference` c
    INNER JOIN (

        # Get the max sequence_id for each object_id

        SELECT `object_id`, max(`sequence_id`) as `max_sequence_id`
        FROM `bc_parameter_difference`
        GROUP BY `object_id`
    ) d ON d.`object_id` = c.`object_id` AND d.`max_sequence_id` = c.`sequence_id` AND c.`code` = 201
) b ON b.`object_id` = a.`object_id`
WHERE a.`parameter_name` = 'area'
GROUP BY a.`object_id`

En een vraagje, wat doet dit stukje?

SQL:

SELECT 
            d1.object_id
        FROM 
            bc_parameter_difference d1
        WHERE (
        SELECT COUNT(*) FROM bc_parameter_difference d2
        WHERE d2.object_id = d1.object_id AND d1.sequence_id <= d2.sequence_id
        ) <= 1 

[Douweegbertje]

radem205 schreef op donderdag 19 maart 2015 @ 13:26:
De tabelstructuur is helaas vast. Wat had je graag veranderd willen zien?

Logica die wel klopt.

In feite zeg je dit

Stel ik wil per 'object_id' een totaal van 'area' voor decode '2011':
Er zal dan gezocht moeten worden naar alle objecten waarvan de laatst ingevoerde code (hoogste sequence_id) '201' is

In de voorbeeldtabel hierboven zal dusalleen object_id = 23 geselecteerd moeten wordenn (immers is 200 de laatste code van object_id 25.

Jij wilt iets, wat niet logisch is en waar je huidige database niet direct voor gemaakt is.

Je eerste zin is goed, je wilt per object_id een totaal van de area waard de code 201 is. Prima.
Je verzint dan alleen je eigen logica door daar weer een 'nieuw' ID van te verzinnen om alleen daar weer iets vandaan te halen.

Vervolgens wil ik van object_id = 23 het totaal van de waarde 'Area' hebben.

Waarom?? Je zegt net zelf en ik quote weer

Stel ik wil per 'object_id' een totaal van 'area' voor de code '201

Verder niet veel gekeken nog naar de query zelf (sorry, niet veel tijd zet het anders in een sql-fiddle?), maar volgens mij kan dat veel efficienter en kan de sql van Jurgle ook met wat minder code. Ik zie niet in waarom er 2 joins nodig zijn, als je domweg maar één keer dat hoeft te doen om het 'nieuwe where id' te vinden op basis van hoogste seq. id.

Lees stof trouwens:

http://explainextended.com/2009/06/16/in-vs-join-vs-exists/