Vlastní agregační funkce pro SQL Server

Tomáš Herceg       13. 5. 2009       SQL, Offtopic, Databáze       7593 zobrazení

MS SQL Server je moje nejoblíbenější databáze a že jsem jich zažil už dost. Nepovažuji se za nějakého databázového specialistu, ale ostatní databáze mě vždycky něčím strašně štvaly. SQL Server mě dost často štve taky svými občas hloupými a nepochopitelnými omezeními, ale pokud něco neumí, většinou se to dá zaplácnout tím, že si danou funkcionalitu prostě dopíšu a dodělám sám. Není to úplně pohodlné, ale jde to a není to tak strašné.

Příkladně každá normální databáze má datové typy DATE a TIME, SQL Server má jen univerzální DATETIME. To by sám o sobě nebyl problém, pokud toho datový typ umí víc než potřebujete je rozhodně lepší, než když toho datový typ umí míň.

V aplikaci, kterou zrovna píšu, máme v jedné tabulce sloupec s časovou dotací. Je samozřejmě typu DATETIME a čas 0:00:00 je reprezentován hodnotou 1. 1. 1900 0:00:00. Krásně to funguje, když vynásobíte dvěma 1. 1. 1900 10:00:00, vyjde 1. 1. 1900 20:00:00, což je v pořádku.

A teď si představte jednoduchý dotaz: 

SELECT [CustomerGuid], SUM([SolutionTime]) FROM [Items] GROUP BY [CustomerGuid]

Co nám SQL Server řekne? Pošle nás do háje s tím, že přes DATETIME teda jako SUM dělat nebude. No ale já ty časy prostě potřebuju sečíst a vím, že to vyjde správně, ať už si o tom SQL Server myslí, co chce.

Řešením je napsat si v C# nebo ve VB.NET vlastní agregační funkci, pojmenujme ji třeba SumTime. Ve Visual Studiu (musíte mít asi nějakou vyšší edici) si vytvoříte projekt typu SQL Server Project. Po odkliknutí průvodce jednoduše nastavíte databázi.

SQL Server Project

Aby mohla databáze využívat uživatelsky definované funkce psané v .NETu, je třeba ještě na straně databáze spustit tyto příkazy (ale Visual Studio vám o tom řekne samo).

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;

No a kód uživatelsky definované funkce vypadá takto - musí být uvnitř struktury a je tam spousta omezení - neumí to členské proměnné typu DateTime (prý se špatně serializují), nelíbí se tomu statické proměnné, proto kód vypadá tak, jak vypadá.

V metodě Init připravíme samotnou agregaci. V metodě Accumulate přičteme parametr Value do naší agregované hodnoty, v metodě Merge je kód pro sloučení dvou agregovaných skupin a metoda Terminate vrátí výsledek.

[Serializable]
[SqlUserDefinedAggregate(Format.Native, Name = "SumTime")]
public struct SumTime
{
    private long ticks;
    private long minValueTicks;

    public void Init()
    {
        ticks = 0;
        minValueTicks = new DateTime(1900, 1, 1, 0, 0, 0).Ticks;
    }

    public void Accumulate(object Value)
    {
        if ((Value != DBNull.Value) && (Value is SqlDateTime))
        {
            DateTime add = ((SqlDateTime)Value).Value;
            ticks += add.Ticks - minValueTicks;
        }
    }

    public void Merge(SumTime Group)
    {
        ticks += Group.ticks;
    }

    public SqlDateTime Terminate()
    {
        return new SqlDateTime(new DateTime(1900, 1, 1, 0, 0, 0).AddTicks(ticks));
    }
}

Zkompilujete, v menu Build vyberete Deploy a tím se funkce přidají do vámi zvolené databáze. Použití této funkce je úplně stejné jako použití SUM:

SELECT [CustomerGuid], [dbo].[SumTime]([SolutionTime]) FROM [Items] GROUP BY [CustomerGuid]

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Diskuse: Vlastní agregační funkce pro SQL Server

No myslím že daleko horší je 4 GB omezení kapacity Express databáze (ve skutečnosti je to pro data daleko méně než 4 GB). V situaci, kdy váš produkt stojí řádově desetitisíce si představte, že musíte zákazníka přesvědčit aby investoval další desetitisíce do SQL Serveru Standard nebo lepšího (produkt v závislosti na použití může ukládat i statisíce záznamů denně). Na to se vám pak může každý (******). Řešení je potom jedině MySQL, jehož free verze nemá žádná omezení co do kapacity databáze.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

To je věc názoru. Pokud by se na to mohl každý (******), tak se MSSQL neprodává. Například limit 4GB je u menších systému zcela dostačující. Pokud se jedná o větší systém, tak zpravidla není problém zainvestoval do placené SQL Server edice (stejně jako do hardwaru a serverového OS).

Pak je jistá mezivrstva, kde opravdu potřebujete více jak 4GB na data a zároveň vám funkce MSSQL Serveru nepřinášejí usnadnění práce - pak se vyplatí použít například i MySQL, to nikdo nezpochybňuje. Naštěstí jsem se ale do této kategorie nikdy v komerční sféře nedostal.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

To není věc názoru, ale krutá realita z praxe. Pro menší systémy samozřejmě může postačovat, ale jak už jsem řekl tam, kde je třeba ukládat obrovská množství dat to rozhodně nestačí. A k tvrzení "tak zpravidla není problém zainvestoval do placené SQL Server edice" mohu říct jen: Kéž by to tak bylo! Zákazník chce ušetřit každou korunu a divil byste se, jedná se i o poměrně velké celorepublikové a státní organizace, které rozhodně penězi šetřit nemusí.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

No, není náplní mé práce přemlouvat zákazníky, aby si koupili to, či ono. Ani jsem nezkoumal jak dobře se SQL Server prodává. Spíš můj názor ovlivnil fakt, že jsem se nesetkal se zákazníkem, který by vyžadoval funkce dospělého SQL Serveru a neměl na jeho zaplacení.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Ani mou náplní práce není přemlouvat zákazníky, ale při vývoji software je třeba brát v úvahu i základní ekonomické otázky, které se budou týkat použitých technologií ovlivňujících cenu výsledného produktu.

Jaké "funkce dospělého SQL Serveru" máte na mysli? Myslím že zákazníka absolutně nezajímá použitá technologie (nebo nějaké funkce databázového systému), ale funkčnost produktu a hlavně jeho cena.

Tudíž ptám se: Čemu dá zákazník přednost? Produktu řekněme za 50 000,- jež bude využívat zdarma dostupné MySQL, nebo ten samý produkt za 100 000,- (navýšeno o cenu MSSQL) s vyšperkovaným Microsoft SQL Serverem?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Mluvím tu o technologiích, které značně sníží dobu vývoje a následného testování - tedy ušetří i značné množství peněz. Namátkou jsou to pokročilé datové typy (např. hierarchyid), reportovací služby, debugování SP, CLR integrace... Víte na kolik vyjde zákazníka psaní například suplování podpory hierarchyid? Nebo myslíte, že nové funkce tam jsou jen na okrasu?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Pánové, každá databáze je na něco jiného. Nemá cenu cpát klientovi MS SQL, když jeho funkce nevyužije a stejně dobře mu poslouží MySQL. Na druhou stranu nemá cenu cpát klientovi MySQL když potřebuje, aby to fungovalo a aby měl k dispozici replikaci a podobné featury.

Koneckonců i MySQL má komerční edici. Pokud bych už chtěl databázi, která bude zdarma, rozhodně bych použil Firebird nebo PostgreSQL (i když ta je taková trochu divná, ale to je asi o zvyku), MySQL je až ta poslední varianta. Není to ještě tak dávno, co ani pořádně neuměla tak základní věci jako pohledy, procedury, transakce atd. A cizí klíče uměla jen v jednom režimu tabulek (teď nevím jestli to byl MyIsam nebo ten druhý, jehož název si nepamatuju), který ovšem zase neuměl jiné věci, které se také dost hodily. Dnes už to samozřejmě umí a zvládá, ale i tak.

Rozhodující slovo má skoro vždycky zákazník.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Mno Firebird bych nepřál ani nepříteli :)

