[MSSQL] Kan dit efficiënter?

[Douweegbertje]

Ik heb wat SP's draaien, en o.a. heb ik een aantal opdrachten die data verwijderen uit een DB, en vullen uit een DB op een andere server. Dat ziet er dan zo uit:

code:

Begin
    Execute('USE dbBlaat; Delete from dbBlaat.dbo.TableNaam where Gegeven = ''A''') at [xxx.xxx.xxx.xxx]
    Execute('USE dbBlaat; Insert TableNaam Select * from [xxx.xxx.xxx.xxx].dbBlaat.dbo.TableNaam') at  [xxx.xxx.xxx.xxx]
end

Nu ben ik bezig om te kijken waar wat tijd gewonnen kan worden; even voor de goede orde is dit iets van 0.1% van alles wat draait in de nacht. Helaas heb ik (nog) niet een adequate test omgeving om allerlei testen uit te voeren, maar ik wilde wel even de vraag stellen: ziet dit er nu 'raar' uit, of is dit een redelijk manier om data over te zetten, in gedachte houdende dat de indexen e.d. in orde zijn
Ik zie zelf persoonlijk er niet echt iets vreemds aan.

[McKaamos]

Ik ben absoluut geen MSSQL kenner, maar heb je geen mogelijkheid om n SlowLog aan te zetten? Dat zit wel in MySQL/MariaDB. Ff finetunen wat je drempelwaarde voor een trage query is, maar dan kan je zo uitfilteren wat de trage opdrachten zijn.


Verder, wat doet de insert precies? Is dat deels bestaande data die je eerst delete?
Want if so, als er een mogelijkheid is voor 'if exist', zou je 'update if exist' kunnen doen. of 'insert if not exist'.
Misschien scheelt dat weer tijd/snelheid?

[Palthe]

Ja, of een incrementele update van je tabel.
Dan zou je alleen de records kunnen kopiëren die na de laatste update van je tabel er bij zijn gekomen in de andere db (door middel van een timestamp last changed bij records die ook kunnen wijzigen of een oplopend record id wanneer dat niet het geval is)
Hoe meer records de tabel bevat (over de loop van tijd) hoe meer je uit een incrementele load gaat winnen.

[ Voor 20% gewijzigd door Palthe op 04-02-2015 19:14 ]

[Douweegbertje]

Ja dit is meer één typisch gevalletje maar er zijn nog veel meer van dit soort transacties.
In feite voor deze is het domweg het verwijderen van de data wat standaard al uit de 2e DB komt, en vervolgens de nieuwe data (uit db2 dus) weer erin zetten. Het bestaat dus uit nieuwe, maar ook al bestaande records.

Alleen mijn redenatie; het lijkt me sneller om een volledige delete te doen, met vervolgens een gehele insert. Maar dat is kwestie van even 'zo nadenken'. Alles itereren en eventueel updaten lijkt me juist een zwaardere load, vooral bij zelfs meer records. Dus eigenlijk het tegenovergestelde wat jij zegt @palthe.

[P.O. Box]

in mysql heb je bv. "insert ignore" bestaat er niet iets soortgelijks in mssql? (moet je wel een PK hebben die in de oude en nieuwe data gelijk is)

[sig69]

Als dit 0,1% is zou ik eerst ergens anders tijd gaan proberen te winnen.
Wat meer inhoudelijk: of het sneller is om alles te verwijderen en weer te inserten ipv updaten en inserten is zeer afhankelijk van je data. Hoeveel gewijzigde, nieuwe en ongewijzigde records zijn er? Indexen op de tabel? Kwestie van testen.
Verder: heeft mysql geen truncate table? Is doorgaans sneller. En mysql heeft volgens mij ook replace into... die zelf inserts en updates voor je afhandelt.

Edit: had even niet gezien dat het mssql betrof ipv mysql

[ Voor 79% gewijzigd door sig69 op 04-02-2015 21:54 ]

[Ealanrian]

Douweegbertje schreef op woensdag 04 februari 2015 @ 19:23:
Ja dit is meer één typisch gevalletje maar er zijn nog veel meer van dit soort transacties.
In feite voor deze is het domweg het verwijderen van de data wat standaard al uit de 2e DB komt, en vervolgens de nieuwe data (uit db2 dus) weer erin zetten. Het bestaat dus uit nieuwe, maar ook al bestaande records.

Alleen mijn redenatie; het lijkt me sneller om een volledige delete te doen, met vervolgens een gehele insert. Maar dat is kwestie van even 'zo nadenken'. Alles itereren en eventueel updaten lijkt me juist een zwaardere load, vooral bij zelfs meer records. Dus eigenlijk het tegenovergestelde wat jij zegt @palthe.

Ik ga nu vast een paar verkeerde aannames doen maar ik ga toch een poging wagen:
Case 1 is truncate & insert en Case 2 is select & update:
Case 1:
Ik neem aan dat de truncate operatie behoorlijk optimaal is dus truncate tel ik als 1 operatie,
Nu insert je alle data van uit db2 naar db1, dit zijn N operaties waarbij N = aantal records. Echter zullen er nu mogelijk nog constraints weer gelegd moeten worden en zullen je indexes opnieuw gebouwd moeten worden. Er van uit gaan dat dit ver geoptimaliseerd is heb je voor beide 1 operatie nodig en kom je dus op een 3N+1 aantal operaties uit.

Case 2:
Bij deze casus ga ik uit van een worst-case versie dus elk record moet een update krijgen:
Er moeten N aantal selects gedaan worden op beide databases, dit zijn dus 2N operaties. Vervolgens moet je voor elke operatie een update doen, dit is dus 1N operatie, de gegevens vanuit DB2 heb je immers al via je eerste select. Hierbij kom je dus op een worst-case uit van 3N, echter zul je niet elk record aanpassen en zal je dus waarschijnlijk minder updates doen waardoor je afhankelijk van het percentage van aan te passen records op grofweg 2.5N uit komen (aanname van de helft van de records moet aangepast worden) in average case (dit kan een stuk beter berekend worden met meer info) of 2N operaties als er niks een update moet hebben dus je best case.

Dus puur op het aantal operaties zal er niet veel verschil zijn tussen de twee bij grote hoeveelheden waarvan alle records aangepast moeten worden, de +1 uit case 1 is een constante en te negeren dus komen beide op 3N.

Maar nu zijn er 2 vrij grote punten volgens mijn redenatie:
1: Ik heb alle operaties even zwaar gerekend, en ik verwacht dat een truncate best wat tijd in beslag neemt ten opzichte van een select en een insert is mogelijk iets zwaarder te wegen als een update.
2: De grote onbekende is de hoeveelheid aan te passen records, dit is volgens mij behoorlijk van invloed op wat er moet gebeuren.


Disclaimer: Mijn hele redenatie is gebaseerd op aannames die ik heb geprobeerd te benoemen en op wat gok werk. Er zullen zeer waarschijnlijk fouten in zitten.

[Firesphere]

Nu kun je een select & update verder optimaliseren door de indexes op je columns goed te zetten, zodat je de meest efficiente select-update query draait.

Wat je eventueel kan doen, is een temporary table maken (Hoewel, heeft MSSQL dat?), waar je je constraints op kan controleren.
Nadeel is wel dat je dan uiteindelijk van je temp de final table moet maken.

[Verwijderd]

Check het MERGE statement eens. Ik moet er bij zeggen dat er ook gevallen zijn waarbij de performance tegenvalt t.o.v. een losse insert/update. Meten is weten.

MSDN: MERGE (Transact-SQL)

[sig69]

Ik ken je omgeving niet, maar met mssql kan je ook nog een ssis oplossing overwegen. Maar wederom: je komt er alleen achter wat het snelst is door te testen.

[markvt]

Varieert de lengte van A, zit er een index op de kolom Gegeven?

code:

    Execute('USE dbBlaat; Delete from dbBlaat.dbo.TableNaam where Gegeven = ''A''') at [xxx.xxx.xxx.xxx]

[Gomez12]

Het is meten is weten...

Bij dit soort acties heb je juist een groot risico dat je indexen op orde hebben het heel erg inefficient maakt.

Een delete is "zwaar" met indexen en een insert is ook "zwaar" met indexen, alleen een update kan ook "zwaar" zijn met indexen.

Gaat het echter om het merendeel van je tabel dan is het veelal efficienter om eerst je indexen te disablen, daarna je delete en insert te doen en dan je indexen te rebuilden, dan sla je alle intermediate rebuilds over die na "elke" delete en "elke" insert volgen.

Heel erg simplistisch gezegd (omdat er nog andere zaken meespelen wat het uiteindelijke beeld weer kan veranderen) presenteer je de volgende keuzes :
1
- Index lookup
- Delete record
- Index rebuild
- Insert record
- Index rebuild

2
- Index lookup
- Update record
- (net naar gelang of de update een index raakt een index rebuild of niet)

Ga je nou echter het merendeel van je records wijzigen dan heb je ook nog optie
3
Voor alles indexen disablen
- Delete record met een performance hit om hem op te zoeken, want geen index maar als je er genoeg hebt dan vervalt deze performance hit volledig tegen een index rebuild
- Insert record zonder een index rebuild
Na alles index rebuild.

En uiteraard als je alles verwijdert dan gewoon truncate en index disable op lege tabel en vullen en index rebuild op complete tabel.

Dat zijn de 4 opties die ik mogelijk acht voor 0,1% van de tijd. Zou je zeggen dat deze actie 99,9% van de tijd innam dan zijn er veel exotischere dingen te bedenken die net naargelang de data veel en veel sneller of veel en veel langzamer zullen zijn, maar dan krijg je er wel een brok complexiteit bij en daarmee kost het je onderhoudbaarheid. En imho kan je beter ijzer erbij prikken dan onderhoudbaarheid opgeven, ijzer is cheap...

[Firesphere]

Even voor het gemak, "meten is weten" is niet iets wat er toe doet.

De machine die het moet uitvoeren, blijft hetzelfde, dus het optimaliseren van de query's, is het enige wat er toe doet.
Dit kan aan de hand van het aantal operaties, dat is genoeg.

[sig69]

Beetje kort door de bocht. Sowieso zal je gewoon moeten proberen welke (combinatie van) query's het snelst zullen werken in deze situatie. En dan zijn er zijn ook nog andere oplossingen die je kan gebruiken. Dus het blijft gewoon een kwestie van uitproberen en testen wat het beste werkt (dus: meten is weten).

[ Voor 3% gewijzigd door sig69 op 04-02-2015 22:51 ]

[Douweegbertje]

Thanks allen alvast voor alle informatie, had niet zoveel al verwacht
Mijn probleem is deels dat het een enorm kritisch en grote procedure is die in totaal wel 8 uur bezig is, en dit is ook gemaakt door meerdere mensen waarvan er geen één meer in dienst is Allemaal niet erg, want de kennis is wel aanwezig, alleen laat ik gewoon eerlijk zijn; dit is een stukje meer dan basis SQL. Ik kan de meest gekke queries maken maar met zoveel transactions en zo'n grote dataset ben ik nog niet echt thuis.

Uiteindelijk is het ook de bedoeling dat ik wat ruwe data op een test DB ga zetten, en eigenlijk de acties allemaal ga nalopen in een test omgeving.
Wat ik wel ervaar is dat naar mijn mening de indexen echt zuigen, want ik heb af en toe een paar zitten met een defragmentation van boven de 80%...

In elk geval zat ruimte voor verbeteringen, maar zoals ik nu even peil is an sich de statement in mijn voorbeeld niet 'gek' , maar ik ga nu zeker eens kijken wat er gebeurt met een update.
_{Daarbij ga ik natuurlijk ook de indexen nog eens fixen}

[Gomez12]

Firesphere schreef op woensdag 04 februari 2015 @ 22:40:
Even voor het gemak, "meten is weten" is niet iets wat er toe doet.

De machine die het moet uitvoeren, blijft hetzelfde, dus het optimaliseren van de query's, is het enige wat er toe doet.
Dit kan aan de hand van het aantal operaties, dat is genoeg.

Het probleem is dat een query tegenwoordig weinig meer zegt vanwege de "intelligentie" van de query optimizer en de snelheid van executie en grootte van data.

Op het moment dat iemand een tabel heeft met 101 rows en die wil er 1 specifieke behouden dan zeg ik : delete, heb je het echter over 100 miljoen (+1) rows en wil je er 1 specifieke behouden dan zeg ik : die ene wegcopieren, truncate, terugcopieren.

De vraag verandert niet (alles wegmieteren minus 1 record) en zolang je met 1000 rows werkt kan je je query optimalizeren tot je een ons weegt maar significant verschil ga je niet zien (als je al enig verschil ziet) ga je dat echter blind doen op 100 miljoen rows (want je query heb je geoptimaliseerd op je 1000 rows en daar heb je de meest geoptimaliseerde uitgepakt) dan moet je er waarschijnlijk even een bed bijpakken terwijl je wacht.

Wil je echt daadwerkelijk je operaties gaan tellen dan moet je exact weten wat de query optimizer van jouw query gaat maken (en dit kan per run verschillen net naar gelang cache gevuld is, table statistics kloppen etc etc) en dan het aantal operaties gaan tellen wat er na de query optimizer gebeurt.
Ik wens je veel succes met een complexere query.

Ik heb rdbms'en een delete all zien optimizen naar een truncate en ik heb het ze niet zien doen, ik ga niet bijhouden welke rdbms engine nu welke optimalisaties wel / niet uitvoert op een query die 0,1% van de tijd kost, dat is echt giga-veel werk om het goed uit te tellen. Veel en veel snellere methode is dan : Meten...

---
@Douweegbertje, ook al duurt de totale complexe query 8 uur, dan blijft 0,1% je nog maar een besparing van 30 seconden opleveren.

Waarschijnlijk heeft het plaatsen van dit topic en het lezen ervan je al meer gekost dan wat de daadwerkelijke runtijd per maand is. En dat is als je die query echt naar 0 seconden krijgt.

[sig69]

sig69 schreef op woensdag 04 februari 2015 @ 21:32:
Als dit 0,1% is zou ik eerst ergens anders tijd gaan proberen te winnen.

Ik quote mezelf niet vaak maar nu is het wel op z'n plaats denk ik

[Douweegbertje]

Eh zonder te weten wat de andere 99.9% is, kun je niet zulke loze statements maken
Misschien is dit wel de grootste stap.. van de 100%, ongeacht of het nu 0.1% of 99% is, alleen bij de 99% zie jij het makkelijker dat het ook zo is

_{En wellicht had ik het verkeerd geformuleerd maar bovenstaande query is onderdeel van.. een proces dat 8 uur duurt}

Mijn initiële vraag was ook meer globaal: zie je hier iets vreemds aan. En ik ben een stuk wijzer geworden.
Overigens snap ik ieder zijn punt, maar af en toe moet je zelf ook proberen eens te kijken wat iemand vraagt; ik kan hier niet alles neer gooien maar ik heb wel een vraag. Uiteindelijk is er een leuke discussie op gekomen, en ben ik al een deeltje wijzer.
Ik ben niet opzoek naar HET antwoord, anders had ik wel een specifieke vraag gesteld waarop iemand HET antwoord zou kunnen geven.

Nu moet je het doen met globale data en geen confirmatie dat jouw antwoord ook HET antwoord is

[sig69]

Als je uiteindelijk maar wel laat weten wat het opgeleverd heeft

[Douweegbertje]

Nja om maar een update te geven...
Ik heb de runtime al van zo'n goede 6-7 uur naar zo'n 3, 3 en een half gebracht.
Er zat o.a. een enorme flaw in een stukje code die moest kijken welke records geupdate moesten worden. Elke nacht wordt er een datadump in een DB gezet, en vervolgens wordt daar (o.a.) één tabel naar de live gezet. Vervolgens komt er een nabewerking op in de zin van de records vergelijken met een history tabel, en verwijder alles in de tabel wat al in de history zit.
In de zin van DELETE blaat WHERE EXISTS (SELECT x from Foo where Foo.a = Bar.a AND Foo.b = Bar.B)

Nja, leuk dat er dan ook NULL values in zitten waardoor er iets van ~500 records in bleven staan in plaats van een update van zo'n 20-30 records max.

Na deze opschoning wordt deze data gebruikt om een x-aantal updates uit te voeren op andere tables.

Het enige waar ik nog tegen aanloop is het feit dat elke procedure van updates op zo'n 8 tables 10-15 seconden duurde terwijl als je het handmatig doet het maar < 1 seconden is.
Nu is ~25 records x 15 seconden niet zo'n ramp meer in tegenstelling tot 500, maar het is nog steeds vreemd en dat moet ik nog fixen. Enorm klote om te debuggen als je het niet kan reproduceren.

[Firesphere]

Hoe wordt de query uitgevoerd? Via een framework-constructie of is het een platte instructieset in bash/.net die direct aangeroepen wordt?

[The Eagle]

Zet eens wat commits tussen die updates. Met dergelijke grote batches genereer je vrij veel undo en redo, en dat is een cyclisch verhaal wat per sessie bijgehouden wordt. Dus een commit in de sessie kan wel eens helpen

[Douweegbertje]

Firesphere schreef op dinsdag 17 februari 2015 @ 22:17:
Hoe wordt de query uitgevoerd? Via een framework-constructie of is het een platte instructieset in bash/.net die direct aangeroepen wordt?

Gewoon via tasks in MSSQL, en dan verschillende stappen waarin verschillende SP's worden uitgevoerd die bij success verder gaan en bij een eventuele fail worden er stappen overgeslagen die dan sowieso niet meer kunnen ivm missende data. In feite werkt het perfect. In de laatste stap wordt er ook nog een mail verstuurd of het goed is voltooid, en anders de eventuele fouten.
Op meerdere stukken zijn er wat loggings aangebracht die dan weer in de task manager uitgelezen kunnen worden.

In feite is dit één van de dingen wat wel stabiel en robuust in elkaar zit

The Eagle schreef op dinsdag 17 februari 2015 @ 22:19:
Zet eens wat commits tussen die updates. Met dergelijke grote batches genereer je vrij veel undo en redo, en dat is een cyclisch verhaal wat per sessie bijgehouden wordt. Dus een commit in de sessie kan wel eens helpen

Volgens mij doet een commit niets 'opschonen'. Echter dat is even mijn simpele redenatie. Volgens mij moet ik het gewoon wat structureler oplossen.

[Firesphere]

Douweegbertje schreef op dinsdag 17 februari 2015 @ 22:49:
[...]


Gewoon via tasks in MSSQL, en dan verschillende stappen waarin verschillende SP's worden uitgevoerd die bij success verder gaan en bij een eventuele fail worden er stappen overgeslagen die dan sowieso niet meer kunnen ivm missende data. In feite werkt het perfect. In de laatste stap wordt er ook nog een mail verstuurd of het goed is voltooid, en anders de eventuele fouten.
Op meerdere stukken zijn er wat loggings aangebracht die dan weer in de task manager uitgelezen kunnen worden.

In feite is dit één van de dingen wat wel stabiel en robuust in elkaar zit

Tja, ik dacht ff checken, want een directe query is een paar honderd tot duizend keer sneller dan een half framework moeten doorlopen

[TallManNL]

Douweegbertje schreef op dinsdag 17 februari 2015 @ 22:10:

Het enige waar ik nog tegen aanloop is het feit dat elke procedure van updates op zo'n 8 tables 10-15 seconden duurde terwijl als je het handmatig doet het maar < 1 seconden is.

Weet je zeker dat je sessie waarmee je test met dezelfde set statements draait. Default zit er wat verschil tussen hoe verschillende programma's hun statements zetten bij het opzetten van hun verbinding.

Zulke tijdsverschillen ben ik zelf tegengekomen bij verschillen in de waarde van ARITHABORT

quote uit dat tweede artikel:

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

[Jaymz]

Ik heb, sinds ik de tool ben tegengekomen, erg veel gehad aan Plan Explorer. Zeker als je het actual execution plan te pakken weet te krijgen (en execution plans kunt ontcijferen ) en daarmee bijvoorbeeld de estimated rowcounts met de actual rowcounts kunt vergelijken

Indexen beoordelen op fragmentatie is wat kort door de bocht; blijkbaar vinden er genoeg updates/inserts/deletes op plaats, sys.dm_db_index_operational_stats kan je wat meer inzicht geven in het gebruik van indices