Inverteren van SQL query zonder correlated subquery

[egonolieux]

Ik heb een query die ik graag zou willen inverteren. Hiermee bedoel ik dat ik de resultaten wil vinden die niet overeenkomen met de conditie (i.p.v. wel).

Dit is de query:

code:

SELECT
    card.id
FROM
    card
    INNER JOIN card_effect card_effect1 ON card.id = card_effect1.card_id
    INNER JOIN card_effect_type card_effect_type1 ON card_effect1.type_id = card_effect_type1.id
    INNER JOIN card_effect card_effect2 ON card.id = card_effect2.card_id
    INNER JOIN card_effect_type card_effect_type2 ON card_effect2.type_id = card_effect_type2.id
WHERE
    card_effect_type1.name = 'Ability'
    AND card_effect_type2.name = 'Ancient Trait'
GROUP BY
    card.id
LIMIT
    24

Mijn poging om de query te inverteren is als volgt, maar het probleem is dat deze gebruik maakt van een correlated subquery:

code:

SELECT
    card.id
FROM
    card card
WHERE
    NOT(
        EXISTS(
            SELECT
                1
            FROM
                card sub_card
                INNER JOIN card_effect card_effect1 ON sub_card.id = card_effect1.card_id
                INNER JOIN card_effect_type card_effect_type1 ON card_effect1.type_id = card_effect_type1.id
                INNER JOIN card_effect card_effect2 ON sub_card.id = card_effect2.card_id
                INNER JOIN card_effect_type card_effect_type2 ON card_effect2.type_id = card_effect_type2.id
            WHERE
                sub_card.id = card.id
                AND card_effect_type1.name = 'Ability'
                AND card_effect_type2.name = 'Ancient Trait'
        )
    )
GROUP BY
    card.id
LIMIT
    24

Zou het mogelijk zijn deze query op een ander manier te inverteren zonder gebruik te maken van een correlated subquery?

[Woy]

Je kan de conditie natuurlijk gewoon inverteren, of gebruik maken van NOT IN met een subquery, die hoeft dan niet correlated te zijn.

[GlowMouse]

Een LEFT JOIN met IS NULL behoort ook tot de mogelijkheden. Alle drie de mogelijkheden zouden ongeveer even snel moeten zijn (met jouw query of met de NOT IN zou je eens kunnen proberen om de twee voorwaarden te scheiden zodat je twee keer een NOT EXISTS of een NOT IN krijgt).

[ Voor 11% gewijzigd door GlowMouse op 19-01-2020 21:06 ]

[egonolieux]

Woy schreef op zondag 19 januari 2020 @ 20:58:
Je kan de conditie natuurlijk gewoon inverteren

Bedoel je met "gewoon inverteren" dit?:

code:

WHERE
    card_effect_type1.name <> 'Ability'
    AND card_effect_type2.name <> 'Ancient Trait'

Indien je dit bedoelt, werkt dit maar deels omdat ik bij het inverteren ook alle kaarten wil die geen effecten hebben. Door het op deze manier te inverteren filter je alleen maar op kaarten die effecten hebben.

Misschien alles left joinen en een derde keer joinen en daar een IS NULL op doen?

Woy schreef op zondag 19 januari 2020 @ 20:58:
NOT IN met een subquery

Wat bedoel je hier precies mee?

[ Voor 5% gewijzigd door egonolieux op 19-01-2020 23:12 ]

[Gropah]

egonolieux schreef op zondag 19 januari 2020 @ 23:08:
[...]

code:

WHERE
    card_effect_type1.name <> 'Ability'
    AND card_effect_type2.name <> 'Ancient Trait'

[...]

A != B && C != D is volgens mij geen goede negatie van A == B && C == D

[The Eagle]

Ik zie net dat Mysql ook gewoon de minus operator ondersteunt. Minus, union en consorten zijn in dit soort gevallen heel handig. Denk aan iets als:

SQL:

SELECT
    card.id
From card
MINUS
SELECT
    card.id
FROM
    card
    INNER JOIN card_effect card_effect1 ON card.id = card_effect1.card_id
    INNER JOIN card_effect_type card_effect_type1 ON card_effect1.type_id = card_effect_type1.id
    INNER JOIN card_effect card_effect2 ON card.id = card_effect2.card_id
    INNER JOIN card_effect_type card_effect_type2 ON card_effect2.type_id = card_effect_type2.id
