PL/SQL performance subqueries of hulptabellen

[ErikRo]

Ik werk net 2 weekjes met toad/SQL, heb helaas alleen select rechten, geen handleiding, krijg voorlopig ook geen cursus, maar wel een standaard Oracle boek (helaas engels) om het zelf te leren. Maar het is leuk werk dus ach we proberen het.
Sorry als ik allemaal domme vragen stel.

Wat is de beste manier om snelheid hoog te houden subqueries of hulptabellen?

Het valt me op dat er nogal eens verschillen in opvraagtijden kunnen optreden tot wel vele minuten (db bevat div tabellen van 20.000.000 records).
Type ik te zoeken waarde in een select statement, geen probleem binnen 1 sec, de bijbehorende regel.
bv select A,B,C from X where A=1

Maar daar heb ik dus geen zin in want het gaat soms om een behoorlijk aantal uit te zoeken dingen.
Dus ik dacht slim te zijn door hem te verwijzen naar "tabel.veld" of een subquerie.
Maar dan lopen de tijden dus echt drastisch op.
bv
Select naam, adres, woonpl
From NAW, probleemgevallen
Where naam in (probleemgevallen.veld)

of een
Select naam, adres, woonpl
From NAW
Where naam in
(
Select naam
From probleemgevallen
Where typefout = '1'
)

Soms heb ik pech en moet ik eerst de waarde geschikt maken om mee te zoeken/koppelen. Bv door hem met een substr(probleemgevallen,27,8) uit een tekstveld regel te knippen, "Er is een fout opgetreden met 123456789".
Soms moet ik daar ook nog to_char of een to_number over heen gooien en dat soort grappen kosten je vaak je aanwezige indexen heb ik gehoord.

Maar goed om op de vraag terug te komen.
Is het slimmer om met een eerste selectie de waarden te knippen/converteren en in een aparte tussentabel neer zetten en dan een tweede selectie en in die where te verwijzen naar alle waarden in de hulptabel1.helpveld1 uit querie 1?

Of is het gebruik van subqueries juist aan te raden/sneller?

Of is er een derde betere manier?


En nu we toch bezig zijn.
Vandaag probeerde ik mijn eerste join aan te leggen met de
select tabel1.veld, tabel2.probleemgevallen
from tabel1, tabel2
where tabel1.veld = substr(tabel2.probleemgevallen,27,8) die heb ik na 15 minuten draaitijd maar gekild.

Een andere poging met
select a, b, c
from Tabel1 LEFT JOIN Tabel2 ON Tabel1.veld = Tabel2.veld
where x=y
Werd niet geaccepteerd terwijl dit volgens mij toch goed moet zijn. Velden waren allebei numeriek (10)
De LEFT JOIN kan ook INNER JOIN geweest zijn. Ik doe dit nu uit mijn hoofd, maar ik heb 1 uur besteed aan alle varianten en combies. Uiteindelijk heb ik het maar opgegegeven tot morgen. Ik ga nu

[Anoniem: 3057]

De performance van een query hang van veel dingen af. Een van de meest dramatische invloeden op de performance van een querie is of de kolommen die gebruikt worden om te joinen of in een where conditie een index hebben. Lees ook het stukje in de FAQ over indexen om je een beter beeld te geven. Indexen werken echter niet meer als je een bewerking op de waarde uit een kolom uitvoert voordat je gaat gebruiken in een join of where clause.

Verder is de grote van de hoeveelheid data die uit een query terugkomt van belang voor wat efficienter is: een temporary table of een subquery. By zulke grote resultaten dat je geheugen gaat vollopen is een temporary table zeker aan te raden, bij kleine hoeveelheden gegevens is een subquery beter.

HTH

[mbravenboer]

MrX: By zulke grote resultaten dat je geheugen gaat vollopen is een temporary table zeker aan te raden, bij kleine hoeveelheden gegevens is een subquery beter.

Ik denk dat een zich zelf respecterend database systeem toch wel zelf zou moeten kunnen beslissen of een tussen-resultaat in het gebeugen opgeslagen moet worden of dat deze tijdelijk opgeslagen moet worden op schijf. Hierbij kunnen statistieken helpen.

