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

Hallo mede Invantive gebruikers,

Wij laden elke nacht tabellen van tientallen administraties in SQL server met de Invantive data Replicator. Op dit moment wordt van elke tabel die we overhalen ook elke kolom overgehaald. De databases beginnen inmiddels aardig te groeien terwijl een groot deel van de data onbruikbaar is.

Liever halen we van de benodigde tabellen ook alleen de benodigde kolommen over. Momenteel haalt deze alles over. Zijn er mensen die hier ervaring mee hebben? Wij gebruiken nu een SQL Script die er min of meer als volgt uit ziet (uitgeklede versie uiteraard):

insert into xxdru_desired_tables@inmemorystorage
( full_qualified_name
, partition_filter
, partition_code
)
values
( ( 'ExactOnlineREST.System.SystemDivisions', 'SPECIFIC', 'DEFAULT' )
, ( 'ExactOnlineREST.HRM.Costunits', 'SELECTED', null)
, ( 'ExactOnlineREST.HRM.Costcenters', 'SELECTED', null)
)

Ik hoor graag of andere gebruikers hier ervaring mee hebben en hoe jullie dit doen.

Alvast bedankt!

Hallo @mvink ,

Wij doen dit op dezelfde manier zoals jij dit ook beschrijft.
De hoeveelheid van kolommen is niet anders, daar heb ik geen moeite mee, dit is geen probleem.
Wat mij mooier/beter/efficiënter lijkt is dat de informatie die iedere nacht opgehaald wordt gefilterd kan worden op boekjaar of dat er bijvoorbeeld alleen onverwerkte boekingen volledig hoeven worden opgehaald.
@guido.leenders Wellicht dat dit ook in het script ingesteld kan worden?

Ik ben benieuwd naar de reactie van Guido.

@m.holtrop bedankt voor je reactie ik ben inderdaad benieuwd hoe Guido dit ziet.

@guido.leenders ik loop wel tegen het volgende aan: ik wil bijvoorbeeld de tabel WarehousetransferLines overhalen, maar deze heeft meer dan 1000 kolommen. Dit zorgt voor problemen. Hoe kan ik deze toch overhalen?

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.

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: