data vergelijken tussen master bron en doeldatabank vooraf update
perre van wilrijk
Ik schrijf een stored procedure die dagelijks data overzet van een
bron/master SQL databank A naar een doel SQL databank B. Hierbij
hanteer ik de volgende werkwijze.
cursor op tabel in databank A
Indien de matching key niet bestaat in databank B : INSERT
Indien de matching key wel bestaat in databank B : UPDATE in B obv A
(**)
Na de loop controleer ik op keys die bestaan in B en niet meer bestaan
in A. De betreffende rijen worden dan logisch verwijderd in databank
B.
Mijn issue zit in de UPDATE (**). Ik wil enkel data bijwerken in de
tabellen van databank B indien er verschillen zijn tussen A en B, maw
indien gisteren in de master databank A iets werd gewijzigd en dus nog
niet is gepropageerd naar databank B.
Hoe controleer ik het beste of een update nodig is of niet? Ik heb
volgende constructie uitgewerkt ...
IF EXISTS (SELECT *
FROM [B].[dbo].[table]
WHERE [key] = @key
AND
(f1 <> @f1
OR f2 <> @f2
OR ...)
BEGIN
UPDATE ...
SET modificationdate = getdate(),
...
WHERE [key] = @key
END
Een alternatief is ...
IF NOT EXISTS (SELECT *
FROM [B].[dbo].[table]
WHERE [key] = @key
AND f1 = @f1
AND f2 <> @f2
AND ...)
BEGIN
UPDATE ...
SET modificationdate = getdate(),
...
WHERE [key] = @key
END
Tijdens het testen stel ik vast dat er rijen worden ge-update wanneer
ik het niet verwacht en vice versa dat sommige rijen niet worden ge-
update terwijl dit wel zou moeten gebeuren.
Dit blijkt een gevolg van te perfectioneren CASTING van datatypes (oa
float naar varchar) en van vergelijking van NULL values.
Ik ben bijgevolg op zoek naar "Best practices" voor dit soort
updates. Is mijn IF EXISTS structuur een goede werkwijze of zijn er
betere manieren om bron en doel te vergelijken?
Dank bij voorbaat,
Peter.
Hugo Kornelis
>Hi sqlguru's
>
>Ik schrijf een stored procedure die dagelijks data overzet van een
>bron/master SQL databank A naar een doel SQL databank B. Hierbij
>hanteer ik de volgende werkwijze.
>
>cursor op tabel in databank A
>
>Indien de matching key niet bestaat in databank B : INSERT
>Indien de matching key wel bestaat in databank B : UPDATE in B obv A
>(**)
Hoi Peter,
Waarom gebruik je daar in vredesnaam een cursor voor? Je weet toch dat
die veel trager zijn dan set-based SQL?
Je kan dit hele verhaal oplossen met twee opdrachten, een INSERT en een
UPDATE. Of, als je op SQL Server 2008 zit, met één MERGE opdracht.
Hier is de basis voor de SQL Server 2005 versie:
-- Eerst de UPDATE
UPDATE d
SET Kolom1 = b.Kolom1
, Kolom2 = b.Kolom2
(...)
FROM dbo.Doeltabel AS d
INNER JOIN dbo.Brontabel AS b
ON b.Sleutel = d.Sleutel
WHERE d.Kolom1 <> b.Kolom1
OR d.Kolom2 <> b.Kolom2
(...)
-- Dan de INSERT (andersom is minder efficiënt)
INSERT INTO dbo.Doeltabel
(Sleutel, Kolom1, Kolom2, ...)
SELECT b.Sleutel, b.Kolom1, b.Kolom2, ...
FROM dbo.Brontabel AS b
WHERE NOT EXISTS
(SELECT *
FROM dbo.Doeltabel AS d
WHERE d.Sleutel = b.Sleutel);
LET OP: De vorm van de UPDATE opdracht die ik hier gebruik is alleen
veilig als je de join doet op een kolom of combinatie van kolommen die
gegarandeerd uniek is in de brontabel. Dus PRIMARY KEY of UNIQUE
constraint.
>Tijdens het testen stel ik vast dat er rijen worden ge-update wanneer
>ik het niet verwacht en vice versa dat sommige rijen niet worden ge-
>update terwijl dit wel zou moeten gebeuren.
>
>Dit blijkt een gevolg van te perfectioneren CASTING van datatypes (oa
>float naar varchar) en van vergelijking van NULL values.
Dit probleem zul je ook met de set-based versie houden. Twee dingen:
1) Zorg als het even kan voor identieke data types tussen bron- en
doeltabel. Als dat niet kan, bepaal dan zelf op basis van welk data type
de gegevens vergeleken moeten worden (ik neem aan dat dit het data type
van de doeltabel zal zijn, maar jij kent je gegevens beter dan ik) en
gebruik een expliciete CAST (of CONVERT als je de style parameter nodig
hebt) om de gegevens in dat data type te transformeren voordat je ze
vergelijkt. Dus "OR d.Kolom2 <> b.Kolom2" wordt dan dus zoiets als "OR
d.Kolom2 <> CAST(b.Kolom2 AS int)".
2) Sta geen NULL waarden toe waar ze niet nodig zijn. Waar ze wel nodig
zijn wordt de test op ongelijkheid wat ingewikkelder. De meest
begrijpelijke variant is:
OR d.Kolom2 <> b.Kolom2
OR (d.Kolom2 IS NULL AND b.Kolom2 IS NOT NULL)
OR (d.Kolom2 IS NOT NULL AND b.Kolom2 IS NULL)
Iets korter maar lastiger te begrijpen:
OR NULLIF(d.Kolom2, b.Kolom2) IS NOT NULL
OR NULLIF(b.Kolom2, d.Kolom2) IS NOT NULL
Als je zeker weet dat bepaalde waardes nooit in een kolom kunnen staan,
noch in bron, noch in doel, dan kan je ook zo'n onmogelijke waarde
gebruiken voor je test:
OR COALESCE(d.Kolom2, -1) <> COALESCE(b.Kolom2, -1)
Maar pas op; wat nu nog een onmogelijke waarde is kan na de volgende
release opeens wel gebruikt worden en dan kan je lelijk de mist ingaan!
Persoonlijk ben ik dan ook geen voorstander van deze methode.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
perre van wilrijk
Dank voor het klare antwoord.
De update van tabel A obv tabel B heb je me in een post van lang
geleden eens duidelijk uitgelegd en gebruik ik nu frequent met succes.
Ter info ... Waarom ik toch nog cursors gebruik in de concrete
gevallen die aanleiding gaven tot mijn post? Het betreft
batchprocessen die niet frequent worden uitgevoerd (concreet 1x/dag en
1x/jaar) waarbij performantie geen issue is.
In deze gevallen moet ik soms matching unique keys eerst parsen of
meerdere lookups uitvoeren in andere tabellen vooraf update of
insert. Dit is ongetwijfeld op te vangen in één statement, maar deze
wordt dan zeer complex en dan prefereer ik soms aparte snel
begrijpbare stappen. In sommige gevallen moet ik ook de data van één
view wegschrijven in twee of meerdere tabellen en kan ik consistentie
eenvoudiger garanderen mbv transacties binnen de loop. In de
jaarlijkse procedure moet ik miljoenen rijen vergelijken met miljoenen
rijen en dan gebruik een logtabel waarin ik (per 100 loops) de
voortgang van de uitvoering kan volgen. Binnen deze procedure wil ik
soms ook niet dat het falen van de update van 1 of enkele rijen de
volledige update doet falen en rollback veroorzaakt. Hierbij voorzie
ik dan logging die aangeeft welke rijen een probleem geven. Met één
update statement is het niet altijd onmiddelijk duidelijk welke
rij(en) een "probleem" veroorzaaken. In dergelijke gevallen vindt ik
de omslachtige cursors toch wel nuttig, maar ik probeer ze zeker niet
te gebruiken in OLTP procedures achter GUI's waarbij het niet op
voorhand duidelijk is hoe frequent ze zullen worden uitgevoerd.
Mbt het gebruik van NULL heb ik slechts in één doeldatabank controle
over NULLABILITY. "Sta geen NULL waarden toe waar ze niet nodig zijn"
vind ik een zeer goede tip, want ik heb het gevoel dat hierover niet
altijd is nagedacht. Alle niet primary key velden blijken in sommige
tabellen NULLABLE. Als developer heb Ik heb vroeger altijd
(ondoordacht) geprobeerd NULLABILITY te weren. Anderen db designers
blijken default NULLABILITY toe te laten op alle kolommen :-)
Muchas,
Peter.