v() en nv() mooie functies maar….

16-09-2013 door: Kees Vlek

Inleiding

Zoals zo vaak bij het schrijven van een blog item wordt deze meestal getriggerd door een probleem waar ik tijdens het werk tegen aanloop. Ook dit item is er weer een uit die categorie.
De foutmelding die ik doorkreeg was net zo simpel als kort “De zoek opdracht op pagina xx performed niet goed”.
Ik hou wel van dit soort meldingen omdat het altijd weer een leuke uitdaging is om een performance probleem op te lossen en het resultaat direct bemerkt wordt door de klant.

Elementen betrokken bij het probleem

Apex pagina met items en diversen tabbladen (apex pagina’s in een iframe) met reports.
Db package met function

En dan begint het zoeken…

De verwachting is dat het probleem niet in de hoofdpagina met de “iframe container”, waarin de tabbladen worden geopend, zou zitten want die werkt voor de overige tabbladen prima. Daarbij komt nog dat een performance probleem “meestal” in een query zit. Uiteindelijk de query opgezocht die gebruikt wordt.

select ..
      , ..
      , its.its_oni_package.oni_radio_group_score(p_score_type =>ind.ind_score_type, .. )
   from its_onderzoek_indicatoren oni
  where ..

De query is voor de leesbaarheid versimpeld. De query heb ik naar sqldeveloper gekopieerd om wat makkelijker te kunnen testen. Als eerst maar eens die functie uit query gehaald en jawel hoor de query performde meteen prima. Weer een stapje dichter bij het probleem. Dan maar eens die functie uitpluizen. In de functie zit een cursor die gebruik maakt van de nv()functie.

select .. 
  from its_onderzoek_indicatoren oni 
       . 
       . 
  join ios_elementen elm 
    on oni.oni_one_elm_id = elm.elm_id 
 where oni.oni_one_ond_nr = nv('p10105_ond_nr') 
   and elm.elm_id         = nv('p10105_element')

Voor het gemak heb ik deze functies vervangen door bind variabelen in mijn sqldeveloper query en ik kon geen performance probleem vaststellen. huuh, hoe kan dat nu? Het blijkt dat ik het probleem al opgelost had voordat ik exact wist wat het was.

select .. 
  from its_onderzoek_indicatoren oni 
       . 
       . 
  join ios_elementen elm 
    on oni.oni_one_elm_id = elm.elm_id
 where oni.oni_one_ond_nr = :p10105_ond_nr 
   and elm.elm_id         = :p10105_element

het is duidelijk dat het gebruik van nv() functie het performance probleem creëert.

En dan nog oplossen…

Nu had ik het probleem dat ik wel wist waar het probleem zat maar hoe ga ik het nu oplossen en toch gebruik maken van deze nv() functie? Wat dacht je van een scalar subquery? De scalar subquery expression is een subquery die exact één kolom waarde van één record terug geeft. Om dit te testen is de query als volgt aangepast

select .. 
  from its_onderzoek_indicatoren oni 
       . 
       . 
  join ios_elementen elm 
    on oni.oni_one_elm_id = elm.elm_id 
 where oni.oni_one_ond_nr = (select nv('p10105_ond_nr')  from dual) 
   and elm.elm_id         = (select nv('p10105_element') from dual)

Dit heb ik voor het gemak direct in het package gedaan zodat het wat makkelijker te testen is. Vervolgens dezelfde zoekopdracht gegeven als waar ik mee begonnen ben en jawel hoor het probleem is opgelost!

Conclusie

– Indien mogelijk gebuik dan in je apex applicatie altijd bind variabelen ipv de n() en nv() functies.

– Scalar subqueries zijn een zeer handige oplossing als je de n() en nv() functies wel wil/moet gebruiken.

– Nu nog even mijn eigen applicaties controleren op het gebruik van de n() en nv() functies en aanpassen!