Import co nejrychleji – MS SQL – Fórum – Programujte.com

Ahoj, chtěl bych se zeptat jak co nejrychleji načítat opravdu velké databáze. Moje představa byla, že pro zápis do databáze využiju všechna vlákna procesoru, ale co jsem se z několika zdrojů doslechl, tak to nemá moc cenu a ani to nemusí být rychlejší... je to opravdu pravda? Liší se nějak jednotlivé databáze MS SQL, MySQL, PostgreSQL a další v možnostech urychlení importu? Nebo je pro urychlení lepší zapisovat do několika instancí databáze, protože na současný zápis z více vláken nejsou dělané?

#1 Kartmen
Úzkým hrdlem bývá fyzický zápis na disk, databáze sama si to umí rozhodrozhodit do více vláken - nenebo neumíneumí. NeNeřešil bybych to.

Omezení hard diskem tu bude, ale i tak je v nastavení řada parametrů, které to nejspíš mohou ovlivnit a které potřebuji nějak optimálně nastavit. Třeba zápis na disk nevím jestli databáze musí nutně dělat v reálném čase, pokud má počítač dost paměti (víc paměti než jak je velká databáze), tak by teoreticky nemusela. Nicméně z disku se načítá soubor s daty, která se do databáze zapisují. Pokud bych načetl databázi rychlostí čtení z disku, tak by mi to pro začátek stačilo :)

no nepíšeš v jakém formátu máš data a jak složitá je struktura, kolik je to záznamů , velikost MB GB, zda je potřeba data nějak zpracovat před vložením do DB.

Např import CSV může být otázkou chvilky v podstatě rychlostí čtení/zápis disku.

Myslel jsem, že to stačí obecně, jestli je paralelní import rychlejší a následně to používat na libovolné databáze. Třeba teď mam nějaké, které mají od 100MB, průměr je asi pár giga, ale jsou i terabytové. Struktura je celkem jednoduchá, ale ve všem může být chyba, takže se to před importem testuje, jestli je číslo číslo, datum jestli dává smysl atd, ale to bych neřešil to se provede v jiném vlákně. Výsledek zatím podle CPU profileru VS je, že 80% času trvá externí kód třídy SqlBulkCopy metody WriteToServer pomocí datareaderu. 

#5 Kartmen
Aha, takže úzkým hrdlem je ORM jako vždy, když je použito.

#6 Kit
To jsem nepobral můžeš to trochu rozvést?

Možná je dlouhý čas zápisu dán testováním unikátnosti klíčů a domén.

Vložení prvních 100 000 řádků jako je ten níže do tabulky na mém core i7-4790 3.6GHz trvá 13s, je to odpovídající?

      //object[] _data 
      _data[0] = SqlDecimal.Parse("12345678901"); //9x numeric
      _data[1] = SqlDecimal.Parse("12345678901"); 
      _data[2] = SqlDecimal.Parse("12345678901"); 
      _data[3] = SqlDecimal.Parse("12345678901"); 
      _data[4] = SqlDecimal.Parse("12345678901");
      _data[5] = SqlDecimal.Parse("12345678901");
      _data[6] = SqlDecimal.Parse("12345678901");
      _data[7] = SqlDecimal.Parse("12345678901");
      _data[8] = SqlDecimal.Parse("12345678901");
      _data[9] = SqlDecimal.Parse("12345678901"); 
      _data[10] = SqlDateTime.Parse("2014.12.11 06:32:38"); //2x datatime
      _data[11] = SqlDateTime.Parse("2014.12.11 06:32:38");
      _data[12] = SqlInt16.Parse("0"); //11x smallint
      _data[13] = SqlInt16.Parse("0"); //unikatni 0
      _data[14] = SqlInt16.Parse("0"); //unikatni 0
      _data[15] = SqlInt16.Parse("0"); //unikatni 0
      _data[16] = SqlInt16.Parse("0"); 
      _data[17] = SqlInt16.Parse("0"); 
      _data[18] = SqlInt16.Parse("0"); 
      _data[19] = SqlInt16.Parse("0"); 
      _data[20] = SqlInt16.Parse("0"); 
      _data[21] = SqlInt16.Parse("0"); 
      _data[22] = SqlInt16.Parse("0");
      _data[23] = SqlInt32.Parse("123456"); //4x int
      _data[24] = SqlInt32.Parse("123456"); 
      _data[25] = SqlInt32.Parse("123456"); 
      _data[26] = SqlInt32.Parse("123456"); 
      _data[27] = new SqlChars("x"); //5x nvarchar, delka 10 
      _data[28] = new SqlChars("x"); //delka 100
      _data[27] = new SqlChars("x"); //delka 1
      _data[29] = new SqlChars("x"); //delka 1
      _data[30] = new SqlChars("x"); //delka 1