WHERE
    card_effect_type1.name = 'Ability'
    AND card_effect_type2.name = 'Ancient Trait'
GROUP BY
    card.id
LIMIT
    24

Effectief pak je dus alle id's uit je cardtabel die niet aan je initiele query voldoen. Dan heb je volgens mij je doel bereikt toch?

[Woy]

egonolieux schreef op zondag 19 januari 2020 @ 23:08:
[...]


Bedoel je met "gewoon inverteren" dit?:

code:

WHERE
    card_effect_type1.name <> 'Ability'
    AND card_effect_type2.name <> 'Ancient Trait'

Het zou dan

SQL:

WHERE
    card_effect_type1.name <> 'Ability'
    OR card_effect_type2.name <> 'Ancient Trait'

of

SQL:

WHERE NOT (
    card_effect_type1.name = 'Ability'
    AND card_effect_type2.name = 'Ancient Trait' )

worden

Indien je dit bedoelt, werkt dit maar deels omdat ik bij het inverteren ook alle kaarten wil die geen effecten hebben. Door het op deze manier te inverteren filter je alleen maar op kaarten die effecten hebben.

Zonder sub-query zal je dan sowieso iets met een Left-Join moeten doen

Wat bedoel je hier precies mee?

SQL:

SELECT *
FROM card
WHERE card.id NOT IN ( /*jouw query*/ )

Of zoals @GlowMouse voorstelt, met twee NOT IN clauses

SQL:

SELECT *
FROM card
WHERE card.id NOT IN ( /* Query met alle card ids met 'ability' */ )
AND card.id NOT IN ( /* Query met alle card ids met 'Ancient Trait' */ )

[Vloris]

egonolieux schreef op zondag 19 januari 2020 @ 23:08:
Bedoel je met "gewoon inverteren" dit?:

code:

WHERE
    card_effect_type1.name <> 'Ability'
    AND card_effect_type2.name <> 'Ancient Trait'

Indien je dit bedoelt, werkt dit maar deels omdat ik bij het inverteren ook alle kaarten wil die geen effecten hebben. Door het op deze manier te inverteren filter je alleen maar op kaarten die effecten hebben.

Dan doe je toch dit:

code:

WHERE
    (card_effect_type1.name IS NULL OR card_effect_type1.name <> 'Ability')
    AND (card_effect_type2.name IS NULL OR card_effect_type2.name <> 'Ancient Trait')

of is dat te simpel gedacht?

[egonolieux]

Gropah schreef op zondag 19 januari 2020 @ 23:32:
[...]

A != B && C != D is volgens mij geen goede negatie van A == B && C == D

Klopt, het inverse is A != B || C != D, maar omdat SQL in essentie een gefilterd kruisproduct is, werkt dit niet zo maar.

The Eagle schreef op zondag 19 januari 2020 @ 23:39:
Ik zie net dat Mysql ook gewoon de minus operator ondersteunt. Minus, union en consorten zijn in dit soort gevallen heel handig. Denk aan iets als:

SQL:

SELECT
    card.id
From card
MINUS
SELECT
    card.id
FROM
    card
    INNER JOIN card_effect card_effect1 ON card.id = card_effect1.card_id
    INNER JOIN card_effect_type card_effect_type1 ON card_effect1.type_id = card_effect_type1.id
    INNER JOIN card_effect card_effect2 ON card.id = card_effect2.card_id
    INNER JOIN card_effect_type card_effect_type2 ON card_effect2.type_id = card_effect_type2.id
WHERE
    card_effect_type1.name = 'Ability'
    AND card_effect_type2.name = 'Ancient Trait'
GROUP BY
    card.id
LIMIT
    24

Effectief pak je dus alle id's uit je cardtabel die niet aan je initiele query voldoen. Dan heb je volgens mij je doel bereikt toch?

Van minus had ik nog niet gehoord. Hoewel het er zeer handig uit ziet, zou ik me willen beperken tot standaard SQL.

Woy schreef op maandag 20 januari 2020 @ 08:35:
[...]

SQL:

SELECT *
FROM card
WHERE card.id NOT IN ( /*jouw query*/ )

Of zoals @GlowMouse voorstelt, met twee NOT IN clauses

SQL:

SELECT *
FROM card
WHERE card.id NOT IN ( /* Query met alle card ids met 'ability' */ )
AND card.id NOT IN ( /* Query met alle card ids met 'Ancient Trait' */ )

Beschamend dat ik er zelf niet kon op komen. Wat is eigenlijk de reden waarom je het in meerdere (2) NOT IN clauses zou opsplitsen? Meerdere kleinere datasets om op te filteren i.p.v. één grote?

Vloris schreef op maandag 20 januari 2020 @ 12:57:
[...]


Dan doe je toch dit:

code:

WHERE
    (card_effect_type1.name IS NULL OR card_effect_type1.name <> 'Ability')
    AND (card_effect_type2.name IS NULL OR card_effect_type2.name <> 'Ancient Trait')

of is dat te simpel gedacht?

Ik neem aan dat je dit wilt doen met een LEFT JOIN (zoals Woy reeds zei)? Overigens denk ik niet dat dit zal werken omdat de IS NULL elke rij zal geven als resultaat (tenzij een kaart alle mogelijke effecten heeft).

[ Voor 12% gewijzigd door egonolieux op 20-01-2020 20:21 ]

[GlowMouse]

egonolieux schreef op maandag 20 januari 2020 @ 20:16:
[...]

Beschamend dat ik er zelf niet kon op komen. Wat is eigenlijk de reden waarom je het in meerdere (2) NOT IN clauses zou opsplitsen? Meerdere kleinere datasets om op te filteren i.p.v. één grote?

Het is dan makkelijker voor de server om te herkennen dat hij na één resultaatrij in de subquery al kan stoppen.

[egonolieux]

GlowMouse schreef op maandag 20 januari 2020 @ 20:22:
[...]

Het is dan makkelijker voor de server om te herkennen dat hij na één resultaatrij in de subquery al kan stoppen.

Bedoel je dat als je bijvoorbeeld 5 van die subqueries heb en de 2e false geeft, dat de 3e tem 5e niet meer uitgevoerd moeten worden?

[chaoscontrol]

egonolieux schreef op maandag 20 januari 2020 @ 20:24:
[...]


Bedoel je dat als je bijvoorbeeld 5 van die subqueries heb en de 2e false geeft, dat de 3e tem 5e niet meer uitgevoerd moeten worden?

Idd, er wordt per row gekeken of er aan de condities wordt voldaan. Is de 1e of 2e etc conditie al niet gevonden dan kan de server verder met de volgende rij.

[GlowMouse]

egonolieux schreef op maandag 20 januari 2020 @ 20:24:
[...]

Bedoel je dat als je bijvoorbeeld 5 van die subqueries heb en de 2e false geeft, dat de 3e tem 5e niet meer uitgevoerd moeten worden?

Dat bedoelde ik niet (maar is een ander voordeel). Ik bedoel de situatie waarin een subquery meer dan één rij kan teruggeven. De server kan dan na één rij al stoppen.

[egonolieux]

En stel dat ik nu op een stuk of 10 effecten wil filteren, is de performance dan niet zeer afhankelijk van de input? Als de server na de 2e al kan stoppen zal het waarschijnlijk performanter zijn dan één grote subquery, maar wat met 10 of meer?

[Voutloos]

egonolieux schreef op maandag 20 januari 2020 @ 20:16:
Ik neem aan dat je dit wilt doen met een LEFT JOIN (zoals Woy reeds zei)? Overigens denk ik niet dat dit zal werken omdat de IS NULL elke rij zal geven als resultaat (tenzij een kaart alle mogelijke effecten heeft).

Een LEFT JOIN, met de waarde in de join conditie.

SQL:

LEFT JOIN card_effect card_effect1
    ON sub_card.id = card_effect1.card_id
LEFT JOIN card_effect_type card_effect_type1
    ON card_effect1.type_id = card_effect_type1.id AND card_effect_type1.name = 'Ability'
/* herhaal voor type2, etc */
WHERE
    card_effect_type1.name IS NULL
/* AND type2 null, etc*.

Een van de kortere oplossingen, en het zou me niet verbazen als het ook een van de snellere is.

(Of de oplossing met meerdere NOT IN (SELECT card.id) clauses)

[ Voor 3% gewijzigd door Voutloos op 20-01-2020 20:53 ]

[Bejit]

Ehm, waarom wil je zonder sub query? Het gaat uiteindelijk om het query plan wat er achter draait.

Er zijn tientallen manieren om dit resultaat op te halen.

SQL:

WITH CTE(SELECT 'Ability' as [Ability],  'Ancient Trait' as [Ancient Trait])
SELECT
    card.id
FROM
    card
    INNER JOIN card_effect card_effect1 ON card.id = card_effect1.card_id
    INNER JOIN card_effect_type card_effect_type1 ON card_effect1.type_id = card_effect_type1.id 
    INNER JOIN card_effect card_effect2 ON card.id = card_effect2.card_id
    INNER JOIN card_effect_type card_effect_type2 ON card_effect2.type_id = card_effect_type2.id
WHERE NOT EXISTS(SELECT NULL FROM CTE WHERE card_effect_type1.name <> CTE.[Ability]
    OR card_effect_type2.name <> CTE.[Ancient Trait])
GROUP BY
    card.id
LIMIT
    24

Of je kan gewoon de WHERE en OR clause gebruiken . Verder zou ik mij ook afvragen of de group by echt nodig is. Zulke operators(Distinct, order by etc.) vreten vaak resources, tenzij je een goede (clustered/columnstore) index hebt.

GlowMouse schreef op maandag 20 januari 2020 @ 20:22:
[...]

Het is dan makkelijker voor de server om te herkennen dat hij na één resultaatrij in de subquery al kan stoppen.

Onzin, dat bepaalt de query engine zelf wel. Je kan 2 totaal andere queries schrijven en het exact zelfde query plan krijgen. De vraag is of het compileren van de query even vlug gaat.
Voorbeeld: (Eerst met de NOT IN en tweede met AND)
https://ibb.co/9GTT7dk
https://ibb.co/nwPy2Sg

[ Voor 19% gewijzigd door Bejit op 20-01-2020 21:13 ]

[egonolieux]

Voutloos schreef op maandag 20 januari 2020 @ 20:47:
[...]
Een LEFT JOIN, met de waarde in de join conditie.

SQL:

LEFT JOIN card_effect card_effect1
    ON sub_card.id = card_effect1.card_id
LEFT JOIN card_effect_type card_effect_type1
    ON card_effect1.type_id = card_effect_type1.id AND card_effect_type1.name = 'Ability'
/* herhaal voor type2, etc */
WHERE
    card_effect_type1.name IS NULL
/* AND type2 null, etc*.

Een van de kortere oplossingen, en het zou me niet verbazen als het ook een van de snellere is.

(Of de oplossing met meerdere NOT IN (SELECT card.id) clauses)

Werkt inderdaad ook

Bejit schreef op maandag 20 januari 2020 @ 21:02:
Ehm, waarom wil je zonder sub query? Het gaat uiteindelijk om het query plan wat er achter draait.

Ik heb geen problemen met subqueries, wel met trage correlated subqueries zoals uit mijn eerste post

Bejit schreef op maandag 20 januari 2020 @ 21:02:
Of je kan gewoon de WHERE en OR clause gebruiken . Verder zijn ik mij ook afvragen of de group by echt nodig is. Zulke operators(Distinct, order by etc.) vreten vaak resources, tenzij je een goede (clustered/columnstore) index hebt.

Maar als je niet grouped/distinct dan krijg je toch bergen dezelfde IDs als resultaat terug? De ID is overigens de primary key (en een integer).

[Bejit]

egonolieux schreef op maandag 20 januari 2020 @ 21:13:
[...]


Werkt inderdaad ook


[...]


Ik heb geen problemen met subqueries, wel met trage correlated subqueries zoals uit mijn eerste post


[...]


Maar als je niet grouped/distinct dan krijg je toch bergen dezelfde IDs als resultaat terug? De ID is overigens de primary key (en een integer).

Hoeft niet. Wat staat er in de type tabel? Kan je daar misschien niet een filter op gooien?

Wat is het exact resultaat dat je wil bereiken? Lijst van 24 id's? Een count? Een sum?

Nog een manier:

SQL:

SELECT
    c.id
FROM
    card c
    WHERE NOT EXISTS(SELECT NULL FROM card_effect cf1 
INNER JOIN card_effect_type ct1 ON cf1.type_id = ct1.id
WHERE c.cardid - cf1.cardid AND ct1.name = 'Ability' ) 
OR
NOT EXISTS(SELECT NULL FROM card_effect cf2 
INNER JOIN card_effect_type ct2 ON cf2.type_id = ct2.id
WHERE c.cardid = cf2.cardid AND ct2.name = 'Ancient Trait' ) 
LIMIT
    24

Huppakee, geen group by (Ff checken of zelfde resultaat krijgt, doe dit uit de losse pols)

EDIT: Tikvauten verbeterd.

[ Voor 31% gewijzigd door Bejit op 20-01-2020 21:32 ]

[egonolieux]

Bejit schreef op maandag 20 januari 2020 @ 21:15:
[...]

Hoeft niet. Wat staat er in de type tabel? Kan je daar misschien niet een filter op gooien?

Wat is het exact resultaat dat je wil bereiken? Lijst van 24 id's? Een count? Een sum?

Als je een hele boel joins doet (many to many en one to many), dan krijg je toch per definitie dubbele waarden? Hou zou je deze wegfilteren zonder te groupen?

Ik wil in essentie een gepagineerde lijst van +- 20000 cards met een aantal filters er op. Ik haal eerst de IDs op om achteraf de gehele rows uit de card tabel te fetchen. De reden dat ik eerst de IDs op haal, is dat er dan niet gegrouped moet worden op de gehele row (is heel wat sneller).

[ Voor 4% gewijzigd door egonolieux op 20-01-2020 21:22 ]

[Bejit]

egonolieux schreef op maandag 20 januari 2020 @ 21:20:
[...]


Als je een hele boel joins doet (many to many en one to many), dan krijg je toch per definitie dubbele waarden? Hou zou je deze wegfilteren zonder te groupen?

Ik wil in essentie een gepagineerde lijst van +- 20000 cards met een aantal filters er op. Ik haal eerst de IDs op om achteraf de gehele rows uit de card tabel te fetchen. De reden dat ik eerst de IDs op haal, is dat er dan niet gegrouped moet worden op de gehele row (is heel wat sneller).

Zie oplossing boven, geen group by meer nodig. Sowieso kan dat ook via een subquery. Laat SQL je query compileren en probeer niet slimmer te zijn dan de engine, tenzij je exact weet wat je doet.

Altijd onthouden, semi joins zijn sneller dan wat voor join dan ook en ideaal om een lijst te generen zonder dubbelen.

[ Voor 7% gewijzigd door Bejit op 20-01-2020 21:33 ]

[Voutloos]

Je query lijkt op de gemarkeerde oplossing van Woy, behalve dan dat je hem weer correlated gemaakt hebt.

[egonolieux]

Bejit schreef op maandag 20 januari 2020 @ 21:24:
[...]

Zie oplossing boven, geen group by meer nodig. Sowieso kan dat ook via een subquery. Laat SQL je query compileren en probeer niet slimmer te zijn dan de engine, tenzij je exact weet wat je doet.

Altijd onthouden, semi joins zijn sneller dan wat voor join dan ook en ideaal om een lijst te generen zonder dubbelen.

En wat als je een stuk of 10 verschillende filters hebt die elk onafhankelijk van elkaar dynamisch aan de query toegevoegd worden? De filter op de effecten is er maar 1 van. Op het punt dat de query uitgevoerd moet worden, weet ik niet of één van die filters ergens een join gebruikt (waardoor ik niet weet of ik moet groupen of niet). Ik zou natuurlijk alles in subqueries kunnen steken, maar ik weet niet of dat echt veel nut heeft . Uit mijn benchmarks blijkt dat de group by verwaarloosbaar is qua performance.

[egonolieux]

Voutloos schreef op maandag 20 januari 2020 @ 21:36:
Je query lijkt op de gemarkeerde oplossing van Woy, behalve dan dat je hem weer correlated gemaakt hebt.

Inderdaad, nu je het zegt

[Bejit]

egonolieux schreef op maandag 20 januari 2020 @ 21:36:
[...]


En wat als je een stuk of 10 verschillende filters hebt die elk onafhankelijk van elkaar dynamisch aan de query toegevoegd worden? De filter op de effecten is er maar 1 van. Op het punt dat de query uitgevoerd moet worden, weet ik niet of één van die filters ergens een join gebruikt (waardoor ik niet weet of ik moet groupen of niet). Ik zou natuurlijk alles in subqueries kunnen steken, maar ik weet niet of dat echt veel nut heeft . Uit mijn benchmarks blijkt dat de group by verwaarloosbaar is qua performance.

Dat komt omdat er waarschijnlijk een clustered index staat op die PK (cardid) . Die group by zou ik echt weglaten als ik jou was. Nogmaals zie mijn laatste geposted oplossing.

Met die oplossing kan je direct de attributen van de card toevoegen omdat je geen group by hoeft te doen en nogmaals moet joinen op zijn eigen kaart.

Voutloos schreef op maandag 20 januari 2020 @ 21:36:
Je query lijkt op de gemarkeerde oplossing van Woy, behalve dan dat je hem weer correlated gemaakt hebt.

Ik weet niet of je mijn query bedoelt, maar tussen onze 2 queries zit een levensgroot verschil .

[edeboeck]

egonolieux schreef op maandag 20 januari 2020 @ 21:20:
[...]
Als je een hele boel joins doet (many to many en one to many), dan krijg je toch per definitie dubbele waarden? Hou zou je deze wegfilteren zonder te groupen?

Dat kan je doen door gebruik te maken van keyword DISTINCT.... voorbeeldje
Ja, ik weet dat W3Schools niet altijd correct zit, maar in dit geval was dat het snelste en duidelijkste voorbeeld dat ik vond

Edit:
GROUP BY is echt wel bedoeld voor het geval je statistische waarden over een groep records wilt berekenen, niet om duplicaten te filteren.

[ Voor 11% gewijzigd door edeboeck op 20-01-2020 21:50 . Reden: toevoeging GROUP BY ]

[Bejit]

edeboeck schreef op maandag 20 januari 2020 @ 21:49:
[...]
Dat kan je doen door gebruik te maken van keyword DISTINCT.... voorbeeldje
Ja, ik weet dat W3Schools niet altijd correct zit, maar in dit geval was dat het snelste en duidelijkste voorbeeld dat ik vond

Zelfde effect als group by.(sterker nog, zonder aggregatie, exacte zelfde operator in query plan, c.q. zelfde query plan)

[edeboeck]

Bejit schreef op maandag 20 januari 2020 @ 21:50:
[...]

Zelfde effect als group by.(sterker nog, zonder aggregatie, exacte zelfde operator in query plan, c.q. zelfde query plan)

Dat geloof ik gerust hoor, was eerder toevoeging omdat TS de vraag stelde hoe hij anders dubbels kon weren.

[Bejit]

edeboeck schreef op maandag 20 januari 2020 @ 21:51:
[...]
Dat geloof ik gerust hoor, was eerder toevoeging omdat TS de vraag stelde hoe hij anders dubbels kon weren.

Je methode is wel netter hoor, want is direct duidelijk dat je resultaat wilt ontdubbelen. Echter distinct, zeker over grotere datasets, zijn killing.

Verder TS, zie: https://sqlperformance.co...eries/left-anti-semi-join waarom not exists beter zijn dan de alternatieven.

[edeboeck]

Bejit schreef op maandag 20 januari 2020 @ 21:54:
[...]

Je methode is wel netter hoor, want is direct duidelijk dat je resultaat wilt ontdubbelen. Echter distinct, zeker over grotere datasets, zijn killing.

Verder TS, zie: https://sqlperformance.co...eries/left-anti-semi-join waarom not exists beter zijn dan de alternatieven.

Lichtelijk off-topic t.o.v. TS, maar gezien jouw eerdere opmerking i.v.m. query plan, mag ik dan concluderen dat GROUP BY's over grotere datasets ook killing zijn?
(ik zou daarvan uitgaan gezien de operatie die uitgevoerd moet worden waarvan de worst-case performantie volgens mij per definitie moet tegenvallen)

[Bejit]

edeboeck schreef op maandag 20 januari 2020 @ 21:57:
[...]
Lichtelijk off-topic t.o.v. TS, maar gezien jouw eerdere opmerking i.v.m. query plan, mag ik dan concluderen dat GROUP BY's over grotere datasets ook killing zijn?
(ik zou daarvan uitgaan gezien de operatie die uitgevoerd moet worden waarvan de worst-case performantie volgens mij per definitie moet tegenvallen)

GROUP BY zijn inderdaad killing. Tenzij je realtime data nodig hebt is het beter om te kijken naar data warehouses en cubes die hiervoor geoptimaliseerd zijn.

GROUP BY is lastig te indexen zonder de rest van je queries te killen.

[Voutloos]

Bejit schreef op maandag 20 januari 2020 @ 21:49:
Ik weet niet of je mijn query bedoelt, maar tussen onze 2 queries zit een levensgroot verschil .

Jouw query is een slechte versie van Woy zijn query. Hij lijkt er op, maar correlated dus voldoet niet aan de vraag.

Het stuk query dat ik gaf was een voorbeeld hoe het alternatief met een left join zou kunnen. Afhankelijk van je voorkeuren, zou je die aanpak mooier kunnen vinden, puur subjectief. Bovendien vergeten mensen vaak dat je ook een literal waarde als join conditie kan gebruiken, dus ik vond het leuk om dat zo te schrijven. Ik zou het liefst voor Woy zijn query gaan. Als de noodzaak er is zou ik de left join aanpak nog meten.

Bejit schreef op maandag 20 januari 2020 @ 21:54:
Verder TS, zie: https://sqlperformance.co...eries/left-anti-semi-join waarom not exists beter zijn dan de alternatieven.

De TL;DR bevat een hele dikke typo, of onze terminologie is anders. Ik noem die subquery dus niet correlated . Deze kan duidelijk 1x, terwijl de subquery zoals gegeven in topicstart voor elke card opnieuw gedaan moet worden.

Expliciet: Een subquery heeft bij Woy geen card.id van de buitenste query nodig. Dat zie je door goed te lezen ipv met incomplete vuistregels te strooien.

[Bejit]

Voutloos schreef op maandag 20 januari 2020 @ 22:09:
[...]
Jouw query is een slechte versie van Woy zijn query. Hij lijkt er op, maar correlated dus voldoet niet aan de vraag.

Het stuk query dat ik gaf was een voorbeeld hoe het alternatief met een left join zou kunnen. Afhankelijk van je voorkeuren, zou je die aanpak mooier kunnen vinden, puur subjectief. Bovendien vergeten mensen vaak dat je ook een literal waarde als join conditie kan gebruiken, dus ik vond het leuk om dat zo te schrijven. Ik zou het liefst voor Woy zijn query gaan. Als de noodzaak er is zou ik de left join aanpak nog meten.

[...]
De TL;DR bevat een hele dikke typo, of onze terminologie is anders. Ik noem die subquery dus niet correlated . Deze kan duidelijk 1x, terwijl de subquery zoals gegeven in topicstart voor elke card opnieuw gedaan moet worden.

Expliciet: Een subquery heeft bij Woy geen card.id van de buitenste query nodig. Dat zie je door goed te lezen ipv met incomplete vuistregels te strooien.

Als je had gelezen wat de requirements was, dan weet je dat je een totaal ander resultaat moet krijgen. Bij Woy moet je nog steeds DISTINCTen/GROUPen en vervolgens weer joinen met de card om attributen erbij te joinen.

Wat uiteindelijke sneller is hangt af van indexes en de hoeveelheid data. Sowieso je expliciet regel is om te huilen/lachen.

Hieronder ultime voorbeeld: (Getest op WWImporters DB)
Mijn query:

SQL:

SELECT SupplierID
  FROM [Purchasing].[Suppliers] S
  WHERE 
   EXISTS(SELECT NULL FROM (SELECT SUPPLIERID FROM [Purchasing].[PurchaseOrders] PO
  INNER JOIN [Purchasing].[PurchaseOrderLines] POL ON PO.PurchaseOrderID = POL.PurchaseOrderID
  WHERE NOT (POL.PackageTypeID =9 OR POL.StockItemID =158)) a
   WHERE S.SupplierID = a.SupplierID
   )

(6 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PurchaseOrderLines'. Scan count 1, logical reads 158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PurchaseOrders'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Suppliers'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 36 ms.

Voy query:

SQL:

SELECT S.SupplierID
  FROM [Purchasing].[Suppliers] S
  INNER JOIN [Purchasing].[PurchaseOrders] PO ON S.SupplierID = PO.SupplierID
  INNER JOIN [Purchasing].[PurchaseOrderLines] POL ON PO.PurchaseOrderID = POL.PurchaseOrderID
  WHERE NOT (POL.PackageTypeID =9 OR POL.StockItemID =158 )
  GROUP BY S.SupplierID

(6 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PurchaseOrderLines'. Scan count 1, logical reads 158, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PurchaseOrders'. Scan count 13, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Suppliers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 81 ms.

Mijn query(met semi join) is consistent 50 ms sneller . Ja is niet zelfde DB of data, maar laat TS lekker testen dan kan die zelf kiezen.