Overigens is het voor veel operaties zoals de select en de project geeneens nodig dat de volledige relatie in het geheugen staat: deze werken in eenvoudige gevallen per tuple (niet altijd dus).

Het voordeel van sub-queries is dat je de keuze aan het DBMS overlaat: als een opgeslagen tussenresultaat efficient is, kan het DBMS hiervoor kiezen. Als er een andere oplossing is, waarbij tijdelijke opslag niet noodzakelijk is, kan hier ook voor gekozen worden.

Uiteraard kan een DBMS verkeerde keuzen maken en dat kan misschien in specifieke gevallen vrij ernstige gevolgen hebben. Het afdwingen van een bepaalde aanpak kan dan nodig zijn.

Je kan ook nog overwegen om serialized views te gebruiken als een bepaalde query heel vaak gebruikt wordt in andere queries. Als je een echt goed DBMS hebt, kan je hier een view van maken, die geserializeerd of in ieder geval geindexeerd kan worden. Je kan dan op deze view werken in plaats van met de sub-query, terwijl je nu ook weer niet direct een temporary table gebruikt.

Overigens heb je hiervoor natuurlijk wel wat meer rechten nodig, maar als je echt serieuze queries uit moet gaan voeren neem ik aan dat je toch wel contact op kan nemen met de database administrator om het een en ander te regelen als dat de load serieus zou kunnen beinvloeden...

[jochemd]

Op donderdag 11 juli 2002 23:50 schreef ErikRo het volgende:

Maar goed om op de vraag terug te komen.
Is het slimmer om met een eerste selectie de waarden te knippen/converteren en in een aparte tussentabel neer zetten en dan een tweede selectie en in die where te verwijzen naar alle waarden in de hulptabel1.helpveld1 uit querie 1?

Of is het gebruik van subqueries juist aan te raden/sneller?

Of is er een derde betere manier?

Subqueries en IN's moet je vernijden. Die zijn veel slechter te optimaliseren voor de database engine. Als het kan overal joins gebruiken.

Voor de rest kan je het beste even in je hansleiding de werking van het EXPLAIN commando opzoeken, dan worden veel dingen vanzelf duidelijk.

[mbravenboer]

jochemd: Subqueries en IN's moet je vernijden. Die zijn veel slechter te optimaliseren voor de database engine. Als het kan overal joins gebruiken.

Vrijwel elke database die subqueries ondersteunt, zet deze om naar joins of cartesische producten met selecties en projecties. Veel databases gebroken hierdoor eigenlijk vrijwel nooit echt subqueries in het executie-plan.

De term subqueries is hier trouwens behoorlijk verwarrend omdat eigenlijk elke serieuze relationele algebra expressie wel enkele subqueries gebruikt. Het gaat bij de problematische subqueries slechts om de queries in een conditie van een select expressie. Deze kunnen dus echter in vrijwel alle gevallen prima omgezet worden naar normale joins.

Je bezorgd dus in feite alleen de query compiler/optimizer wat meer werk. Als een subquery een duidelijkere oplevert zou ik hem dus zeker niet gaan herschrijven naar een JOIN. Als je een query voorgecompileerd gebruikt valt het nadeel van een lastigere optimalisatie zelfs helemaal weg en is een herschrijving met het doel om de optimizer te helpen dus helemaal zonde van je tijd .

[ACM]

Op vrijdag 12 juli 2002 00:45 schreef mbravenboer het volgende:
Je bezorgd dus in feite alleen de query compiler/optimizer wat meer werk. Als een subquery een duidelijkere oplevert zou ik hem dus zeker niet gaan herschrijven naar een JOIN. Als je een query voorgecompileerd gebruikt valt het nadeel van een lastigere optimalisatie zelfs helemaal weg en is een herschrijving met het doel om de optimizer te helpen dus helemaal zonde van je tijd .

Idd, ofwel ze worden naar dezelfde parse-tree omgezet, maar je loopt zelfs kans dat de subquery naar een efficientere parse-tree omgezet wordt

Zelfde grapjes met left en right joins, ze leveren vaak eenzelfde (of zelfs dezelfde) parse-tree op, dus 'why bother?'

Hiervoor vindt ik overigens postgresql een erg goede database, die niet alleen de geanalyzeerde query kan uitleggen, maar desgewenst het complete query-plan, de parsetree etc kan loggen/tonen.

Oracle moet dat ook kunnen, maar het is me nooit echt duidelijk geworden in het korte tijdsbestek dat ik ermee werkt, hoe dat moest

(waarbij ik met 'parse-tree' natuurlijk niet alleen de parse-tree bedoel, maar ook de complete backend representatie van de query en het uit te voeren 'object' )

[jochemd]

Op vrijdag 12 juli 2002 00:45 schreef mbravenboer het volgende:

Vrijwel elke database die subqueries ondersteunt, zet deze om naar joins of cartesische producten met selecties en projecties. Veel databases gebroken hierdoor eigenlijk vrijwel nooit echt subqueries in het executie-plan.

Dat hangt er maar vanaf hoe ingewikkeld de subquery is. Als je bij een vertaling van een subquery naar een join wat moet gaan spelen met een GROUP BY en een HAVING is mijn ervaring dat een database dat zelf minder goed kan. Het helpt natuurlijk ook dat je meer van de betekenis van de data afweet

Op vrijdag 12 juli 2002 00:49 schreef ACM het volgende:

Idd, ofwel ze worden naar dezelfde parse-tree omgezet, maar je loopt zelfs kans dat de subquery naar een efficientere parse-tree omgezet wordt

Jij als PostgreSQL gebuiker zou beter moeten weten. Kijk alleen maar eens naar het verschil tussen een IN en een EXISTS, die in heel veel gevallen een equivalente parse-tree zouden kunnen opleveren, maar dat niet doen.

[ACM]

Op vrijdag 12 juli 2002 01:41 schreef jochemd het volgende:
Jij als PostgreSQL gebuiker zou beter moeten weten. Kijk alleen maar eens naar het verschil tussen een IN en een EXISTS, die in heel veel gevallen een equivalente parse-tree zouden kunnen opleveren, maar dat niet doen.

Nee, IN en EXISTS zijn ook 'heel' andere dingen
Die je zeker niet altijd naar elkaar kan herschrijven. (toch? )
Hoe goed de optimiser dat naar elkaar om weet te schrijven kan natuurlijk ook nog van de optimiser afhangen

Maar een subquery is om te schrijven naar een query op temptables/whatever en een left join is om te schrijven naar een right join.

[mbravenboer]

jochemd: Dat hangt er maar vanaf hoe ingewikkeld de subquery is.

Eigenlijk hangt het niet zozeer af van de complexiteit van de subquery, maar meer van het punt of er een correlatie is tussen de subquery en de query waarin deze zich bevindt. Er zijn dan attributen in de subquery die van buiten de query zelf komen. Hierbij is de herschrijving een stuk ingewikkelder, maar nog steeds goed en systematisch mogelijk. Ik ben zelf op dit moment met deze herschrijving bezig, dus vandaar dat ik de details van deze transformatie ken. Bij niet correlerende gevallen is er helemaal geen probleem en maakt de compelexiteit van de subquery eigenlijk helemaal niets uit. Alle zooi hangt dan lekker dieper in de relationeel algebra expressie en is niet relevant voor het elimineren van de subquery zelf.

Als er echt veel correlaties zijn, kan het inderdaad wel vrij lastig worden en ik me voorstellen dat een query compiler dan besluit om een relationele expressie toe te staan in de conditie.

Jij als PostgreSQL gebuiker zou beter moeten weten. Kijk alleen maar eens naar het verschil tussen een IN en een EXISTS, die in heel veel gevallen een equivalente parse-tree zouden kunnen opleveren, maar dat niet doen.