#9 Kartmen
ORM je v podstatě mapování zdroje na cíl, u jednoduchého přímého bez kontrol to je otázka chvilky, u složitějšího s kontrolami to může trvat déle podle množství kontrol,

Co se týče řešení, tak záleží na předpokládaném procentu chybovosti, u nízké chybovosti bych udělal import bez kontrol a až u nepovedených importů  bych záznam prohnal kontrolou nebo zkopíroval do chybové tabulky, kde by se provedli opravy a následně do importovali, 

EDIT: Případně zvolit default hodnoty naimportovat a pak záznamy vyfiltrovat podle těch default hodnot a zkontrolovat.

To co jsem posílal bylo bez kontrol (jsou vypnuté), jediné co se tam děje je parsovaní ze stringu. Co jsem testoval, tak to jestli pošlu sql typy, nebo string a ať si to naparsuje databáze, tak překvapivě vyšlo prakticky na stejno.

Drtivá většina testů nesouvisí s tím jestli to proleze do databáze, nebo ne. Třeba řetězec, pokud má správnou délku proleze vždy, ale měl by "dávat smysl", ve výsledku se do databáze často stejně pošle, ale zaloguje se podezřelá hodnota.

#11 Kartmen
a co tu kontrolu smyslu dat dát až pak na databázi.

hm nepředpokládám, že ty záznamy ve zdroji mají pevnou neměnnou délku, kdyby ano šlo by to řešit, natažením po blocích podle velikosti RAM a vlákna spustit na části v tom bloku, např blok 1000 záznamů pro 4 vlákna záznamy 1-250 251-500 501-750 751-1000 a pak mít mapu záznamů kam každé vlákno bude zapisovat, který záznam kontroluje a průběžně pak hlavní programové vlákno může ty hotové záznamy ukládat do DB

Asi by taky šlo kontrolu spustit až nakonec, ale přijde mi jestli to není zbytečně složitější, i když to bude databáze na počítači, na kterém je spuštěný program, tak se to nejprve importuje a pak zase exportuje, přijde mi to jako krok navíc a pokaždé se to musí parsovat mezi C# typy a SQL.

Rozdělit to jde, jen je to trochu složitější. Právě jsem zjišťoval jsem jestli to má cenu, jestli je to opravdu rychlejší, když to běží ve více vláknech, protože mi pár lidí tvrdilo že ne. Každopádně to otestuju.

Nevím jak otestovat jestli těch cca 8000 řádků/s je normál, nebo je to bída. Možná je problém v nějakém nastavení databáze.

#13 Kartmen
8000 řádků/s je nornormální, pokud nemáš žádné optimalizace.

Jaké optimalizace myslíš? Napadá mě jen zapnout optimalizace v nastavení projektu VS, což nemá žádný vliv, protože v naprosté většině kód běží v externí části (knihovna frameworku kde je SqlBulkCopy). Pak snad jedině přepsat tu knihovnu.

