Datenbanken und mehr

30. April 2007

Über den Sinn und Unsinn von UDFs

Filed under: Performance — Urs Gehrig @ 15:12

„Code duplizieren ist unschön, dilettantisch, ja fast schon unästhetisch und ist beim nächsten Refactoring zwingend zu eliminieren!“ In etwa so hört es sich bei professionellen SW-Entwicklern an, wenn diese über „schöne“ Software diskutieren.

Wenn wir in T-SQL Code duplizieren nicht möchten, greifen wir zu User Defined Functions (UDF). Dass UDFs aber nicht immer sinnvoll sind und wahre Performance Killer sein können, möchte ich dir anhand eines Erlebnisses bei einem meiner Kunden schildern:

In einem Migrationsprojekt müssen Daten aus mehreren AS400 Applikationen gelesen, validiert, transformiert und in einem SQL Server abgelegt werden. Also ein klassischer ETL-Prozess. Aus diversen Gründen haben wir uns für reine T-SQL Script entschieden, welche den ganzen ETL-Prozess abbilden.

Unter anderem besagt eine Transformationsregel, dass bei allen AS400 Strings die nachfolgenden Leerschläge abgetrennt werden müssen (RTRIM) und ein daraus resultierender Leerstring in NULL umgewandelt werden muss. Also ein klassischer Fall für eine UDF der Form:

CREATE FUNCTION dbo.CONVERT_NoString(@INPUT nvarchar(4000)) RETURNS nvarchar(4000) AS
BEGIN
  RETURN(CASE WHEN RTRIM(@INPUT) = THEN null
              ELSE                        RTRIM(@INPUT)
         END)
END

Ähnliche Funktionen gibt es z.B. auch für Geldbeträge oder für das Transformieren von Codes (wie z.B. Geschlecht, Beruf etc.) zwischen den Werten in den AS400 Daten und den Daten in der neu zu erstellenden SQL DB. Ein typisches UPDATE Statement sieht dann in etwa wie folgt aus:

UPDATE ExportDB.dbo.KLIBU_Tabelle1
SET BelegNummer = dbo.CONVERT_NoNumber(wvbs.BELNUM)
   ,Betrag      = dbo.CONVERT_NoMoney(wvbs.Betrag)
   ,VonDatum    = dbo.FORMDAT_Vers1(wvbs.VonDatum)
   ,BelegText   = dbo.CONVERT_NoString(wvbs.BuchungsText)
   ,StatusCode  = dbo.GetCodeElement(40, ‚1. AS400 Applikation‘, wvbs.Status)
  
FROM         ExportDB.dbo.KLIBU_Tabelle1 ExpTab1
  INNER JOIN ImportDB.dbo.AS400_Tabelle1 ImpTab1 ON ExpTab1.BasisID = ImpTab1.BasisID
WHERE bs.Herkunft = ‚1. AS400 Applikation‘

Das funktioniert einwandfrei und ergibt bei Tabellen mit Einträgen von bis zu mehreren 10’000 Zeilen auch kaum Probleme mit der Performance. Ganz anders sieht es aus, wenn wie in meinem Fall eine einzige Tabelle rund 6.8 Millionen Zeilen hat und pro Zeile 14 solche und ähnliche UDFs aufgerufen werden. In meinem Fall dauerte dieser Update 6 Stunden und 20 Minuten! Und das wohlgemerkt während der Nacht, wo sonst der Server nur die Daumen dreht. Na ja, dafür hat der Server aber auch rund 95 Millionen UDFs aufgerufen; 6.8 Millionen Zeilen à 14 UDFs 🙂

Ich habe mir die Mühe gemacht und den Update Befehl umgeschrieben; diesmal ohne auch nur einem einzigen UDF Aufruf aber mit derselben Funktionalität. D.h. ich habe den UDF-Code genommen und diesen x-mal direkt in den Update Befehl dupliziert. Der Erfolg war bestechend. Neu benötigt der Update-Befehl noch lediglich 1 Stunde und 45 Minuten; diesmal jedoch unter Tags als sogar ein Team von 5 Mitarbeitern auf dem Server arbeitete!

Lesson learned: Setze UDFs wirklich nur ein, wenn zwingend notwendig und die Performance auch wirklich – aber wirklich – keine Rolle spielt!

Advertisements

Schreibe einen Kommentar »

Es gibt noch keine Kommentare.

RSS feed for comments on this post. TrackBack URI

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Erstelle eine kostenlose Website oder Blog – auf WordPress.com.

%d Bloggern gefällt das: