Extreem trage berekening kostprijs artikelen

Vragen
, ,
1

We gebruiken het onderstaande SQL-statement om voor verkoopanalyses de historische verkoopprijzen en te relateren inkoopprijzen te bepalen. Het was nodig om Exact anders te gebruiken qua in- en verkoopproces. Vanwege samengestelde artikelen en geografische gespreide inkoop wordt de meest recent gebruikte inkoopprijs of meest nabije inkoopprijs in de toekomst teruggezocht voor een artikel.

Langzame bepaling week laatste inkoop

Specifiek de onderstaande query is erg traag geworden bij de overgang van 20.2.129 naar 20.2.141. Per artikel geeft die aan in welke week het artikel de laatste keer gekocht is geweest.

Hoe kan dit qua prestaties weer versneld worden?

De query geeft normaliter een miljoen rijen terug in 30 seconden, maar nu duurt het langer dan een half uur (daarna afgebroken).

--
-- Opzoektabel per week wanneer de laatste keer een artikel is ingekocht.
--
create or replace table plw@inmemorystorage
as
select clr.day_date
,      plw.itemcode
,      min(plw.por_week)
       plw_por_week_min
,      max(plw.sun_in_week_date)
       plw_sun_in_week_date_min
from   CALENDAR@DataDictionary clr
join   pleweek@inmemorystorage plw
on     plw.sun_in_week_date <= clr.day_date
--
-- Optimalisatie: kijk alleen vanaf de eerste besteldatum.
--
where  clr.day_date >= ( select min(orderdate) from purchaselines@inmemorystorage )
and    clr.day_in_week = 1
--
-- Optimalisatie: kijkt niet in de toekomst.
--
and    clr.day_date <= add_months(trunc(sysdate), 1)
group
by     clr.day_date
,      plw.itemcode

SQL Verkoopanalyses

create or replace table stg@inmemorystorage
as
select to_date($X{startdate}) startdate
;
create or replace table sim@inmemorystorage
as
select /*+ http_disk_cache(true, true, interval '1 week') */
       sim.division
,      sim.item
,      sim.itemcode
,      sim.SupplierCode
,      sim.SupplierDescription 
from   supplieritems sim
where  sim.MainSupplier = true
;
create or replace table itm@inmemorystorage
as
select itm.*
,      igp.code itemgroupcode
from   itemsincremental itm
join   itemgroups igp
on     igp.id = itm.itemgroup
;
create or replace table tle@inmemorystorage
as
select to_char(tle.date, 'YYYYIW') 
       tle_week
       label 'Week'
,      to_char(tle.date, 'YYYYMM') 
       tle_maand
       label 'Maand'
,      -tle.amountdc 
       amountdc
       label 'Gefactureerde Omzet'
,      itm.itemgroupcode
,      tle.*
from   transactionlinesincremental tle
join   itm@inmemorystorage itm
on     itm.id       = tle.item
where  tle.date        >= (select /*+ low_cost */ startdate from stg@inmemorystorage)
and    tle.journalcode = '70'
and    tle.glaccount in (select /*+ low_cost */ id from glaccountsincremental where code like '8%')
and    tle.linenumber not in (0, 9998, 9999)
order
by     tle.division
,      tle.date
,      tle.invoicenumber
,      tle.linenumber
;
create or replace table sor@inmemorystorage
as
select to_char(sor.orderdate, 'YYYYIW') 
       sor_week
       label 'Week'
,      to_char(sor.orderdate, 'YYYYMM') 
       sor_maand
       label 'Maand'
,      sor.* prefix with 'sor_'
,      act.* prefix with 'act_'
from   SalesOrderLinesIncremental sor
join   AccountsIncremental act
on     act.id       = sor.OrderedBy
where  sor.orderdate        >= (select /*+ low_cost */ startdate from stg@inmemorystorage)
and    sor.linenumber = 0
;
--
-- Historische inkoopprijzen per artikel.
--
create or replace table purchaselines@inmemorystorage
as
select por.orderdate
,      por.orderdate - to_number(to_char(por.orderdate, 'D')) 
       sun_in_week_date
