[SQL] performance: tabellen van buiten grouped subquery

[Verwijderd]

Ik heb een performance probleem dat voortkomt uit beperkingen in de syntaxis van SQL, lijkt het.
Ik hoop dat ik het goed onder woorden kan brengen.

In mijn query wil ik een subquery doen die fijner ge-grouped is dan de hoofdquery. Dat kan alleen als ik de subquery in de FROM clause zet. En dat heeft weer als gevolg dat ik alle tabellen en joins, die al in de FROM clause van de hoofdquery staan, nog een keer moet opnemen in de FROM clause van de subquery.

Dat betekent dat de query als het ware twee keer gedaan moet worden, met dus een performance verlies.

edit:
Ik vermoed dat het sneller is als het lukt om de subquery in de SELECT clause te krijgen, maar dan zou mijn resultaat dus verder ge-grouped moeten worden dan ik wil.

Hier is de query, als er behoefte is, kan ik wel testdata posten.
Ik gebruik PostgreSQL 8.1

SQL:

select distinct c.naam, count(distinct a.txt), count(btjes.i)
from c left outer join a on c.ia = a.i 
    left outer join (select a.i, count(b.k) as cnt 
    from b, a
    where
    a.i = b.i
    group by a.i
    having count(b.k) >= 3
    ) as btjes on btjes.i = a.i
group by c.naam
order by c.naam

Dit is natuurlijk een versimpelde weergave, nu is er geen overlap meer in de joins die ik doe. In werkelijkheid heb ik in de subquery een aantal tabellen en velden nodig, die ik in de hoofdquery al heb. Maar om het begrijpelijk te maken heb ik het gereduceerd tot waar de crux zit.
Eigenlijk zou ik dus liever dit doen, maar dat kan niet, omdat ik in de subquery group op a.i, en in de hoofdquery niet:

SQL:

select distinct c.naam, count(distinct a.txt), 
        (count(*) from (select a1.i, count(b1.k) as cnt 
        from a1, b1
        where   a1.i = b1.i
        group by a1.i
        having count(b1.k) >= 3
        ) as btjes 
from c c1 left outer join a a1 on c1.ia = a1.i 
left outer join b b1 on b1.i = a1.i
group by c1.naam
order by c1.naam

Heeft er iemand tips??

[ Voor 32% gewijzigd door Verwijderd op 21-07-2006 08:59 ]

[Verwijderd]

Na 3 pogingen om jouw query te verbeteren kwam ik tot de conclusie dat ze hier stored procedures voor hebben uitgevonden...
Die 'having' doet 'm de das om...
Als ik je query goed begrepen heb (b.k doet niks, omdat nergens op de waarde daarvan gecheckt wordt), is m'n beste (of minst beroerde) poging:

SQL:

select c.naam, 
       (select count(distinct a.txt) from a where a.i = c.ia),
       (select count(*) from b
        where b.i = c.ia 
        and (select count(*) from b b2
             where b2.i = b.i) >= 3)
from c 
group by c.naam

Maar hoe efficient dat zal zijn met die 2 geneste subselects op b is maar de vraag...
Overigens niks getest, en weet niet of PostgreSQL deze query slikt.
Ik hoop trouwens wel dat je een index op a.txt hebt, en dat a.txt een fixed length string is (in MSSQL termen een char i.p.v. een varchar), anders is 'count(distinct a.txt)' al een prima manier om je performance om zeep te helpen

[ Voor 3% gewijzigd door Verwijderd op 20-07-2006 21:09 ]

[DND_Roche]

Ik zit te denken of een view (als dat al ondersteunt wordt door PostgreSQL) kan helpen.
Als je de subselect met de having-clausule als view implementeerd, zal dat helpen?
In feite moet hij nog steeds die tabellen door... Ik weet het niet zeker maar je kan het proberen...

(Overigens de distinct c.naam is niet nodig, je groupt al op c.naam)

[Verwijderd]

Afterlife, dat kan juist niet:
"ERROR: subquery uses ungrouped column "c.ia" from outer query"

Om het te versimpelen:

SQL:

select c.naam,  (select count(*) from b where b.i = c.ia)
from c
group by c.naam

Je gebruikt hier al een fijnere grouping in de subquery dan in de hoofdquery.
Dat kan blijkbaar alleen als de hoofdquery niet geGROUPt is.

Hier mag het namelijk wel:

SQL:

select distinct b.i ,(select count(*) 
        from b b2
        where b2.i = b.i) as foo
from b

Maar de performance daarvan tegen de gewone grouped variant is er slecht (cost = 15.41 tegen 1.33)
Met de gewone grouped variant bedoel ik dan:

SQL:

select b.i, count(*)
from b
group by i

Hoe kan het gebruik van een stored procedure daarbij helpen dan?

[ Voor 79% gewijzigd door Verwijderd op 21-07-2006 09:53 ]

[Verwijderd]

Hoe kan het gebruik van een stored procedure daarbij helpen dan?

Met een stored proc (of een FUNCTION, want ik kon zo snel geen verwijzingen naar stored procs in de PostgeSQL documentatie vinden) kun je veel efficienter de gegevens bijelkaar sprokkelen: eerst de gegevens uit c (c.naam, c.ia group by c.naam, c.ia), daar vervolgens de gegevens uit a en b bijhalen waarbij je die beide counts in een variabele bijhoudt, en wanneer je bij de laatse c.naam/c.ia combinatie voor die naam bent (dus wanneer de volgende naam anders is of je aan 't eind van de query op c bent) geef je je resultaat als record variabele terug aan de client.
In PostgreSQL gaat dat met RETURN NEXT zag ik.

Een cost van 1.33 zul je er niet mee halen, maar ik maak me sterk dat je de performance die je nu hebt minimaal kunt verdubbelen.
Bovendien is een netjes geschreven SP / FUNCTION ook veel beter leesbaar en onderhoudbaar dan een lastige query waarbij je je in allerlei bochten moet wringen om het gewenste resultaat te krijgen.