Een IN conditie is een vergelijking met een bepaalde waarden en kan in feite gedesugared worden naar een = ANY( ..) conditie. Bij de EXIST is deze vergelijking niet nodig en hierdoor heb je toch echt een probleem minder, misschien dat de expressies daarom verschillen ... Wel wordt het ook hier weer complexer als er een correlatie is.

Even puristisch geneuzel: parse-trees zijn het resultaat van het parsen van een stukje concrete syntax en bevatten bijvoorbeeld ook literals en dergelijke. Elke (niet source to source) transformatie of compiler zet een parse-tree gelijk om naar een abstract-syntax-tree... Er wordt dus in de rest van het component gewerkt met een AST en niet met een parse-tree.
</puristisch-geneuzel (sorry )>

[jochemd]

Op vrijdag 12 juli 2002 01:57 schreef ACM het volgende:

Nee, IN en EXISTS zijn ook 'heel' andere dingen
Die je zeker niet altijd naar elkaar kan herschrijven. (toch? )

Als ik kon bewijzen dat het altijd kon had ik de recente discussies op pgsql-general wel beslecht en eeuwige roem verworven

Maar in een aantal simpele gevallen kan je best een IN vervangen door een EXISTS. Als ik het schema goed heb begrepen zal onderstaande query een gelijke resultset hebben als het origineel van ErikRo. Misschien kan je nog een EXPLAIN ANALYZE doen

code:

SELECT  naam, adres, woonpl
FROM    NAW
WHERE EXISTS
    (
    SELECT  naam
    FROM    probleemgevallen
    WHERE   typefout = '1'
    AND     NAW.naam = probleemgevallen.naam
    )

[jochemd]

Op vrijdag 12 juli 2002 02:07 schreef mbravenboer het volgende:

Een IN conditie is een vergelijking met een bepaalde waarden en kan in feite gedesugared worden naar een = ANY( ..) conditie. Bij de EXIST is deze vergelijking niet nodig en hierdoor heb je toch echt een probleem minder, misschien dat de expressies daarom verschillen ... Wel wordt het ook hier weer complexer als er een correlatie is.

Is er in bovenstaand voorbeeld een reden waarom beide queries niet op de zelfde query tree uit zouden mogen/kunnen komen?

[mbravenboer]

jochemd: Maar in een aantal simpele gevallen kan je best een IN vervangen door een EXISTS.

Inderdaad, maar dan introduceer je dus een correlatie in de EXIST subquery. Dit zorgt eigenlijk voor een nog lastigere situatie dan wanneer je gewoon de IN subquery zou behouden. Optimalizerend zal het waarschijnlijk niet zijn, uiteraard is het wel een leuke relatie .

[mbravenboer]

jochemd: Is er in bovenstaand voorbeeld een reden waarom beide queries niet op de zelfde query tree uit zouden mogen/kunnen komen?

Nee, voor zover ik het kan overzien niet, maar dat het in 1 situatie equivalent kan zijn, zegt natuurlijk helemaal niets over de algemene relatie tussen de twee conditionele expressies .

[Anoniem: 32233]

code:

ipv
SELECT  naam, adres, woonpl
FROM    NAW
WHERE EXISTS
    (
    SELECT  naam
    FROM    probleemgevallen
    WHERE   typefout = '1'
    AND     NAW.naam = probleemgevallen.naam
    )

gewoon:

SELECT naw.naam
,   naw.adres
,   naw.woonpl
FROM   naw naw
,   probleemgevallen pv
WHERE  naw.naam = pv.naam
AND    pv.typefout = '1'

Lijkt mij sneller te werken.

[Anoniem: 14124]

Op vrijdag 12 juli 2002 10:38 schreef SGBas iets totaal onrelevants:

Eerst lezen dan pas bl44ten, daar ging het helemaal niet over. Ik kan me voorstellen dat de lappen tekst van mbravenboer vermoeiend zijn om te lzen, maar toch

[ErikRo]

Dank, dank, voor de hele reacties.
Gezien de vele meningen ben ik zo gek geweest om elke variant te testen. Ik wilde bovendien ook wel eens weten hoeveel e.e.a. scheelde t.o. elkaar.
Tabel 1 bevat 22.5 miljoen records veld was geindexeerd, tabel 2 bevat 2 records en was niet geindexeerd. Het te koppelen veld was (helaas) niet uniek en kwam meerdere keren voor.

Wat heb ik allemaal getest?
1. hulptabel met joins (met en zonder stringbewerking),
2. subqueries inc
3. subqueries met de exists variant.

Bovendien heb ik ook meegetest
select x
where x=y
net zoals
select y
where y=x
+ distincts in de subquerie, etc.


Het testrapport zal ik jullie besparen, maar ik zal een kort verslag geven. Het is niet wat je noemt een wetenschappelijke test. Dus ik hoop niet dat ik meteen hierop wordt aangevallen. Ik hoopte jullie hier ook een plezier mee te doen.

de winnaar was nummer 1 (het join type).
De geindexeerde tabel links zetten scheelde zelfs iets, maar niet veel (5 a 10 sec op 3 min). terwijl Oracle dat allemaal scheen te doen.
Zoals te verwachten was nam de tijd toe met een stringbewerking in de where X=(to_char)Y als (to_char)X=Y.
Met 12 seconden in ene geval en 1.05 min in het andere geval.

Tweede was de subquerie methode 3.40min en 5.15min.

Derde de subquerie + exists voorwaarde, na 6.04 stopte hij ermee omdat het virtueel geheugen (c schijf) op was.
Maar wat ik wel gek vond, was dat hij bij het uitvoeren mijn cancel/onderbrekings mogelijkheid binnen TOAD weghaalde. Zodat ik een foute opvraging TOAD moest killen, erg fijn!! Is hier een oplossing/verklaring voor???

Als ik weer een leuke vraag heb, weet ik waar ik voortaan moet wezen.

groet,
Erikro

ps de allerallersnelste mogelijkheid is trouwens met querie zoeken en de daar gevonden waarde(n) als harde tekst overtypen in de where typen b.v. X in (1,2,3) of X=1.
Raar maar Waar, zoektijd max 2 seconden.
Wie niets te doen heeft, mag me dit eens uitleggen.

[jochemd]

En wat is de EXPLAIN output van de verschillende opties?

[ErikRo]

Helaas, Ik heb het wel geprobeerd maar ik kan ook geen explain uitvoeren, dan moet er volgens toad weer eerst een script gedraaid worden met beheerdersrechten. Die heb ik niet en die krijg ik ook niet.
Ze vinden het al zo erg dat wij TOAD gebruiken, ook al hebben we alleen leesrechten. Dan nog zijn ze bang dat we de database om zeep helpen. En dan kan dus volgens mij niet.

[justmental]

Op vrijdag 12 juli 2002 16:51 schreef ErikRo het volgende:
Helaas, Ik heb het wel geprobeerd maar ik kan ook geen explain uitvoeren, dan moet er volgens toad weer eerst een script gedraaid worden met beheerdersrechten. Die heb ik niet en die krijg ik ook niet.
Ze vinden het al zo erg dat wij TOAD gebruiken, ook al hebben we alleen leesrechten. Dan nog zijn ze bang dat we de database om zeep helpen. En dan kan dus volgens mij niet.

Ik weet niet wat er verder nog op die database gebeurt, maar een beetje fout opgezette query kan het voor een hoop gebruikers aardig verpesten.

[ErikRo]

OK, maar dan bedoel je draaitijd neem ik aan?
Waar ik het over heb is dat ze bang dat (een deel) van de records fysiek beschadigen of de gegevens van inhoud verandert.

[justmental]

Op vrijdag 12 juli 2002 17:08 schreef ErikRo het volgende:
OK, maar dan bedoel je draaitijd neem ik aan?
Waar ik het over heb is dat ze bang dat (een deel) van de records fysiek beschadigen of de gegevens van inhoud verandert.

Dat voorkom je idd. door alleen leesrechten te geven.