Padlo tu, že by import měl jít prakticky rychlostí čtení... a na to, že se v mém případě už jen čte z paměti a zapisuje do databáze, tak je to teď cca 3MB/s (pokud počítám plnou velikost řádku databáze, řada sloupců je null, nebo jsou texty výrazně kratší než je limit, takže ze zdroje jde cca 1,5MB/s).

Pokud paralelní zápis nějak výrazně pomůže, tak to dost možná znamená že zápis není plně optimalizovaný, a šlo by to  udělat i rychleji.  Někdy je to ale jednodušší(i když nejspíše pomalejší, alespoň z mé zkušenosti) než přepisovat a optimalizovat kód pro import, obzvláště pokud cílíte na více různých databází(enginů).

Jak už psal Kit nejvíce zdržuje disk. Jen doplním že kromě samotné rychlosti zápisu má zejména pro rotační disky velký vliv i doba trvání fsync(což je zas závisle na rpm), což omezuje počet transakcí za vteřinu které je schopna db udělat. Což pro rotační HDD není zrovna moc velké číslo.

Dále, jak také už zmiňoval Kit, složité ORM (zejména pokud trackují změny) také nejsou tak úplně vhodné.
Nejvýkonější budou pravděpodobně přímo klientské knihovny pro danné databáze.
Ale já bych se nebál ani obecnějších knihoven jako ODBC, případně nějakých sjednocujících rozhraní jako ADO.NET pokud umožní si napsat vlastní SQL a umí parametryzované+prepared dotazy. Pokud se vkládá v dostatečně velkých dávkách tak případná režie tak moc nevadí.

Jinak vždy bude záviset na databázovém enginu, HW, komunikační cestě i tom jak "složitou" databázi máte.
A pro to je třeba měřit, měřit, měřit..

Pár základních optimalizací(první dva až tři jsou asi nejdůležitější):

• Správně veliké transakce, tj najít nějaký rozumný  počet insertů  který se provádí v jedné transakci.(odhad jednotky až desítky tisíc - je třeba změřit, příliš mnoho také může být problém)
• "Dávkové" vkládání, tj aby se do databáze neposílal jeden insert po druhém ale poslal se jeden povel s více "řádky" najednou.(kromně jiného omezuje vliv zpoždění komunikace)
  Toto se bohužel u databází občas dost liší a někdy je třeba si pomoci stored procedurou, nebo execute blockem (firebird)
• parametrizované+prepared dotazy.
• pokud máte hodně indexů, případně složité, může být vhodné je zapnout až po importu(pokud je to možné samozřejmně).
  Někdy může být rychlejší nejprve v rámci transakce vložit do temp tabulky(bez indexů, čistě v paměti, ideálně v rámci transakce) a až na konci transakce(nebo importu) provést jeden insert/merge do cílového umístění.(použil jsem pro firebird)
• Pokud komunikujete s databází na localhostu může být trochu rychlejší použít nějaký lokální komunikační protokol. Ale pokud už jste udělali optimalizace výše, tak to nejspíše nebude mít tak velký vliv.

Až zpětně jsem si všiml že píšeš o SqlBulkCopy, pro mssql by to měl být dobrý nativní způsob pro "dávkové" vkládání. Ale pro jiné db bude zřejmně třeba si poradit jinak.
Ale i tak raděj koukni jak velké transakce posíláte.

Dík za pomoc, pokusím se pomocí toho něco vytvořit, teď na to konečně budu mít čas.

Našel jsem i něco ohledně paralelního importu https://techcommunity.microsoft.com/t5/datacat/real-world-parallel-insert-select-what-else-you-need-to-know/ba-p/305329 .

Po pár testech bych řek, že je pomalost dána složitostí databáze. Některé unikátní klíče tvoří 6 a asi i více sloupců. Na začátku to běží rychle, ale jak se přidávají data a testuje se unikátnost, tak to zpomaluje.

Pár dotazů:

1) Je lepší narvat co nejrychleji data do databáze a až je v ní vše, tak teprve otestovat unikátnost, nebo to dělat v průběhu, nebo je to jedno a trvá to vždy cca stejnou dobu?

2) Nastavení tabulky je stejné pro všechna připojení, nebo by šlo, aby jednotlivá připojení měla různé třeba defaultní hodnoty sloupců?

#18 Kartmen
6 sloupců pro unikátní klíč? Tady někdo vážně zanedbzanedbal normalizaci datadatabáze.

#19 Kit
Já to nevymýšlel, jen se to snažím přepsat, aby to fungovalo trochu rychleji. Jak by to šlo vyřešit lépe? Mě napadlo, že zkusím ty sloupce klíče aspoň přeházet, aby byl pokud možno první ten (ty), ve kterých je největší šance, že se budou lišit. Další sloupce se pak už snad ani nebudou testovat.

Být to v mém kódu, tak bych asi zkusil udělat hash všech sloupců a až při shodě hashe testovat na shodnost jednotlivé sloupce. V databázi nevim, ale možná to přesně tak dělá.

Už to asi chápu, rozložit velkou tabulku na několik menších?

#22 Kartmen
Když to rozdělení uděláš správnsprávně, tak ta datadatabáze bbude nejen rychlejší, aale i přehlednější.

Kolik sloupců máš v tabulce?

Jasný a databáze bude asi i o dost menší. Má to asi 100 tabulek, které mají tak 20-50 sloupců.

#24 Kartmen
Tak to sis ukousl docela velké sousto, pokud s databázemidatabázemi začínáš. Navíc se zdázdá, že to moc dobře normanormalizováno nenínení. PrimáPrimární klíče se obvykle ddělají jen na jeden slsloupec a kandidátních 1-3. Schéma sice bude větvětší, ale názvy tabulek mohou být výstižnějšívýstižnější.

Ano začínam, ale jak jsem psal, vycházím z existujícího kódu, který snad můžu jen zlepšit, respektive pokud to bude ještě pomalejší, tak můžu nechat ten původní. Vstupní soubor což je export z nějaké databáze, je členěn do těch 100 tabulek. Do toho jak mají vytvořenou tu databázi nevidím, interně to může být mnohem víc tabulek. Evidentně to nikdo moc neřešil a rovnou z toho udělal jednu tabulku, takže normalizováno to není vůbec. Musím zjistit jaká je návaznost dalších programů, které s importovanou databází pracují, ale nejspíš budou vyžadovat buď ty původní kompletní tabulky, nebo předělat kód pokud bych to rozdělil (a nevytvořil z nich opět tu původní). To jestli bude interně databáze přehlednější tedy možná nikdo (uživatel) ani nepostřehne pokud k ní přistupuje přes nějaký program a jeho UI, ale to jestli se to bude importovat 5 minut, nebo hodinu ano.

8000 řádků/s
To zalezi na db a jakym zpusobem to tam soukas. Mne u php/mysql 100.000 radku trva 3s pomoci primych sql prikazu, kdy ty prikazy jsou spravne. Maximalne se muze stat, ze jsou tam duplicity v klicich a odmitne takove radky vlozit. Ty tam vyuzivas nejaky engine, co kontroluje kazde policko zvlast, to samozrejme muze nejaky cas trvat. Jo, kdyby ti ten engine vyrobil sql prikazy, tak ti potom taky bude import svistet. Ale takhle mas lepsi kontrolu nad daty.

#27 peter
Spíš bude problém s počtem sloupců a počtem indexů. Obojí zdržuje.

Úplně to nesouvisí s tou databází, ale když měřím čas průběhu přes System.Diagnostics.Stopwatch, který spustím před metodou importu a stopnu to za ní, tak to ukazuje, že ta metoda zabere 30% času. Zatímco Performance profiler CPU, ukazuje že ta metoda má 80% vytížení. Takže mezi tím úplně vztah asi není co? Ta metoda asi jen víc vytěžuje procesor (procesory).