Selecteer kolommen om over te halen naar SQL Server vanaf Exact Online

Vragen
, ,
4

Als ik goed lees dan zie ik twee wensen:

  • verticaal filteren: minder data overhalen van 1 tabel binnen 1 administratie/partitie.
  • horizontaal filteren: minder kolommen overhalen van 1 tabel.

Op dit moment kan dit wel met Invantive Data Replicator, maar het vereist het aanmaken van een tabel in-memory met bijvoorbeeld:

create or replace table vert_en_horz_gefilterd@inmemorystorage
as
select column1
,      column2
,      ...
,      column50
--
-- Filter horizontaal.
-- Sla resterende kolommen over: column51...column75.
--
from   exactonlinerest..salesinvoicesbulk
--
-- Filter verticaal: deze of vorige maand gemaakt.
--
where  created >= add_months(trunc(sysdateutc, -1), -1)

Standaard worden in-memory tabellen niet gematerialiseerd als je ze uitleest, met andere woorden:

select count(*)
from   vert_en_horz_gefilterd@inmemorystorage

geeft je alleen het aantal rijen terug, maar werkt de replica niet bij. In-memory tabellen worden vanuit de replicatiegedachte beschouwd als extreem snel en betrouwbaar, zodat replicatie niet nodig is.

Je kunt het echter wel forceren met een Invantive SQL ods hint:

select /*+ ods(true, interval '0 seconds') */
       count(*)
from   vert_en_horz_gefilterd@inmemorystorage

De uitleg van de ods hint vind je op bij de grammatica.

De query duurt nu iets langer omdat hij de replica in de backing database (meestal SQL Server) wegschrijft.

Zijn er ook nadelen verbonden aan deze aanpak?

Ja, afhankelijk van je toepassingsgebied. Het grootste nadeel vind ik zelf dat als je meerdere partities/administraties hebt, dat dan alle administraties opeens 1 partitie worden. De in-memory tabel is niet gepartitioneerd. Je hebt dan wel netjes een division kolom bijvoorbeeld op Exact Online, maar de data wordt in zijn geheel vervangen voor alle partities tegelijk. Als je bijvoorbeeld 500 partities tegelijk zo behandelt, dan betekent dit ook dat 1 falende partitie de verversing van de rest tegenhoudt. Voor ondernemers met enkele tientallen administraties zal dat geen punt zijn, maar een groter kantoor kan daar best last van hebben.

Daarnaast moet de in-memorytabel ook in het geheugen passen. Invantive SQL draait meestal in 64-bit, maar in-memory tabellen veel groter dan enkele tientallen gigabytes leveren vaak issues op omdat op onderdelen bijvoorbeeld 1 veld de inhoud nog steeds niet groter dan 2…4 GB kan worden. Meestal valt dit probleem wel mee; Invantive SQL heeft een erg agressief algoritme om binnen resultsets data te delen en comprimeren (zowel binaire data als teksten), waardoor het geheugenbeslag vaak bizar laag is voor grote resultsets.

Tenslotte heb ik zelf met deze wijze van werken alleen ervaring op tabellen met het copy scenario. Trickle loading wordt lastig. Misschien dat het kan, maar ik heb het zelf nooit uitgewerkt op een serieus productiesysteem.

De beschreven wijze van werken zie ik wel vaak bij platformen die veel tabelfuncties hebben, zoals NMBRS, Loket of Visma net. In Invantive SQL worden dan vaak views gemaakt met daarin Invantive SQL queries of helemaal handgeschreven algoritmes, maar je komt vaak niet om een losse SQL heen. Ik verwacht dat we op enig moment user-defined views gaan toevoegen die dan bewaard worden in een data dictionary, maar op dit moment zijn alle views hard-coded of ze staan in een startup SQL zoals op Invantive Cloud.

Mocht niet zozeer de functionaliteit vooropstaan, maar de hoeveelheid storage nodig op de backing database, dan kun je ook kiezen voor databasecompressie. De inhoud van de dcd en dcs is zo gekozen dat je bijvoorbeeld op SQL Server compressie kunt aanzetten. Dit is ook een van de redenen waarom we zo vaak proberen een maximumlengte op een veld te zetten, ook al is er in de API-specificatie niks over beschreven.

Meer uitleg in het Engels is te vinden in Limit columns or rows replicated to SQL Server.

Elders kun je meer lezen over SQL Server (pagina)compressie.

In het algemeen is het advies dus:

  • Gebruik zoveel mogelijk standaardfeatures inclusief hard-coded views; dat is goedkoop, betrouwbaar en schaalt goed.
  • Als dat te veel ruimte kost, gebruik dan native database compressie.
  • Als dat niet goed genoeg werkt, gebruik dan een in-memorytabel met query.
  • Als dat niet goed genoeg werkt, schrijf dan de replicatie met features zoals Invantive PSQL, synchronize, union distinct on.

Je kunt onderaan de lijst steeds meer delen van het proces beïnvloeden, maar het kost ook steeds meer arbeidsuren en onderhoud.

M.b.t. WarehouseTransferLines gebaseerd op de XML-API adviseer ik: