SQL tabel updaten met gewijzigde records

[Torrentus]

Hallo Tweakers,

Voor mijn stage ben ik een kritische blik aan het werpen op een datawarehouse. Kort samengevat performt het datawarehouse gewoon niet, en daar zijn workarounds omheen gebouwd om het toch werkbaar te houden. (Oorspronkelijke idee was alles rechtstreeks te bekijken via views, dat werd zo traag dat ze nu elke nacht een mega-view dumpen in een tabel en op die tabel dan weer views hebben geschreven).

Goed, ik heb de query execution paths bekeken en het valt me op dat er veel tijd verloren gaat met het sorteren van (tussen)-resultaten. Doorredenerend bleken alle tabellen ongeïndexeerd. Als reactie op mijn vraag waarom dat eigenlijk zo is, kreeg ik te horen dat als ze de tabellen zouden indexeren, de (ook) dagelijkse export uit het ERP-systeem te lang zou duren.

De dagelijkse export uit dat ERP systeem is waar mijn vraag betrekking op heeft. Dat zou te lang gaan duren met indexering omdat ze elke nacht alle tabellen leegmaken en dan weer opnieuw vullen met alle data uit het ERP-pakket. Omdat de tabel leeggemaakt wordt gaat ook de bestaande index verloren, en het elke nacht opnieuw opbouwen van die index kost blijkbaar te veel tijd.

Ik vraag me dan af of dit inderdaad de gangbare manier is om een ERP-systeem in een SQL-database te exporteren. Vanuit mijn begninersperspectief lijkt het veel logischer om de tabellen van de vorige dag níet te droppen, maar enkel aan te vullen met de nieuwe records. Daarbij moeten dan wel ook alle bestaande records aangepast worden als er zich die dag wjizigingen in zo'n record hebben voorgedaan. Een soort kopiëeren waarbij al bestaande identieke records overgeslagen worden.

Ik heb al flink gegoogled, maar kan geen werkwijze vinden om in SQL bovenstaande mogelijk te maken. Daarom uiteindelijk toch maar een topic geopend hier. Ik hoop dat iemand me kan vertellen wat de gangbare manier is om zo'n export dagelijks te doen om het datawarehouse up-to-date te houden, en of het wel/niet mogelijk is om enkel gewijzigde records te updaten, in plaats van de hele tabel opnieuw te vullen.

Edit: Soms is de oplossing zo simpel dat je hem over het hoofd ziet. Volgens mij zou je hiermee eenvoudig de nachtelijke import zo kunnen neerzetten dat hij incrementeel werkt.

^{update my_table set
my_col1 = 'newValue1',
my_col2 = 'newValue2'
where id = 'someKey'
and (my_col1 != 'newValue1'
or my_col2 != 'newValue2');} --> Slecht idee

[ Voor 7% gewijzigd door Torrentus op 11-12-2014 09:25 ]

[creator1988]

[quote]Torrentus schreef op woensdag 10 december 2014 @ 11:20:
Ik geloof er niets van dat een nieuwe index aanmaken op die tabel zo duur is. Misschien alles deleten en opnieuw invoeren terwijl je ook een index hebt, maar dan kan je de index droppen, data weggooien en weer opnieuw invoeren en nieuwe index maken.

Test dit gewoon eens op een clone van je DB. Assumption is the mother of all fuckups, etc.

[Viper®]

Ik zou eerst wat onderzoek verrichten zonder al te snel naar een oplossing toe te werken.

Doorredenerend bleken alle tabellen ongeïndexeerd. Als reactie op mijn vraag waarom dat eigenlijk zo is, kreeg ik te horen dat als ze de tabellen zouden indexeren, de (ook) dagelijkse export uit het ERP-systeem te lang zou duren.

Of iemand heeft al zaken uitgezocht en dat is de conclusie, of men roept maar iets zonder eerst onderzoek te hebben gedaan.

SQL heeft standard vrij veel tooling om zaken uit te kunnen zoeken.

Zo zou je eens kunnen kijken wat de fragmentatie van alle indexes zijn. Ook kan je ze rebuilden of re-indexen. Als dit namelijk niet gebeurd houdt de indexatie oude records vast.

D.m.v. execution plans en de profiler kan je zien wat er precise gebeurd. Betrek er anders een goede DBA'er bij

Verder zou je eens met de sql tuning wizard i.c.m. met een dump van de data richting de tabellen kunnen onderzoeken welke indexen nodig zijn.

[Torrentus]

Bedankt voor jullie antwoorden.

@Creator Goed punt, ik ga ervoor zorgen dat ik een clone van de database tot m'n beschikking krijg om e.e.a. op te testen. Het is inderdaad een aanname op basis van wat me verteld is. Ik heb zelf geen ervaring met de kosten van het opbouwen van indexes.

@Viper, je geeft aan dat ik eens zou kunnen kijken naar de fragmentatie van de indexes. Begrijp ik je verkeerd of kan ik dat niet bekijken aangezien er geen indexes zijn?

Ik heb het execution plan bekeken en zie dat er veel tijd in de sort-actie (40%) vlak voor het einde van het proces plaatsvindt. Op basis daarvan ben ik naar indexing gaan kijken.

Je suggestie om er een ervaren DBA'er bij te halen is natuurlijk een goede, dat gaan ze ook vast en zeker wel doen hier (offerte-trajecten lopen al), maar ze hebben mij de kans geboden er ook eens naar te kijken. Eventueel kunnen we mijn bevindingen aan het einde van mijn stageperiode bespreken met een ervaren DBA'er.

De SQL tuning wizard was ik nog niet tegengekomen in SMSS, bedankt voor de tip. Die draait nu.

[Viper®]

Ik zie dat er helemaal geen indexen op zitten. Zelf ben ik geen DBA maar er zijn volgens mij maar weinig scenario's waarbij geen indexen een voordeel hebben. Met indexen zal het toevoegen van data iets langzamer gaan (de index moet namelijk bijgewerkt worden). Echter als je wil gaan zoeken, sorteren etc zal een index allemaal maar sneller werken.

Het toevoegen van data in SQL en er later indexen opzetten zal bij het laden van de data misschien wat snelheidswinst geven, echter zal het aanmaken van de index weer langer duren.

Zodra je een testomgeving hebt waarmee je kan spelen (alles leeggooien, data inlezen) kan je verschillende scenario's doorlopen.

Mijn ervaring binnen verschillende bedrijven is dat er weinig mensen echt ervaring hebben met databases en hoe deze goed ingericht dienen te worden. Echte DBA'ers zijn daarom ook vrij duur.

Zoek eens op het internet, er is veel te vinden over SQL, indexatie, statistics, optimizing etc.
Leuk om er eens in te duiken en zaken uit te zoeken

https://ola.hallengren.co...atistics-maintenance.html
MSDN: Reorganize and Rebuild Indexes
http://blogs.lessthandot....build-and-reorganize-sql/

[Erwinvz1]

Viper® schreef op woensdag 10 december 2014 @ 15:27:
Ik zie dat er helemaal geen indexen op zitten. Zelf ben ik geen DBA maar er zijn volgens mij maar weinig scenario's waarbij geen indexen een voordeel hebben. Met indexen zal het toevoegen van data iets langzamer gaan (de index moet namelijk bijgewerkt worden). Echter als je wil gaan zoeken, sorteren etc zal een index allemaal maar sneller werken.

Bij een INSERT is een index vertragend....


@Torrentus
Ik heb je verhaal gelezen en is erg lastig te bepalen wat de best practise, zeker omdat we niet weten welke mogelijkheden jullie hebben m.b.t. datawarehouse en om wat voor data het gaat.

Als het om data gaat van bijvoorbeeld een dag oud, dan is het zeker niet onverstandig om dit volledig te de-normaliseren. Dus je hele view opslaan in een database en het s'nachts laten dumpen. Zo voorkom je dure joins en is je performance veel beter. Vergeet niet dat je bron tabellen wel goede index-en nodig hebben, dan is het opbouwen van je databasedump ook een stuk sneller.

Met welke tooling draai je nu je dump?
Om welke database server gaat het? Microsoft SQL?

Edit: Soms is de oplossing zo simpel dat je hem over het hoofd ziet. Volgens mij zou je hiermee eenvoudig de nachtelijke import zo kunnen neerzetten dat hij incrementeel werkt.

Ik denk dat die oplossing ook veel tijd kost, je moet namelijk alle records al lezen. Dan is het volledig dumpen misschien wel sneller.

[Verwijderd]

Waarom exporteer je niet alle data vanuit je ERP naar DWH, en ga in DWH pas checken wat nieuw is en wat niet. Je wilt imho je ERP zo min mogelijk belasten, ook met het oog op nachtprocessen.

Ik heb ook wel eens gezien dat met Materialized views gewerkt werd (aan ERP kant), en daar ging DWH vervolgens zijn import/selectie op doen. Maar hangt sterk af van je situatie. Het is niet altijd zo zwart-wit, ook niet je beslissing voor full of incremental load.

bekijk ook eens: MSDN: MERGE (Transact-SQL)

[The Eagle]

Laat ik nou zelf technisch ERP specialist zijn met een behoorlijke focus op Oracle DB's

Ik snap je vraag, maar het antwoord is niet eenduidig te geven. Het is afhankelijk van het datamodel, de over te zetten data, evt combineren van datasources, gebruikte DBMS'en, evt hints, DB parameters, etc.
En ook is de vraag in hoeverre de data real time of met achterstand het DWH in mag, en welke typen verbindingen er gebruikt worden tussen de systemen. Kortom: het hele architectuurplaatje speelt mee

Materialized views heb ik wel eens als een oplossing gezien, icm data exports en imports in het DWH. Directe links over SQLnet ook, en via middleware ook. Valt geen pijl op te trekken dus

Dat gezegd hebbend: over wat voor systemen praten we, op wat voor infra draaien ze, hoe groot zijn de datasets die dagelijks over moeten, en hoeveel variantie zit er in die data?

[ZpAz]

Voor zo ver ik weet is het sneller om eerst alles in te voegen en dan de index aan de tabel toe te voegen dan met index een flinke lading achter elkaar toe te voegen. Je kan dat eens proberen.

[JaQ]

Een export en 1:1 import van een ander systeem is:
* geen DWH maar een kopie
* een zeer inefficiënte manier van data verplaatsen.

Afhankelijk van je database "merk" zijn er legio andere oplossingen. Je update klinkt overigens als de slechts mogelijke optie die er is (slow bij row processen)

[sig69]

Tsja het is moeilijk er iets zinnigs over te zeggen zonder de exacte situatie te kennen. Ik kan alleen maar zeggen: meten is weten. Ik lees alleen dat "het te lang duurt". Tsja, wat duurt te lang? Hoe worden de tabellen leeg gemaakt en weer gevuld? Enzovoorts

[Douweegbertje]

Wij hebben al een implementatie met een vrij grote MSSQL database. Ik kan alleen niet super veel in details treden maar dat terzijde. De database zelf is zo'n 60 gig, en sommige tables bevatten dus ook miljoenen records. Heel simpel; zonder index ben je per definitie fucked. Ja dat werkt allemaal prima met misschien < xxxx records maar anders niet.
Wij hadden ooit een 'fail' waardoor er een index miste op een table van ~2m records, en zonder de index duurde een simpele select op één column zo'n 6 seconden. Met index < 0,x-iets.
Nu weet ik niet hoe groot je dataset is, maar ik ging toen onder load (onder werktijd dus) de index bouwen en dat duurde ongeveer 2-3 minuten. Redelijk snel dus.

In elk geval is onze procedure ongeveer zo: Data wordt uit systeem X gehaald en in de DB gezet, vervolgens draaien er allemaal stored procedures die de data omzetten naar onze structuur. Hierbij wordt ook al rekening gehouden met wat 'factoren' om bijv. een gegeven een bepaalde status te geven. Zie het maar als 'harde view' om je data te prepareren voor uiteindelijk gebruik. Uiteindelijk wordt onze 'live' database helemaal leeg gegooid, en wordt de 'import' gekopieerd naar de live. Dat gaat overigens enorm snel met de juiste setup (enkele minuten). Uiteraard zitten alle indexen er dan al op.

Deze procedure duurt al met al wel een paar uur, echter resulteert dit wel in een fatsoenlijk werkbare database die 'helemaal netjes is'.


IMO een gehele database 'updaten' zoals je in je openingspost zegt lijkt me niet een geweldige optie. Eigenlijk moet je dan je gehele database itereren, dingen updaten, inserten en nog verwijderen. Nu ben ik ook niet DE expert (ben immers maar programmeur ) maar dan 'moet' je ook je indexen rebuilden etc.

[sig69]

Het kan makkelijk dat alles weggooien en opnieuw inserten sneller is dan alles checken en updaten, dat hangt er net vanaf hoeveel records er wijzigen en hoeveel er toegevoegd worden. Het is gewoon specialistisch werk waarvoor je eigenlijk gewoon op locatie moet zijn

[Gomez12]

In wezen wil je bijna nooit handmatig incrementeel gaan werken vanuit een 3e systeem.
Dan heb je last van :
- traagheid vanwege opzoeken of iets incrementeel moet of niet
- (in jouw specifieke voorbeeld) afhandeling benodigd voor remove / keys etc
- Traagheid vanwege row by row kijken
- Onvoorspelbaarheid van tijd want wellicht heb je normaal 10.000 mutaties maar wordt er bij een jaarafsluiting alles van het vorige jaar gemuteerd en praat je dan opeens over 1.000.000 mutaties en dan red je het wellicht niet meer binnen 12 uur, leuk een onvoorspelbaar systeem.

In principe zou ik grofweg 2 kanten kunnen opgaan :
- Als je import al de 100% juiste structuur heeft : index uitschakelen, bulk import, index inschakelen en rebuilden (dan is er maar rebuild actie nodig terwijl je anders bij elke row een rebuild actie hebt)
- Als je import een andere structuur heeft : Alles importeren in een nieuwe staging table zonder indexen en dan vanuit die staging table je daadwerkelijke tabel vullen met query's zodat sql set-based kan werken (op deze manier kan je wel incrementeel gaan werken imho want nu kan je over staging en nieuw gewoon controles draaien die verifieren of je incrementele acties wel alles meepakken of dat er toch ergens een gaatje zit vlak na bijv een jaarovergang oid).
Dan kan je daadwerkelijke tabel wel indexen bevatten.

In het heel kort gezegd : Indexen betekenen performanceverlies bij inserts en performancewinst bij selects. De performanceverlies is niet erg als je het over x query's hebt, maar als jij row by row gaat bijwerken dan heb je iets in de orde van grootte van xxx.xxx performanceverlies (want bij elke insert)

[Torrentus]

Wat een fantastische community is dit toch ook. Bedankt allemaal voor het meedenken.

Ik begrijp dat e.e.a. lastig is in te schatten zonder alle achtergrondinformatie. Zoals jullie vast begrijpen kan helaas ook ik niet te ver in detail treden, maar het DWH draait inderdaad op Microsoft-SQL, ik werk met SMSS. De dump wordt met een eenvoudige stored procedure gedaan. Die stored procedure dumpt elke nacht alle records uit een view met de benodigde gegevens in een tabel.

Als het goed is krijg ik vandaag beschikking over een clone van de database, dus dan kan ik e.e.a. gaan meten om met concretere cijfers boven tafel te komen.

@Jaq, de export is deel van het nachtelijke proces dat in het DWH plaatsvindt. Het is geen 1:1 kopie, maar wel nagenoeg. Dat is de brondata voor het DWH, vervolgens zijn daar natuurlijk de échte DWH-views en tabellen op geplaatst voor analytische doeleinden, die leiden uiteindelijk tot één hoofdview en die hoofdview wordt elke nacht in een tabel gedumpt. (Gedenormaliseerd, heb ik hier net geleerd.. )

@Douweegbertje ik vermoed dat onze situaties dan vergelijkbaar zijn. Al is het DWH hier wel een fractie kleiner, je omschrijft een situatie die ik hier ook erg herken. Toch nog een vraag over dit stuk van je antwoord:

Douweegbertje schreef op woensdag 10 december 2014 @ 22:07:Uiteindelijk wordt onze 'live' database helemaal leeg gegooid, en wordt de 'import' gekopieerd naar de live. Dat gaat overigens enorm snel met de juiste setup (enkele minuten). Uiteraard zitten alle indexen er dan al op.

Je schrijft dat de indexen er dan uiteraard al opzitten. Doel je daarmee op de harde view (en onderliggende views/tabellen) of de live-tabel? Want in dat laatste geval zouden die indexen het proces toch juist vertragen (INSERT-statement met index duurt langer)? Dan zou de suggestie van Gomez12 'index uitschakelen, bulk import, index inschakelen' de efficiëntste methode zijn?

Nogmaals hartelijk bedankt, ik kan er echt mee verder

[ Voor 5% gewijzigd door Torrentus op 11-12-2014 09:23 ]

[Douweegbertje]

Met MSSQL kun je heel simpel een gehele database binnen enkele minuten (dataverkeer is het limiet haast) in één keer overzetten. Dus in feite wordt de db klaargezet, en alles wordt daar al op gedaan, als het klaar is wordt deze over gezet. Geen idee hoe/wat dat gaat met MySQL, in elk geval kan dat niet op de manier zoals MSSQL dat wel kan. Misschien dat je op een andere manier een copy kan maken zonder export oid.

[The Eagle]

Zal met MySQL ook wel kunnen verwacht ik. Beetje DBMS kan gewoon een hot of cold clone aan.

Wat me verder nog te binnen schoot: de import en exportscripts, maken die gebruik van tijdelijke tabellen? Zo ja, truncate die tabellen maar eens voor je het ding aan zet

Having said that: export van een volledige DB is nooit echt handig. There ar better ways

[Douweegbertje]

Beetje jammer die laatste zin, los van de spelfout. Kun je dan juist even beargumenteren waarom het niet handig is, en wat dan de betere oplossing is. Uiteindelijk gaat haast heel dit topic hierover, en je roept nu iets waar iedereen graag het antwoord op wilt hebben.

[The Eagle]

Mja, tikfouten op een iPad, gebeurt wel eens he

Vwb die volledige DB export:
- Een volledige export van een ERP DB gebruik je in principe alleen als je een clone wilt maken voor bijv test- of dev doeleinden (OTAP refresh).
- Doe je een volledige export die buiten je productieomgeving gebruikt gaat worden, dan zie je vaak dat je aan allerlei compliance en/of scramble regels moet voldoen (P data mag niet zomaar buiten P). Of dat hier relevant is weet ik niet, maar het zou kunnen.
- Als jij een volledige DB moet exporteren om er analyses op uit te kunnen voeren, dan weet je dus kennelijk niet welke data je bij je analyse nodig gaat hebben danwel welke relevant is. Anders had je je dataset wel beperk tot het noodzakelijke (o.m. om de tijdspanne kort te houden)
- Als je een vrijwel volledige DB exporteert naar file dan duurt dat lang, omdat je met je selecties zit. Als je een volledige export van je DB gebruikt kun je echte DBMS tooling gebruiken en dat kan aanzienlijke tijd besparen, zowel bij export als bij import.
- Als je toch zo nodig een volledige DB clone wilt gebruiken, dan heb je daar ook de ruimte voor om dat te doen. In voorkomend geval loont het vaak om de reporting op een standby-DB van productie te doen. Daarmee ontlast je het OLTP proces, en weet je zeker dat je reporting / DWH tooling een juiste bron heeft.

En zo zijn er nog wel meer redenen te bedenken waarom een volledige DB export niet handig is. Tijd is een van de voornaamste. Is tevens waar TS zijn stageopdracht aan te danken heeft

[Gomez12]

@The Eagle : Je vergeet echter dat het hier een ERP-systeem betreft, ik ken genoeg ERP-systemen die niet op een makkelijk te queryen db-server draaien (de grotere wel, maar genoeg kleintjes die "exotische" dbase-systemen gebruiken)

Plus dat je met veel ERP-systemen veel tegenkomt dat er weinig kennis is over wat het achterliggende data-model en wat waar en wanneer geraakt wordt (alleen kan men wel info query'en)
Dan kan je wel per exacte query de synch gaan aanpassen (want per query weet je wel de info die benodigd is) maar wil je enkel een reporting systeem hebben dan is het veelal makkelijker om alles over te pakken vanuit een "gammel" db-systeem en dan in een behoorlijk db-systeem de boel weer de boel recht te trekken zodat je de synch maar 1x hoeft te schrijven.

Plus dat erp-systemen nog wel eens flexibel willen zijn waardoor iets wat nu niet gebruikt wordt bijv over een half jaar wel gebruikt kan gaan worden (prijs-berekeningen etc zijn daar leuke voorbeelden van, de complete prijsbereken structuur is ingewikkeld en complex en er wordt maar 10% van gebruikt, maar pak je enkel die 10% dan kan je over een half jaar weer aan de synch gaan knutselen omdat verkoop een nieuwe grote klant heeft die vereist dat er nu 10,5% gebruikt gaat worden).
Staffels / korting over korting over korting structuren etc heb ik daarmee al veel fout zien gaan (dat gebruiken we toch niet en 2 jaar later zit er een "fout" in je DWH want spontaan is men het wel gaan gebruiken)

Wat jij aanraad is de beste manier, maar die vereist wel redelijk wat in-depth kennis en ervaring met mogelijke vragen/wensen en het data-model achter het erp-systeem zodat je niet elke keer constant de synch hoeft aan te passen als er weer iets verandert.

[The Eagle]

Yup, redelijk herkenbaar allemaal.
Gelukkig zie je wel dat degene die een nieuwe functionaliteit implementeren, er meestal ook van op de hoogte zij dat er bepaalde data geinterfaced wordt. En die trekken dan wel aan de bel

Having said that: de kleinere ERP systemen waar jij op doelt neem ik, nofi, eigenlijk niet echt serieus. Die E staat niet voor niks voor Enterprise. Als je klanten enterprise klanten zijn, laat je het wel uit je hoofd om een relatief exotische DB te gebruiken. Dan val je bij een beetje pakketselectie al meteen af wegens afwijkend van corporate standaarden. Plus als je een paar ton aan licenties per jaar aftikt, meen ik dat je als klant ook mag verwachten dat je het datamodel gewoon kunt krijgen.
Dat je vervolgens consultancy nodig hebt voor beheer en evt customs...tsja, zo gaan die dingen. Ieder zijn vak

[Gomez12]

Tja, jij kan ze niet serieus nemen.
Maar bij het formaat bedrijf wat jouw werkwijze hanteert zie ik weer niet zosnel een stage-opdracht komen van : Werp eens een kritische blik op het DWH (waar al f*cked up workarounds in de werkwijze zijn geimplementeerd om het nog enigszins werkbaar / binnen de kosten te houden). En zeker niet dat een stagair dan de vrijheid heeft om zelf SQL-query's te gaan googlen om het beter te maken.

Oftewel ik vermoed dat de TS bij een formaat bedrijf zit wat volgens jou geen erp heeft

Bij jouw formaat bedrijf is :
- Inventariseren al bijna geen optie omdat je daarvoor al in-depth knowledge nodig hebt of van het bedrijf of van het pakket of van het DWH (en TS heeft geen van 3)
- SQL-oplossingen aandragen totaal not done, daar heb je consultants en devvers voor die gewoon ingehuurd worden.
- Inventariseren en tegelijk oplossingen aandragen helemaal not-done omdat het gescheiden afdelingen zijn.

[pedorus]

Torrentus schreef op donderdag 11 december 2014 @ 09:20:
@Douweegbertje ik vermoed dat onze situaties dan vergelijkbaar zijn. Al is het DWH hier wel een fractie kleiner, je omschrijft een situatie die ik hier ook erg herken.

Hmm, dan vind ik het nogal lang duren als het clonen een hele nacht duurt terwijl de database in het ram van een moderne databaseserver past.. Of op een SD-kaartje van nog geen 30 euro. Ter vergelijking kost een database-backup minder dan een uur / 100G in mijn ervaring. En dan moet de data van disk komen. Dit doet erg denken aan per row wachten op round-trips naar een disk ofzo. Ik vermoed dat hier met wat tuning wel het een en ander te halen valt..

The Eagle schreef op donderdag 11 december 2014 @ 11:35:
Zal met MySQL ook wel kunnen verwacht ik. Beetje DBMS kan gewoon een hot of cold clone aan.

Natuurlijk kan dat, mits InnoDB voor een hot clone.

[The Eagle]

@Gomez12: lijkt een beetje of het nu op de man gespeeld wordt, al zal dat niet je bedoeling zijn. Verder zie ik je eigenlijk alleen maar aannames doen. Goed bedoeld, maar ik kan me niet voorstellen dat jij weet hoe het er bij TS of bij ons op de zaak gaat
Maar dat gaat allemaal off-topic nu, en ik denk niet dat TS daar mee geholpen is. Ben ik zelf ook debet aan btw, ben niet heilig ofzo

---

@TS, het blijkt dus MSSQL te zijn voor beiden? Hoe doe je je export /import nu, met queries of op een andere manier? En hoe lang duurt dat? Wat zijn de eisen en wensen aan tijdigheid van de upload?

[Torrentus]

Ik heb in zekere zin het antwoord op m'n vraag gekregen; ik heb me vergist en de tabel droppen en vanaf de grond op opnieuw opbouwen lijkt wel de gangbare manier te zijn om een dergelijke kopie/export te maken.

Gomez zat trouwens wel goed, geen MSSQL voor beide partijen. Het ERP-systeem draait (nog) op Sybase. Er worden trouwens wel degelijk Enterprises mee bedient, maar het is geen multi-national waar ik aan de slag ben. Middenformaat dus in dat geval.

De export/import wordt uitbesteed en wordt niet door het bedrijf zelf uitgevoerd. Ik heb daarom geen inzicht in die procedure behalve dat ik weet dat de tabellen elke nacht verwijdert en weer opgebouwd worden en in de logs kan ik zien dat het hele proces 6 uur duurt. Daar zitten ook nog bewerkingen en aggregraties in natuurlijk, het is niet alleen tabel naar tabel overkopieëren.

Dat daar nog veel efficiëntieslagen in te halen zijn lijkt me triviaal, maar dat is eigenlijk zaak van de partij waaraan de boel is uitbesteed (zolang het niet langer dan 6 uur wordt is het ok; 's nachts wordt er niet gewerkt). Daar hoef ik me niet mee bezig te houden.

Ik moet me bezig houden met de efficiëntie van het data warehouse bij het bedrijf zelf, dat dus deels bestaat uit tabellen die gevuld worden door de externe partij. Daar mogen naar het schijnt geen indexen op omdat dat het proces van 6 uur nog verder zou vertragen. Uit dit topic heb ik begrepen dat dat nog wel meevalt, dus ik ga vandaag/volgende week eens kijken hoe lang het opbouwen van die indexen kost op een ongeïndexeerde gevulde tabel (of de hele set met brondatatabellen).

Ik heb de performancewizard trouwens uitgevoerd, heb er een query van de totaalview ingestopt (die zijn inhoud bepaalt op basis van alle ongeïndexeerde tabellen die de externe partij in het datawarehouse stopt). De winst lijkt 15% te zijn als ik alle indexen opbouw die de wizard aanraadt. Is dat een normale uitkomst? Op basis van verhalen die ik hier en elders gelezen had had ik eigenlijk meer snelheidswinst verwacht..

[ge-flopt]

Als je een mirror wilt hebben van de database, waarom logship je het geheel dan niet? of eventueel met een backup/restore. Dan worden je indexzen niet opnieuw aangemaakt, want die neem je over van de bron.

[Torrentus]

ge-flopt schreef op vrijdag 12 december 2014 @ 14:17:
Als je een mirror wilt hebben van de database, waarom logship je het geheel dan niet? of eventueel met een backup/restore. Dan worden je indexzen niet opnieuw aangemaakt, want die neem je over van de bron.

Ik vermoed omdat het geen mirror is

Daar zitten ook nog bewerkingen en aggregraties in natuurlijk, het is niet alleen tabel naar tabel overkopieëren.

[pedorus]

Torrentus schreef op vrijdag 12 december 2014 @ 13:35:
Ik heb daarom geen inzicht in die procedure behalve dat ik weet dat de tabellen elke nacht verwijdert en weer opgebouwd worden en in de logs kan ik zien dat het hele proces 6 uur duurt. Daar zitten ook nog bewerkingen en aggregraties in natuurlijk, het is niet alleen tabel naar tabel overkopieëren.

Je kunt met monitoring wel zien wat voor writes er zijn natuurlijk. Ik heb met mysql wel eens de boel een factor 10+ versnelt door settings in linux/mysql aan te passen omdat het proces helaas writes per 1 deed. Op eenzelfde manier zou je hier SQL Server (bijv. checkpoints) een klein beetje kunnen tunen, en wellicht een SSD kunnen gebruiken als die nog niet wordt gebruikt, misschien wat meer memory installeren, het process op het warehouse kunnen draaien voor minder network latency, etc.

Daar mogen naar het schijnt geen indexen op omdat dat het proces van 6 uur nog verder zou vertragen. Uit dit topic heb ik begrepen dat dat nog wel meevalt, dus ik ga vandaag/volgende week eens kijken hoe lang het opbouwen van die indexen kost op een ongeïndexeerde gevulde tabel (of de hele set met brondatatabellen).

Dat valt niet mee, waarschijnlijk moet je zorgen dat de indexen er weer vanaf zijn voordat een nieuwe volledige export begint, omdat anders die 6u helemaal uit de klauwen loopt. En dan achteraf pas weer toevoegen. Tenzij dat process de tabellen toch opnieuw aanmaakt en daarmee de indexes zelf weg gooit.

De insertion volgorde is ook interessant, bij een clustered index wil je dat ze overeen komen, anders werkt eerst heap en daarna sorteren waarschijnlijk beter.

[Gomez12]

pedorus schreef op vrijdag 12 december 2014 @ 15:10:
[...]
Je kunt met monitoring wel zien wat voor writes er zijn natuurlijk. Ik heb met mysql wel eens de boel een factor 10+ versnelt door settings in linux/mysql aan te passen omdat het proces helaas writes per 1 deed.

Het is een versnellingsstap, maar wel 1 met grote consequenties voor beschikbaarheid / ACID compliance etc.
Zonder de versnelling heb je in principe zekerheid dat elke actie ook correct weggeschreven is bij een power outage etc, terwijl je met versnelling best een corrupte database kan krijgen bij een power outage omdat bijv een trigger / key niet bijgewerkt is zolang omdat er nog pas 9 records in memory stonden terwijl er bij 10 weggeschreven werd.

Zeker als je elke nacht toch compleet opnieuw vult dan is het wmb "verwaarloosbare data", als de db corrupt raakt dan mieter je die weg en bouw je de database opnieuw op en laat je de nachtverwerking opnieuw draaien en je bent binnen 12 uur weer online.

Oftewel ik zou het risico op basis van de huidige gegevens wel durven lopen vanwege de versnelling die het oplevert, ik benoem het alleen even omdat het niet alleen een versnelling is maar ook een compliance ding, mag dat ding tussen 8 en 5 max 1 uur down gaan dan is het bijv imho geen optie meer om de versnelling door te voeren

[The Eagle]

Dat import proces: worden daarin de tabellen leeggemaakt met een delete, krijgen ze een truncate of worden ze echt gedropt en opnieuw gebouwd? Als dat een delete from is levert dat namelijk nogal wat undo op, en das een relatief kostbaar zaakje.
Verder ook de vraag wat voor tooling er wordt gebruikt: is die import een zut SQL statements op temp tabellen, of gebruikt men datapump achtige zaken die op DBMS niveau werken?

Indexen bouwen lijkt me sowieso een goede stap na de import. Zoiets kan ook (al dan niet deels) tijdens productie plaatsvinden. Een index kan toch pas gebruikt worden als ie klaar en online is, dus hooguit wordt de performance dan gedurende de dag beter

[Douweegbertje]

pedorus schreef op vrijdag 12 december 2014 @ 01:33:
[...]

Hmm, dan vind ik het nogal lang duren als het clonen een hele nacht duurt terwijl de database in het ram van een moderne databaseserver past.. Of op een SD-kaartje van nog geen 30 euro. Ter vergelijking kost een database-backup minder dan een uur / 100G in mijn ervaring. En dan moet de data van disk komen. Dit doet erg denken aan per row wachten op round-trips naar een disk ofzo. Ik vermoed dat hier met wat tuning wel het een en ander te halen valt..

[...]

Lezen svp.
Ik zeg dat het verplaatsen qua tijd minimaal is < 3-4 minuten oid... Onze tijd gaat zitten in alle SP's die de data omzetten naar een werkbaar formaat, en inderdaad soms alles langs gaan om bijv. statussen te updaten. if x waarde van kolom y van table z = 1 then xx = A in table yy als heel simpel voorbeeld.

[pedorus]

Het enige dat ik uit jouw post gehaald heb, is de omvang van 60G, waarvan TS zegt dat het in zijn geval om minder data gaat..

Om er toch nog iets over te zeggen dan: SPs zijn meestal niet het snelst voor berekeningen, en je kan dingen op meerdere manieren doen. Als de brondata <60G is, en de berekeningen niet super ingewikkeld (wat bij ERP zeer waarschijnlijk zo is), dan moet de data in afzienbare tijd te verweken zijn.

Voor het idee: Ik heb laatst een scriptje herschreven, in een versimpeling kwam het hier op neer: Scriptje A: Voor ieder matchend record in tabel x, zoek bijberende records in tabel y voor de verdeling. Doe dan een merge (insert ... on duplicate ...) op tabel z. Scriptje B, vernieuwde versie: sla tabel y op in het werkgeheugen. Loop over matchende records van tabel x, doe berekening, sla tussenresultaten op in geheugen. Flush geheugen in batches naar tabel z. De draaitijd van scriptje A was een uur, de draaitijd van scriptje B 15 seconden..

Maar alles is afhankelijk van specifieke situaties natuurlijk