,      to_char(por.orderdate, 'YYYYIW') 
       por_week
       label 'Week'
,      to_char(por.orderdate, 'YYYYMM') 
       por_maand
       label 'Maand'
,      ple.itemcode
,      ple.netprice
,      ple.quantity
,      por.ordernumber
from   exactonlinerest..PurchaseOrders por
join   exactonlinerest..PurchaseOrderLines ple
on     ple.PurchaseOrderID = por.PurchaseOrderID
;
--
-- Maximum prijzen in een week waarin er inkopen geweest zijn
-- voor een artikel.
--
create or replace table pleweek@inmemorystorage
as
select itemcode
,      por_week
,      sun_in_week_date
,      avg(quantity * netprice) netpricewavg
,      avg(netprice) netpriceavg
,      max(netprice) netpricemax
,      min(netprice) netpricemin
,      count(*) cnt
from   purchaselines@inmemorystorage
group
by     itemcode
,      por_week
,      sun_in_week_date
;
--
-- Opzoektabel per week wanneer de laatste keer een artikel is ingekocht.
--
create or replace table plw@inmemorystorage
as
select clr.day_date
,      plw.itemcode
,      min(plw.por_week)
       plw_por_week_min
,      max(plw.sun_in_week_date)
       plw_sun_in_week_date_min
from   CALENDAR@DataDictionary clr
join   pleweek@inmemorystorage plw
on     plw.sun_in_week_date <= clr.day_date
--
-- Optimalisatie: kijk alleen vanaf de eerste besteldatum.
--
where  clr.day_date >= ( select /*+ low_cost */ min(orderdate) from purchaselines@inmemorystorage )
and    clr.day_in_week = 1
--
-- Optimalisatie: kijkt niet in de toekomst.
--
and    clr.day_date <= add_months(trunc(sysdate), 1)
group
by     clr.day_date
,      plw.itemcode
;
--
-- Bepaal per artikel wat de laatste bekende prijs is
-- per week. Als er meerdere prijzen zijn, dan wordt de hoogste
-- inkoopprijs genomen.
--
create or replace table approx_prices@inmemorystorage
as
select plw0.day_date
,      plw0.itemcode
,      plw0.plw_sun_in_week_date_min
,      case
       --
       -- Weighted average price since VZKI contained all kinds of 
       -- shipping products.
       --
       when plw0.itemcode = 'iets'
       then 29 /* plw1.netpricewavg */
       else plw1.netpricemax
       end
       netpriceweek
from   plw@inmemorystorage plw0
join   pleweek@inmemorystorage plw1
on     plw1.sun_in_week_date = plw0.plw_sun_in_week_date_min
and    plw1.itemcode = plw0.itemcode
;
create or replace table sle@inmemorystorage
as
select sor.*
,      sle.* prefix with 'sle_'
,      coalesce(apeb.netpriceweek, apef.netpriceweek, itm.CostPriceStandard) * sle.quantity 
       sle_costpricequantity
       label 'Kostprijs Artikelen'
,      coalesce(por.suppliercode, sim.suppliercode)
       por_suppliercode
,      coalesce(por.suppliername, sim.supplierdescription)
       por_suppliername
,      por.ordernumber
       por_ordernumber
,      itm.code
       sle_itemcode
       label 'Artikelcode'
,      itm.itemgroupcode
       sle_itemgroupcode
,      itm.costpricestandard
       sle_costpricestandardcurrent
       label 'Kostprijs Artikel Momenteel'
,      apeb.netpriceweek
       sle_costpriceapproximateb
       label 'Kostprijs Artikel Historisch Terug'
,      apef.netpriceweek
       sle_costpriceapproximatef
       label 'Kostprijs Artikel Historisch Vooruit'
,      case
       when apeb.netpriceweek is not null 
	   then 'Vorige inkoop'
       when apef.netpriceweek is not null 
	   then 'Volgende inkoop'
	   else 'Standaard kostprijs'
	   end
	   herkomst	   
