[SQL] subquery verbeteren/vervangen

[Verwijderd]

Ik zit met een klein performance probleem.

Ik heb 3 tabellen: A, B & C

Tabel structuur:
A
id
..
..

B
id
fk(fk naar A many-to-one)
i (de counter die gecheckt moet worden)
..


C
fk(fk naar B many-to-one)
i


Ik wil alle informatie terug krijgen van A met de volgende beperking:

- In B of C moet er 1 row zijn welke i > 0

Nu heb ik de volgende query:

code:

SELECT * FROM A WHERE A.id IN (SELECT DISTINCT B.fk FROM B LEFT OUTER JOIN C ON B.id = C.fk WHERE (B.i > 0 OR C.i > 0))

Deze query werkt wel alleen is veel te traag op grote hoeveelheden data. Heeft iemand een helder idee hoe ik de subquery zou kunnen vervangen/verbeteren?

De structuur van de database is niet te veranderen.

[Rekcor]

Heb je al eens

MySQL:

EXPLAIN SELECT * FROM A WHERE A.id IN (SELECT DISTINCT B.fk FROM B LEFT OUTER JOIN C ON B.id = C.fk WHERE (B.i > 0 OR C.i > 0))

MySQL:

EXPLAIN EXTENDED SELECT * FROM A WHERE A.id IN (SELECT DISTINCT B.fk FROM B LEFT OUTER JOIN C ON B.id = C.fk WHERE (B.i > 0 OR C.i > 0))

gedraaid? Deze geven vaak goed inzicht in wat de vertragende factoren zijn in je query. Op internet zijn er wel tutorials te vinden.

[ Voor 28% gewijzigd door Rekcor op 18-08-2011 17:35 . Reden: Query toegevoegd ]

[Remus]

Voor zover ik kan zien heb je hier helemaal geen subselect nodig en zou B LEFT JOIN C INNER JOIN A al voldoende moeten zijn. Overweeg daarnaast het gebruik van EXISTS in plaats van IN.

[Verwijderd]

@Rekcor:
Ik heb set showplan gebruikt om te kijken waar het probleem zit, maar dit zit het meest in de subselect bij de LEFT OUTER JOIN

@Remus Hoe zou je de query dan schrijven? Ik moet namelijk de resultaten uit A hebben en niet uit B. Exists vs in geeft mij niet veel performance winst

[coldasice]

kun je niet zoiets maken?

SELECT * FROM A WHERE A.id IN (SELECT B.fk FROM B WHERE (B.i > 0))
UNION
SELECT * FROM A WHERE A.id IN (SELECT C.fk FROM C WHERE (C.i > 0))

eventueel het resultaat via een SP in een temp table stoppen en filteren als dit nog nodig is....

weet je mss iets meer over de i waarden?Als die altijd 1,2,of 3 zijn kun je eventueel de > vervangen....

[GlowMouse]

Voor zover het om MySQL gaat, is de query in de OP traag omdat voor elk record in A de subquery wordt uitgevoerd. Hetzelfde geldt voor de queries van coldasice. Een query met exists zal sneller gaan. Je kunt dan nog beter in je code eerst de query achter IN uitvoeren.

Ik zou hem zo doen:
SELECT DISTINCT A.*
FROM A
LEFT JOIN B ON(A.id=B.fk AND B.i>0)
LEFT JOIN C ON(A.id=C.fk AND B.i>0)
WHERE (B.fk IS NOT NULL OR C.fk IS NOT NULL)

Met in B en C een index op (fk,i).

[Verwijderd]

@coldasice & @GlowMoudse Daar had ik ook aan gedacht maar de fk in tabel C wijst naar het id van Tabel B en niet naar het id van Tabel A

i = een int en kan dus alles zijn.

RDBMS = Miscrosoft SQL server

[coldasice]

Verwijderd schreef op donderdag 18 augustus 2011 @ 21:23:

i = een int en kan dus alles zijn.

geeft ongelijk 0 nog een kleine verbetering?

[Verwijderd]

Nee geen merkbaar resultaat, maar hij moet ook groter dan 0 zijn

[GlowMouse]

Verwijderd schreef op donderdag 18 augustus 2011 @ 21:23:
@coldasice & @GlowMoudse Daar had ik ook aan gedacht maar de fk in tabel C wijst naar het id van Tabel B en niet naar het id van Tabel A

Ah, ik snapte de query van remus al niet; nu wel. Hij gaat zo:
SELECT DISTINCT A.*
FROM B
LEFT JOIN C ON(B.id=C.fk AND C.i>0)
INNER JOIN A ON(A.id=B.fk)
WHERE B.i>0 OR C.i>0

[Verwijderd]

GlowMouse schreef op donderdag 18 augustus 2011 @ 21:37:
[...]

Ah, ik snapte de query van remus al niet; nu wel. Hij gaat zo:
SELECT DISTINCT A.*
FROM B
LEFT JOIN C ON(B.id=C.fk AND C.i>0)
INNER JOIN A ON(A.id=B.fk)
WHERE B.i>0 OR C.i>0

Tnx deze is wel een stuk sneller

[Remus]

GlowMouse schreef op donderdag 18 augustus 2011 @ 21:37:
[...]

Ah, ik snapte de query van remus al niet; nu wel. Hij gaat zo:
SELECT DISTINCT A.*
FROM B
LEFT JOIN C ON(B.id=C.fk AND C.i>0)
INNER JOIN A ON(A.id=B.fk)
WHERE B.i>0 OR C.i>0

Dat bedoelde ik inderdaad, ik had alleen geen zin in spoonfeeding Overigens kan de C.i > 0 in óf de join-condition, óf in de where-condition weg.

[ Voor 9% gewijzigd door Remus op 19-08-2011 08:26 ]

[GlowMouse]

Remus schreef op vrijdag 19 augustus 2011 @ 08:25:
[...]

Dat bedoelde ik inderdaad, ik had alleen geen zin in spoonfeeding Overigens kan de C.i > 0 in óf de join-condition, óf in de where-condition weg.

In de WHERE kan hij niet weg, want dan mis je de rijen waar C.id>0 maar B.id=0. In de join-conditie voegt hij bij MySQL toe aan de snelheid waarmee de query wordt uitgevoerd, en ik kan me voorstellen dat dat bij andere db's ook zo is.

[Remus]

GlowMouse schreef op vrijdag 19 augustus 2011 @ 12:07:
[...]

In de WHERE kan hij niet weg, want dan mis je de rijen waar C.id>0 maar B.id=0. In de join-conditie voegt hij bij MySQL toe aan de snelheid waarmee de query wordt uitgevoerd, en ik kan me voorstellen dat dat bij andere db's ook zo is.

Je hebt helemaal gelijk.