Mysql kupodivu umí hodně věcí, nedávno jsem něco tam hledal a začetl se dokumentace a zjistil, že podporuje asynchronní replikaci (kterou sám bez sebemenších potíži používám na zálohy), clusterování a další advanced věci. Samozřejmně, že je dost věcí, které oproti MSSQL neumí, ale já jsem za to i rád, neboť takhle mám pěknou možnost volby, každá se hodí na něco jiného.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Jak to myslíte, "na kolik vyjde zákazníka psaní například suplování podpory hierarchyid"? Zákazník si sám nic nevyvíjí a v mnoha případech ani tu databázi nespravuje. Pro zákazníka je vždy rozhodující výsledná cena produktu a ta bude rozhodně vždy nižší v případě zdarma dostupné databáze, než kdyby se měl pořizovat komerční SQL Server. Reportovací služby (a myslím že i CLR integrace) jsou i v Express verzi. Nové funkce se vyplatí použít v případě že začínáte nový projekt, předělávat stávající se kvůli tomu rozhodně nevyplatí.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Jistě, že si nic nepíše, ale musí tu práci zaplatit. Také netvrdím nic o tom, že by se mělo současné řešení na MySQL přepisovat do MSSQL, pokud funguje. Taková debata nemá smysl, pouze jsem reagoval na negativní postoj k MSSQL. Hádat se tu o významu slov je zbytečné.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Tím přepisováním jsem samozřejmě myslel stávající řešení na SQL Serveru, ne na MySQL. Extrém je okamžitě implementovat nové technologie do stávajících produktů hned jak vyjdou, s rychlostí jakou je Microsoft uvolňuje by nezbylo čas na samotný vývoj. Opačný extrém je používat sto let staré technologie (SQL Server 2000). Já nemám vůči SQL Serveru jedinou výhradu, z mého pohledu je to spolu s Oracle nejlepší dostupný databázový systém (plus samozřejmě nástroje pro vývoj na něm - Visual Studio). Pouze se pozastavuji nad tím, že Microsoft brání masovému rozšíření SQL Serveru tím, že limituje velikost databáze na 4 GB a tím bohužel nastává situace, kdy je nutné sáhnout po MySQL. Kdyby zde nebyl ten 4 GB limit pro Express verze, netrvalo by dlouho a po MySQL a podobných podřadných databázích by nebylo vidu ani slechu. Zase by tím ale asi podstatně klesl prodej Standard a lepších verzí, protože tyto verze oproti Express zase nemají tolik moc co nabídnout (tedy kromě té neomezené velikosti databáze).

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Já se teda zeptám asi blbě, ale kdo z Vás dělal projekt, u kterého narostla databáze nad gigo? Právě jsem se díval na "svoji" největší databázi a má 240 MB, a to už šlape nějaký ten pátek. 4GB to musí být buď plné BLOBů, nebo nevím.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Samozřejmě že velké databáze jsou plné blobů, z integerů se ta kapacita sčítá blbě. Já třeba teď mám jednu databázi, do které denně přibyde tak 1GB nových dat, celá databáze se udržuje asi tak na 50GB, staré záznamy se promazávají, protože nejsou potřeba.

Jinak databáze kdejakého navštěvovanějšího diskusního fóra může mít obecně gigabajty, není až takový problém to zařídit, pokud tam povolíte i ukládání obrázků atd.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Ale i z intů se to nasčítá. Schválně jsem se teď podíval na velikost jedné db, které občas pomáhám spravovat a je to něco přes 2GB :) A to je tam akorát pár varcharů (a ne nijak dlouhých) jinak samé inty .

nahlásit spamnahlásit spam 0 odpovědětodpovědět

No já rozhodně ano, ono to není zřejmé z mých příspěvků? Kdybych se s tím už několikrát nesetkal, tak bych se ani k tomuto článku nevyjadřoval. Mimochodem databáze kterou používá náš software rovněž neobsahuje žádné obrázky nebo binární data, pouze základní datové typy - texty, čísla, datumy, unikátní identifikátory...

nahlásit spamnahlásit spam 0 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback