[SQL] Laatste versies ophalen uit een tabel (nesting/perform

[Flax]

Binnen sql loop ik toch vaak tegen het probleem aan, dat ik de eerste records van een bepaalde conditie terug wil krijgen. Nu weet ik dat het mogelijk is om dit genest opte lossen. Is het ook mogelijk om dit op een andere manier op te lossen?
Momenteel ben ik dit aan het testen op Oracle, maar mocht iemand weten hoe je het elegant op een ander DBMS zou oplossen, dan zou ik het ook graag weten.

Ik heb de volgende tabel in sql:

code:

CREATE TABLE TEST
(
  ID  INTEGER  NOT NULL,
  VERSION  INTEGER  DEFAULT 0  NOT NULL,
  VALUE  INTEGER  NOT NULL,
);

CREATE UNIQUE INDEX PK_TEST ON TEST (ID, VERSION) ;

In deze tabel heb ik voor de gelegenheid de volgende data gestopt:

code:

id  version value
1   1   11
1   2   12
2   1   21
2   2   22

Met het volgende query kan ik de gegevens ophalen:

code:

select * from test a where version = (select max(version) from test b where a.id = b.id)

Het gewenste resultaat dat ik terug wil hebben is het volgende:

code:

id  version value
1   2   12
2   2   22

Omdat ik dit generiek wil gebruiken, dus op meerdere tabellen met verschillende velden, zoek ik een zo generiek mogelijk antwoord. Weet iemand hoe ik dit zonder nesting kan oplossen?

[whoami]

Kent oracle niet zoiets als TOP in T-SQL of LIMIT in MySQL ?

[cameodski]

Ik weet niet hoe je dit precies in Oracle kunt doen, maar in MSSQL doe ik het zo:

code:

SELECT b.ID, b.Version, b.Value
FROM ( SELECT ID, MAX (Version) AS MaxVersion FROM dbo.test GROUP BY ID) AS a
JOIN dbo.test AS b ON (b.ID = a.ID AND b.Version = a.MaxVersion)

[curry684]

whoami schreef op 07 mei 2004 @ 12:16:
Kent oracle niet zoiets als TOP in T-SQL of LIMIT in MySQL ?

Als ik het goed begrijp is dat niet wat ie vraagt

[cameodski]

curry684 schreef op 07 mei 2004 @ 12:58:
[...]

Als ik het goed begrijp is dat niet wat ie vraagt

Denk ik ook niet, maar wat wel grappig is, is dat je een max zonder group by ook kunt vervangen door top of limit te gebruiken.
Dus deze twee queries zouden hetzelfde resultaat moeten geven:

code:

select max(version) from test

select top 1 version from test order by version desc
of MySQL variant:
select version from test order by version desc limit 1,1

[justmental]

Bij Oracle doe je dit gewoon op de door jou beschreven manier, dus met een subquery.
Wat de handigste methode is om dit uit te voeren beslist Oracle zelf wel dmv. het executie plan.

[cameodski]

justmental schreef op 07 mei 2004 @ 13:08:
Bij Oracle doe je dit gewoon op de door jou beschreven manier, dus met een subquery.
Wat de handigste methode is om dit uit te voeren beslist Oracle zelf wel dmv. het executie plan.

Als dat waar is (wat ik me niet voor kan stellen) dan is Oracle een stuk slimmer dan MSSQL. In MSSQL zal de variant die ik gegeven heb, stukken sneller zijn dan de variant met de subquery.
Het probleem is, dat er in de subquery een kolom uit de hoofdquery wordt gebruikt. Daardoor moet de subquery record voor record (in het geheugen) worden uitgevoerd.
In de variant die ik heb gegeven, kan er eerst gegroepeerd worden en dan hoeft ie daarna alleen maar een join te doen.
Oracle zal wel heel erg slim moeten zijn om het intelligente alternatief zonder subqueries te kunnen bedenken.

[ Voor 33% gewijzigd door cameodski op 07-05-2004 13:12 ]

[justmental]

cameodski schreef op 07 mei 2004 @ 13:10:
[...]

Als dat waar is (wat ik betwijfel) dan is Oracle een stuk slimmer dan MSSQL. In MSSQL zal de variant die ik gegeven heb, stukken sneller zijn dan de variant met de subquery.

Er zijn gevallen waarbij een specifieke notatie of een hint nodig zijn om te forceren dat het meest efficiente plan gekozen wordt, maar hier moet ie toch zeker wel zelf uit komen

[curry684]

cameodski schreef op 07 mei 2004 @ 13:10:
[...]

Als dat waar is (wat ik me niet voor kan stellen) dan is Oracle een stuk slimmer dan MSSQL. In MSSQL zal de variant die ik gegeven heb, stukken sneller zijn dan de variant met de subquery.

De enige versie die ik van je kan vinden is met subquery (de andere 2 queries die je post zijn niet af) maar ik denk dat de slimheid van SQL Server je nog hard zou verbazen. Heb je feitelijk de execution plans bekeken?

Zie [rml]curry684 in "[ MySQL] Query probleem (joins?)"[/rml] voor een praktijkvoorbeeld.

[ Voor 9% gewijzigd door curry684 op 07-05-2004 13:19 ]

[cameodski]

curry684 schreef op 07 mei 2004 @ 13:16:
[...]

De enige versie die ik van je kan vinden is met subquery (de andere 2 queries die je post zijn niet af) maar ik denk dat de slimheid van SQL Server je nog hard zou verbazen. Heb je feitelijk de execution plans bekeken?

Zie [rml]curry684 in "[ MySQL] Query probleem (joins?)"[/rml] voor een praktijkvoorbeeld.

Met subquery? De eerste query die ik gepost heb, heet geen subquery. Dat is een join op een select statement en daarmee fundamenteel anders.
De subquery variant die ik bedoelde waren van de TS.

Maar misschien heb je wel gelijk dat SQL Server slimmer is dan ik denk en dat het met deze eenvoudige subquery niet uitmaakt.

En het volgende wat je in de aangehaalde topic hebt genoemd, wil ik toch graag even herhalen:
"don't use subqueries unless you have to"
En dat is wat ik met de query in mijn eerste post gerealiseerd heb. Als de queries wat ingewikkelder worden, is met subqueries de kans wel heel erg groot, dat ze bijzonder traag gaan worden.

Heb met SQL Server 7.0 een stored procedure eens teruggebracht van 5 minuten naar 10 sec. en dat alleen door subqueries te vervangen door joins.

[Flax]

Misschien heb ik mijn vraag een beetje verkeerd geformuleerd, maar het is dus niet mogelijk om zoiets als het volgende te doen?

code:

select distinct(id), first(*) 
from test
group by id, version

(misschien zelfs toevoegen van 1 generieke stored procedure?)
Het doel van mij is om de queries zo simpel mogelijk te houden, omdat ik het generiek wil toepassen.

[cameodski]

Flax schreef op 07 mei 2004 @ 13:58:
Misschien heb ik mijn vraag een beetje verkeerd geformuleerd, maar het is dus niet mogelijk om zoiets als het volgende te doen?

code:

select distinct(id), first(*) 
from test
group by id, version

(misschien zelfs toevoegen van 1 generieke stored procedure?)
Het doel van mij is om de queries zo simpel mogelijk te houden, omdat ik het generiek wil toepassen.

Zo simpel als je het graag wil, kan het in ieder geval niet.
En wat bedoel je met generiek? Moet dit voor meerdere tabellen gebeuren?

[Flax]

cameodski schreef op 07 mei 2004 @ 14:29:
[...]

Zo simpel als je het graag wil, kan het in ieder geval niet.
En wat bedoel je met generiek? Moet dit voor meerdere tabellen gebeuren?

Wel, voor alle tabellen die mijn systeem heeft, wil ik deze "functionaliteit" toevoegen. Dit om een soort van "versioning" toe te voegen aan mijn systeem.

[cameodski]

Als je dat voor alle tabellen wilt doen, zou je kunnen overwegen om oude versies in aparte tabellen te zetten. Is toch wel een stukje beter voor de performance.
Een alternatief is om bij te houden welke versie de laatste versie is. Dan hoef je bijvoorbeeld alleen maar te filteren op IsLast = 1.
Constant voor alle tabellen dergelijke queries uit moeten voeren, lijkt me niet zo wenselijk.

P.S. Waarom wil je eigenlijk overal versies van bij houden?

[ Voor 8% gewijzigd door cameodski op 07-05-2004 15:26 ]

[Flax]

cameodski schreef op 07 mei 2004 @ 15:25:
Als je dat voor alle tabellen wilt doen, zou je kunnen overwegen om oude versies in aparte tabellen te zetten. Is toch wel een stukje beter voor de performance.

Ik was in de veronderstelling dat wanneer dit geindexeerd was tezamen met de andere velden waarop ik zoek dit helemaal niets uitmaakt. Verder weet ik niet hoe ik deze dan synchoon moet houden (moet ik weer triggers maken om dit door de database te laten doen).
Ik wil met mijn nummering er voor zorgen dat de laatste versie gewoon '0' is, alle andere versies zijn ongelijk aan 0(groter), maar dit heb ik niet toegevoegd aan het voorbeeld om onnodige complexiteit uit het verhaal te verwijderen
Opzich zou ik er idd ook wel een view op kunnen creeeren.

Een alternatief is om bij te houden welke versie de laatste versie is. Dan hoef je bijvoorbeeld alleen maar te filteren op IsLast = 1.

Zie voorafgaande

Constant voor alle tabellen dergelijke queries uit moeten voeren, lijkt me niet zo wenselijk.

P.S. Waarom wil je eigenlijk overal versies van bij houden?

Omdat ik versioning, transacties en workflow wil toevoegen aan een database onafhankelijk programma
Hiervoor zoek ik dan ook een uniforme manier, en mijn gedacht was dat je met een versie nummer toch al een aardig eind zou kunnen komen.