from   sor@inmemorystorage sor
join   SalesOrderLinesIncremental sle
on     sle.orderid  = sor.sor_orderid
and    sle.LineNumber != 0
join   itm@inmemorystorage itm
on     itm.id       = sle.item
left
outer
join   exactonlinerest..purchaseorders por
on     por.purchaseorderid = sle.purchaseorder
and    itm.itemgroupcode   != 'post'
left
outer
join   sim@inmemorystorage sim
on     sim.item     = sle.item
left
outer
join   approx_prices@inmemorystorage apeb
on     apeb.itemcode = itm.code
and    apeb.day_date = sor.sor_orderdate - to_number(to_char(sor.sor_orderdate, 'D'))
left
outer
join   approx_prices@inmemorystorage apef
on     apef.itemcode = itm.code
and    apef.day_date = sor.sor_orderdate - to_number(to_char(sor.sor_orderdate, 'D')) + 7
;
create or replace table sorcost@inmemorystorage
as
select sor_division
,      sor_orderid
,      sor_ordernumber
,      sor_orderdate
,      sum(sle_costpricequantity)
       sor_costpricequantity
       label 'Kostprijs Artikelen'
,      min(por_suppliercode)
       por_suppliercode
       label 'Eerste Leverancier Code'
,      min(por_suppliername)
       por_suppliername
       label 'Eerste Leverancier Naam'
,      min(por_ordernumber)
       por_ordernumber
       label 'Eerste Leverancier Bestelnummer'
from   sle@inmemorystorage
group 
by     sor_division
,      sor_orderid
,      sor_ordernumber
,      sor_orderdate
;
create or replace table sorall@inmemorystorage
as
select cst.sor_costpricequantity
,      cst.por_suppliercode
,      cst.por_suppliername
,      cst.por_ordernumber
,      sor.sor_amountfcexclvat - cst.sor_costpricequantity
       margin
       label 'Bruto Marge'
,      case 
       when sor.sor_amountfcexclvat != 0
       then ( sor.sor_amountfcexclvat - cst.sor_costpricequantity )
            / sor.sor_amountfcexclvat
       else 1
       end
       margin_percentage
       label 'Bruto Marge %'
,      sor.*
from   sor@inmemorystorage sor
left
outer
join   sorcost@inmemorystorage cst
on     cst.sor_orderid = sor.sor_orderid
2

De SQL query koppelt de bestellingen uit Exact Online aan een weekkalender. Voor optimalisatiedoeleinden worden alleen kalenderdatums bekeken die groter/gelijk zijn aan de oudste bestelling. Anders zou er ook gezocht worden naar bestellingen in bijvoorbeeld 1923.

De verantwoordelijke where voor het beperken van het datumbereik is:

--
-- Optimalisatie: kijk alleen vanaf de eerste besteldatum.
--
where  clr.day_date >= ( select min(orderdate) from purchaselines@inmemorystorage )
...
--
-- Optimalisatie: kijkt niet in de toekomst.
--
and    clr.day_date <= add_months(trunc(sysdate), 1)

Het lijkt er op dat voor elke maandag de minimale besteldatum opnieuw bepaald wordt met de subquery:

select min(orderdate) from purchaselines@inmemorystorage

Dat is niet erg efficient onder versie 20.2.141; de uitkomst is tenslotte telkens hetzelfde. Normaliter heeft een query die alleen op de InMemoryStorage werkt automatisch de hint low_cost. Die low_cost hint betekent dat de SQL-optimizer er van uit gaat dat de query meteen geëvalueerd kan worden tot een constante.

Voor andere datacontainers moet die hint expliciet opgegeven worden door de SQL-bouwer, maar het had hier automatisch moeten gebeuren.

Workaround is om de query aan te passen en wel specifiek deze regel:

where  clr.day_date >= ( select /*+ low_cost */ min(orderdate) from purchaselines@inmemorystorage )

De betekenis van de Invantive SQL-hints staat beschreven in de SQL-documentatie.

Voor een volgende release zullen we proberen om het oorspronkelijke gedrag bij InMemoryStorage weer te herstellen.

3

Deze vraag is automatisch gesloten na tenminste 2 weken inactiviteit nadat een mogelijk passend antwoord is gegeven. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.