[SQL] Delete in subtabel

[ThaStealth]

Hallo,

Ik ben bezig met het schrijven van een opschoonquery die ervoor zorgt dat mijn Firebird database niet teveel data bevat (ik ben alleen geinteresseerd in de data van de laatste 7 dagen).

Ik heb de volgende 2 tabellen:

Log

DateTime	PositionID	AngleID
1-5-2010	5	1
2-5-2010	6	2
3-5-2010	7	2
4-5-2010	8	2
5-5-2010	9	2
9-5-2010	12	2
10-5-2010	13	2

Positions

PositionID	Longitude	Latitude
5	5.33124	6.31774
6	12.43123	14.550444
7	12.43123	14.550444
8	12.43123	14.550444

Stel ik wil alle records verwijderen (zowel uit de log tabel als uit de positions als uit de Angle tabel) die ouder zijn dan 7 dagen en NIET meer gebruikt worden in de laatste 7 dagen. Dus Position 5, 6, en 7 moeten gewist worden, en Angle 1 moet gewist worden (en ook eerste 3 records in de log tabel).

Op dit moment gebruik ik de volgende query:

SQL:

DELETE FROM POSITIONS b WHERE b.POSITIONID NOT IN (SELECT DISTINCT a.POSITIONID FROM LOG a WHERE a.DATETIME > CURRENT_DATE-7)

Maar deze query is tamelijk traag (er zitten in de orde van 100.000 records in elke tabel) en het zijn een stuk of 10 tabellen die op deze manier opgeschoont worden. Ik vroeg me af of er snellere mogelijkheden zijn. Ik hebt het reeds via een JOIN geprobeerd, echter vind Firebird dit geen geldige opbouw van de query

[TUX2K]

Je zou je relaties moeten controleren, deze zou je op cascade delete kunnen zetten.
Cascade delete zorgt er voor dat als er in de hoofd tabel een rij wordt verwijdert dat deze ook direct uit de subtabellen wordt verwijdert.

[Gomez12]

Draai je subquery eens om, dan krijg je IN ipv NOT IN. Geen idee hoe Firebird er intern mee omgaat, maar qua indexen etc werkt IN over het algemeen beter dan NOT IN

[ThaStealth]

TUX2K schreef op maandag 10 mei 2010 @ 16:56:
Je zou je relaties moeten controleren, deze zou je op cascade delete kunnen zetten.
Cascade delete zorgt er voor dat als er in de hoofd tabel een rij wordt verwijdert dat deze ook direct uit de subtabellen wordt verwijdert.

Het is helaas niet mogelijk om relaties aan te leggen in de tabellen.

Gomez12 schreef op maandag 10 mei 2010 @ 18:49:
Draai je subquery eens om, dan krijg je IN ipv NOT IN. Geen idee hoe Firebird er intern mee omgaat, maar qua indexen etc werkt IN over het algemeen beter dan NOT IN

Hoe bedoel je omdraaien?

Het kan namelijk zijn dat er ID's nog voorkomen in de laatste 7 dagen, ook al komen ze al eens voor verder daarvoor, ik wil die ID's nog behouden in dat geval

[Gomez12]

ThaStealth schreef op dinsdag 11 mei 2010 @ 17:25:
[...]
Hoe bedoel je omdraaien?

Het kan namelijk zijn dat er ID's nog voorkomen in de laatste 7 dagen, ook al komen ze al eens voor verder daarvoor, ik wil die ID's nog behouden in dat geval

Ok, als dat ook een eis is dan zie ik zo snel geen echt andere manier om de query te schrijven. Wel vreemd voorbeeld dan met record 6,7,8 die gelijk zijn maar ok.

Dan zou ik eens naar je explain / db gaan kijken...
Ik heb geen idee wat het aantal positions per week is wat erbij komt, maar normaal gesproken zou ik zeggen dat die subquery gewoon in het geheugen blijft staan waardoor het enkel een not in [lijst in geheugen] zou moeten zijn.

Ik zie zosnel niet hoe 100.000 id's niet meer in het geheugen passen.

Mogelijke vertragingen kunnen zijn :
- Je subquery past niet in het geheugen en wordt elke keer opnieuw gedraaid
- Je db weigert je subquery te cachen op currentdate en voert hem steeds opnieuw uit ( probeer currentdate eens als vaste waarde mee te geven )
- Je hebt een baggerzooi aan indexen staan op de positions tabel