Je zou bijvoorbeeld de eerste niet zijn die niet wist dat een truncate een impliciete commit doet

[ErikRo]

Ik zou ook inderdaad geen volledige schrijf cq verwijderrechten willen. Kan ik ook de schuld niet krijgen als zelf wat fout doen.

Maar volgens mij het ook mogelijk zijn mensen zoals ik, create/wijzig cq verwijderrechten op temp en eigen tabbellen (alleen voor mij zichtbaar) te geven.
Daardoor kan ik juist draaitijd verminderen.
Bv door verder te werken met een extract wat ik in een kleine tabel zet.

[justmental]

Op vrijdag 12 juli 2002 17:28 schreef ErikRo het volgende:
Ik zou ook inderdaad geen volledige schrijf cq verwijderrechten willen. Kan ik ook de schuld niet krijgen als zelf wat fout doen.

Maar volgens mij het ook mogelijk zijn mensen zoals ik, create/wijzig cq verwijderrechten op temp en eigen tabbellen (alleen voor mij zichtbaar) te geven.
Daardoor kan ik juist draaitijd verminderen.
Bv door verder te werken met een extract wat ik in een kleine tabel zet.

Om het zo in te richten dat je rechten hebt om een tabel met indexen te maken zonder de rest te kunnen verzieken is meestal een hoop werk.
Misschien kun je wel een eigen kopietje van de database krijgen waar je meer rechten op hebt?

Overigens heb je zo'n tijdelijke tabel vrijwel nooit nodig.
Met PL/SQL kun je voor een moeilijke zoekactie je query's opdelen in losse cursoren.

Ook kun je bijvoorbeeld gebruik maken van collections.

[ErikRo]

Vertel...

Volgens mij mag ik dit allemaal niet. Ik heb alleen verbinding via toad op een "view" en daarop heb ik geloof ik alleen selectrechten.
Cursors lijkt me meer iest voor mensen met meer rechten. Maar verras me eens

[justmental]

Op vrijdag 12 juli 2002 17:48 schreef ErikRo het volgende:
Vertel...

Volgens mij mag ik dit allemaal niet. Ik heb alleen verbinding via toad op een "view" en daarop heb ik geloof ik alleen selectrechten.
Cursors lijkt me meer iest voor mensen met meer rechten. Maar verras me eens

Als jij rechten hebt om Selects uit te voeren dan kun je ook PL/SQL gebruiken.
PL/SQL is een 3e generatie taal die standaard in Oracle zit gebouwd.
Een cursor is gewoon een SQL statement die je vervolgens rij voor rij kunt doorlopen en waarmee je bijvoorbeeld de resultaten die je ophaalt weer in een andere cursor kunt gebruiken.
Manuals staan gewoon online op http://technet.oracle.com/

[ACM]

Op vrijdag 12 juli 2002 16:13 schreef jochemd het volgende:
En wat is de EXPLAIN output van de verschillende opties?

De explain van Oracle is niet zo 'simpel' als die van postgres of mysql (die wel heeeel erg simpel is )

Btw, kan je niet een testdatabase regelen? Of is de boel daarvoor te groot en te zwaar?

[ErikRo]

Op vrijdag 12 juli 2002 17:53 schreef justmental het volgende:

[..]

Als jij rechten hebt om Selects uit te voeren dan kun je ook PL/SQL gebruiken.
PL/SQL is een 3e generatie taal die standaard in Oracle zit gebouwd.
Een cursor is gewoon een SQL statement die je vervolgens rij voor rij kunt doorlopen en waarmee je bijvoorbeeld de resultaten die je ophaalt weer in een andere cursor kunt gebruiken.
Manuals staan gewoon online op http://technet.oracle.com/

He dat is alvast goed nieuws om te horen.
Maandag ga ik daar meteen eens mee experimenteren

[ErikRo]

Op vrijdag 12 juli 2002 17:58 schreef ACM het volgende:

[..]
Btw, kan je niet een testdatabase regelen? Of is de boel daarvoor te groot en te zwaar?

Hij moet echt heel groot zijn heb ik me laten vertellen.