[SQL] records die niet in een tabel voorkomen tonen

[Standeman]

Ik zit met een probleempje waar ik maar niet uitkom. Ik ben bezig met een plan systeem en ziet er uit als volgt.

Ik heb vier tabellen, te weten

objects(id, name) //objecten waarvoor taken uitgevoerd moeten worden.
taskdefinitions(id, name) //Gedefinieerde taken
object_taskdefinitions (object_id, task_def_id)
tasks(id, objectid, task_def_id)

Nu wil ik weten welke object / taskdefinitions combinaties er zijn die nog niet in de tasks tabel voorkomen.

Ik ben gekomen tot:

SQL:

select td.* from objects s, taskdefinitions td, objects_taskdefinitions ot
where s.id = ot.objectid
and ot.taskdefinitionid = td.id
and (ot.taskdefinitionid not in (select t.taskdefinitionid from tasks t) 
  and ot.objectid not in (select t.objectid from tasks t));

maar dit geeft me (uiteraard) niet de juiste resultaat. Hier krijg ik een lijst van taakdefinitiies welke niet voorkomen in de task tabel, zonder dat er rekening wordt het gehouden met de combinate van taskdefinition id en object id.

edit:

roep even als het niet duidelijk is, dan geef ik een voorbeeldje

[ Voor 4% gewijzigd door Standeman op 10-04-2009 16:33 ]

[Dido]

Het lijkt alsof een Cartesisch product eindelijk eens nuttig kan zijn

SQL:

SELECT o.id, td.id FROM objects o JOIN taskdefs td
LEFT OUTER JOIN tasks t ON t.obj_id = o.id and t.td_id = td.id
WHERE t.id IS NULL

Oftewel: ik select alle mogelijke object/taskdef combinaties (vandaar geen join-constraint op d eeerste join - dit is meestal gewoon fout!), en daarvan selecteer ik alles wat ik niet in de tasks tabel tegenkom.

Het nut van de object/taskdef-tabel ontgaat me dan wel een beetje, trouwens.

Als je alleen geinteresseerd bent in de in die tabel gedefinieerde combinaties wordt het nog simpeler:

SQL:

SELECT to.obj_id, to.td_id FROM objects_taskdefs to
LEFT OUTER JOIN tasks t ON t.obj_id = to.obj_id and t.td_id = to.td_id
WHERE t.id IS NULL

[ Voor 20% gewijzigd door Dido op 10-04-2009 16:50 ]

[Standeman]

Dido schreef op vrijdag 10 april 2009 @ 16:48:
Het lijkt alsof een Cartesisch product eindelijk eens nuttig kan zijn

SQL:

SELECT o.id, td.id FROM objects o JOIN taskdefs td
LEFT OUTER JOIN tasks t ON t.obj_id = o.id and t.td_id = td.id
WHERE t.id IS NULL

Oftewel: ik select alle mogelijke object/taskdef combinaties (vandaar geen join-constraint op d eeerste join - dit is meestal gewoon fout!), en daarvan selecteer ik alles wat ik niet in de tasks tabel tegenkom.

Het nut van de object/taskdef-tabel ontgaat me dan wel een beetje, trouwens.

Als je alleen geinteresseerd bent in de in die tabel gedefinieerde combinaties wordt het nog simpeler:

SQL:

SELECT to.obj_id, to.td_id FROM objects_taskdefs to
LEFT OUTER JOIN tasks t ON t.obj_id = to.obj_id and t.td_id = to.td_id
WHERE t.id IS NULL

hmmm, dat schijnt toch niet helemaal te werken zoals ik bedoelde. Ik denk dat ik het even wat beter moet uitleggen met een voorbeeldje en het wat versimpelen.

Tabel object_taskdef:

objectid	taskdefid
1	10
1	11
2	10
2	12

Tabel tasks:

taskid	objectid	taskdefid
101	2	10

Het resultaat is dat alle waarden uit tabel object_taskdef wil krijgen m.u.v. de waarden in die in tasks zitten.
Resultaat:

objectid	taskdefid
1	10
1	11
2	12

Ik heb inmiddels all "soorten" outer joins uitgeprobeerd maar ik krijg niet het resultaat. Ik ga nu maar eens lezen wat ze ook al weer betekende (vroegah, had ik een geweldige DBA als collega, helaas nu niet meer ).

[Pete]

Dido's 2de query werkt exact zoals jij het wilt hebben. Hoe ziet Dido's query er in jouw omgeving exact uit? En wat voor resultaat geeft die query dan?


(ik hoop trwns dat de inconsistentie van veldnamen komt door het overtypen. objectid en object_id in dezelfde database )

[ Voor 31% gewijzigd door Pete op 14-04-2009 10:01 ]

[Standeman]

Pete schreef op dinsdag 14 april 2009 @ 09:56:
Dido's 2de query werkt exact zoals jij het wilt hebben. Hoe ziet Dido's query er in jouw omgeving exact uit? En wat voor resultaat geeft die query dan?


(ik hoop trwns dat de inconsistentie van veldnamen komt door het overtypen. objectid en object_id in dezelfde database )

Ik zie het nu ook

Ik had een ID verkeerd staan (10000 i.p.v. 100000)

Dido, thanks

[Dido]

offtopic:
np, in ieder geval doe je je ondertitel eer aan

[Motrax]

Dido schreef op vrijdag 10 april 2009 @ 16:48:
Het lijkt alsof een Cartesisch product eindelijk eens nuttig kan zijn

Eindelijk nuttig? Ik werk hier regelmatig mee, als ETL'ler zijnde. Een dba'er zou misschien van dit soort oplossingen gruwelen, maar DBA != ETL

Sterker nog, ik heb een dergelijke situatie van Standeman gehad in een planningspakket, alleen dan met ongebruikte -en daarmee ongeldige- WBS codes die doorgegeven moesten worden door een interface.

[Standeman]

Motrax schreef op dinsdag 14 april 2009 @ 10:17:
[...]
Eindelijk nuttig? Ik werk hier regelmatig mee, als ETL'ler zijnde. Een dba'er zou misschien van dit soort oplossingen gruwelen, maar DBA != ETL

Sterker nog, ik heb een dergelijke situatie van Standeman gehad in een planningspakket, alleen dan met ongebruikte -en daarmee ongeldige- WBS codes die doorgegeven moesten worden door een interface.

Wat een toeval, ik ben ook bezig met een planningspakket


@Dido

[d00d]

Dus je wilt alles uit object_taskdefinitions behalve alles uit tasks?
Hmmm, wat dacht je van:

SQL:

select object_id, taskdef_id from object_taskdefinitions
except
select object_id, taskdef_id from tasks;

dit werkt alleen vanaf SQL Server 2005 en hoger, good luck!

[Motrax]

Werkt ook, ook met een NOT IN e.d., maar is trager dan een outer join en daarna filteren op de NULL waarden.

Performance overweging dus.

Standeman schreef op dinsdag 14 april 2009 @ 11:11:
Wat een toeval, ik ben ook bezig met een planningspakket

@Dido

Dat schreef je al in je startpost

Overigens wel typisch iets voor een planningspakket. Je hebt namelijk een beperkt aantal WBS elementen, maar wel veel meer entries qua tijdschrijven er op, of in jouw geval een beperkt aantal acties, maar wel een hoop objecten waar acties op kunnen plaatsvinden.

[ Voor 71% gewijzigd door Motrax op 14-04-2009 11:30 ]

[Zoijar]

Motrax schreef op dinsdag 14 april 2009 @ 11:28:
Werkt ook, ook met een NOT IN e.d., maar is trager dan een outer join en daarna filteren op de NULL waarden.

Performance overweging dus.

Hoeveel milliseconde scheelt het met 10 miljoen records? Levert wel een veel overzichtelijkere query op die een opvolger ook zo begrijpt.

[Motrax]

Zoijar schreef op dinsdag 14 april 2009 @ 11:41:
[...]

Hoeveel milliseconde scheelt het met 10 miljoen records? Levert wel een veel overzichtelijkere query op die een opvolger ook zo begrijpt.

Voldoende om niet meer te rekenen in milliseconden Documentatie zat in de query zelf c.q. de bijbehorende documenten. Voor één keer was er wel documentatie

Ik ben het met je eens dat het overzichtelijker is.

In mijn geval was het ergens iets van 3 minuten verschil op 10 minuten totaal. Dus 10 minuten met de NOT EXISTS / NOT IN / EXCEPT of 7 minuten met de outer join / NULL selectie. Qua dataset was het iets van 10.000 WBS elementen en veel meer tijdschrijverij. Maar ik weet het niet meer precies, dit is al weer een tijd terug.

Noem het een trage database (waarschijnlijk geheugenprobleem), of veel data, maar met een extractie die elke dag moet draaien op een productiesysteem wil je rekening houden met performance.

Schiet me net te binnen: ik heb zoiets ook gebruikt om een delta te berekenen van een volledige extractie. Totale tijd voor de berekening: 2 uur. Ik heb niet eens gemeten hoeveel verschil in tijd er was, meteen voor de technisch 'betere' oplossing gegaan.

[ Voor 3% gewijzigd door Motrax op 14-04-2009 11:55 ]

[Standeman]

d00d schreef op dinsdag 14 april 2009 @ 11:26:
Dus je wilt alles uit object_taskdefinitions behalve alles uit tasks?
Hmmm, wat dacht je van:

SQL:

select object_id, taskdef_id from object_taskdefinitions
except
select object_id, taskdef_id from tasks;

dit werkt alleen vanaf SQL Server 2005 en hoger, good luck!

Is 'except' ook onderdeel van de SQL spec? Ik wil me namelijk wel graag aan houden, mochten we ooit een keer op een andere DB vendor overstappen (nu MySQL).

[d00d]

Motrax schreef op dinsdag 14 april 2009 @ 11:28:
Werkt ook, ook met een NOT IN e.d., maar is trager dan een outer join en daarna filteren op de NULL waarden.

Performance overweging dus.

Je kunt niet zomaar stellen dat het één langzamer is dan het andere, dat ligt er natuurlijk maar net aan welke indexen er zijn aangemaakt. Jouw query gebruikt bijvoorbeeld het task.id veld, mijn query heeft deze helemaal niet nodig.

In mijn, eenvoudige, test die geoptimaliseerd is voor mijn query zie ik dat de except query meer dan drie keer zo snel is als de outer join oplossing.

Gewoon even uitproberen dus...

[Zoijar]

Motrax schreef op dinsdag 14 april 2009 @ 11:53:
[...]
Voldoende om niet meer te rekenen in milliseconden Documentatie zat in de query zelf c.q. de bijbehorende documenten. Voor één keer was er wel documentatie

Ik ben het met je eens dat het overzichtelijker is.

In mijn geval was het ergens iets van 3 minuten verschil op 10 minuten totaal. Dus 10 minuten met de NOT EXISTS / NOT IN / EXCEPT of 7 minuten met de outer join / NULL selectie. Qua dataset was het iets van 10.000 WBS elementen en veel meer tijdschrijverij. Maar ik weet het niet meer precies, dit is al weer een tijd terug.

Beetje vreemd, dan zal je query optimizer wel niet goed z'n werk hebben gedaan en gewoon compleet lineair zijn gaan scannen. In dat geval is het kwadratisch. Maar bij een NOT IN subselect kan je gewoon lineair je subselect afwerken en hashen en vervolgens je outer select lineair afwerken en met de hash vergelijken. Dat is normaal gesproken een O(n) operatie. Die outer join doet hetzelfde, alleen bestaat de hash index al als je joined op een index veld. De winst die je dan behaalt zou alleen het hashen van de subselect moeten zijn, iets dat vrij snel gaat.