DB performance: remove distinct statements in subqueries

[Michiel-s]

Achtergrond
Zoals de laatste tijd wel vaker loop ik tegen performance problemen van de database aan, die voortkomen uit de manier waarop de queries zijn opgebouwd. Voor kleine populaties spelen dergelijke issues niet omdat de boete in termen van tijd enkele miliseconden betreft. Wanneer de populaties echter toenemen (in mijn geval > 10k records voor bepaalde tabellen), dan loopt de boete voor niet geoptimaliseerde queries snel op (van enkele seconden tot minuten).

Gerelateerde issues
#454 removing subquery in left join
#426 remove outer subquery which is added by backend
#217 reduce number of queries for getting interface data

Dit issue
Dit issue verzoekt het verwijderen van het distinct statement dat aan alle (sub)queries standaard wordt toegevoegd.

Analyse
Het distinct statement wordt toegevoegd omdat we in Ampersand unique paren in verzamelingen willen hebben. Distinct zorgt daarvoor. Echter, bij subqueries en joins, welke we nogal vaak nodig hebben, zorgt het distinct statement op een subquery ervoor dat er een derived tabel nodig is voor het verwijderen van de dubbelingen in de tussenresultaten. Dit heeft een significante impact op de performance. Waar het ons echter om gaat is dat het eindresultaat geen dubbelingen bevat. Aan de andere kant voorkomt het distinct stament een explosie van tussenresultaten in subqueries. Dit is weer goed voor de performance. Dit voordeel weegt in generieke zin echter niet op tegen het nadeel dat de database subqueries niet kan optimaliseren.

Vereiste actie

• verwijder alle distinct statement voor queries incl. subqueries
• voeg distinct to op alle buitenste queries

LET op. Voor interface queries met '_SRCATOM' placeholder -> distinct op buitenste query laten staan!!

Een voorbeeld ter illustratie van performance winst (factor 100!!)
Gegeven deze rule uit een script: subElement |- propertyDomain~ \/ subClassOf;subElement
Wordt onderstaande sql query gegenereerd voor het verkrijgen van de violations:
Merk op dat de query bezaaid is met distinct statements

select distinct t1.src as src, t1.tgt as tgt 
from 
    (   select distinct t1.src as src, t1.tgt as tgt 
        from 
            (   select distinct "Class" as src, "Property" as tgt 
                from "subElement" 
                where ("Class" is not null) 
                and ("Property" is not null)
            ) as t1 
        left join 
            (   /* Flipped: EDcD propertyDomain[Property*Class] */ 
                select distinct "propertyDomain" as src, "Property" as tgt 
                from "Property" 
                where ("Property" is not null) 
                and ("propertyDomain" is not null)
            ) as t2 
        on (t1.src = t2.src) and (t1.tgt = t2.tgt) 
        where (t2.src is null) or (t2.tgt is null)
    ) as t1 
left join 
    (   select distinct fence0.src as src, fence1.tgt as tgt 
        from 
            (   select distinct "SrcClass" as src, "TgtClass" as tgt 
                from "subClassOf" 
                where ("SrcClass" is not null) and ("TgtClass" is not null)
            ) as fence0, 
            (   select distinct "Class" as src, "Property" as tgt 
                from "subElement" 
                where ("Class" is not null) and ("Property" is not null)
            ) as fence1 
        where (fence0.tgt = fence1.src)
    ) as t2 
on (t1.src = t2.src) and (t1.tgt = t2.tgt) 
where (t2.src is null) or (t2.tgt is null)

Gegeven de volgende populatie in de database:
subElement: 10.046 paren
propertyDomain: 6.171 paren
subClassOf: 910 paren

Is dit wat de database uitvoerd (uitvoering op mijn machine duurt +/- 20 sec):

Verwijderen we alle distinct statements uit de subqueries, dan duurt het slechts 0,2 sec. Analyse van de query geeft ook een veel geruststellender resultaat:

[Michiel-s]

@hanjoosten I've tried to implement this myself, but this one is too hard for me (yet) :)

[stefjoosten]

@Michiel-s,
I did an attempt in SHA 40accc7. Could you please test it?

[Michiel-s]

For conjuncts queries b330310 looks good (separate branch, not commit of @stefjoosten).

For our information, I've noticed that for queries with a UNION statement the DISTINCT is at the UNION statement (which is also the default of the database). Therefore the DISTINCT statement is not necessary on both outer SELECTS statements. See example below.. this is an ok implementation!

select t1.src as src, t1.tgt as tgt 
from    (   blabla
        ) as t1 
union distinct 
select t1.src as src, t1.tgt as tgt 
from    (   blabla
        ) as t1

[Michiel-s]

I've reverted the addition of ALL statements b330310. This is the default of the database and not necessary.

"The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT" see mysql doc

[Michiel-s]

@hanjoosten please let me know when you have time to look into detail into this issue. I can show you the thing in interfaces.json that are not yet consistent.

[stefjoosten]

@hanjoosten and @Michiel-s
can you decide whether this issue can be closed?

[Michiel-s]

Not all actions (see initial comment) are done or checked. I'll look into that.

[Michiel-s]

Tested the solution and it is not yet fixed properly. There are still interface queries generated where no DISTINCT statement is added to the outer query. This causes duplicated.

@hanjoosten let's discuss this asap via Skype.

[hanjoosten]

@Michiel-s We didn't discuss this issue via Skype, but I looked into it myself and found an obvious cause. As far as I can see, this should solve the problem. I've made a pull request. Could you verify that the problem is solved? (and then pull the branch?)

[Michiel-s]

I'll look at this PR Thursday morning

[RieksJ]

This issue was referenced by @hanjoosten in commit AmpersandTarski/ampersand-models@f5f266d (related to ticket #599)

[Michiel-s]

I've merged PR #611. Priority can be lowered now, but there is still something not consistent yet regarding the distinct statements. I would like to have a online work session with @hanjoosten to look into this later. For now please leave this issue open.

[RieksJ]

It would be nice if we could get the XPaths demo working again. How are things coming along?

[stefjoosten]

@Michiel-s can this issue be closed already?