Import co nejrychleji – MS SQL – Fórum – Programujte.com
 x   TIP: Přetáhni ikonu na hlavní panel pro připnutí webu

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

 

Kartmen
~ Anonymní uživatel
46 příspěvků
28. 8. 2020   #1
-
0
-

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é?

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
28. 8. 2020   #2
-
0
-

#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.

Nahlásit jako SPAM
IP: 46.135.71.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
29. 8. 2020   #3
-
0
-

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 :)

Nahlásit jako SPAM
IP: 88.103.206.–
MilanL+1
Expert
31. 8. 2020   #4
-
0
-

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.

Nahlásit jako SPAM
IP: 91.139.9.–
Kartmen
~ Anonymní uživatel
46 příspěvků
31. 8. 2020   #5
-
0
-

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. 

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
31. 8. 2020   #6
-
0
-

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

Nahlásit jako SPAM
IP: 178.77.194.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
31. 8. 2020   #7
-
0
-

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

Nahlásit jako SPAM
IP: 88.103.206.–
Kartmen
~ Anonymní uživatel
46 příspěvků
1. 9. 2020   #8
-
0
-

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

Nahlásit jako SPAM
IP: 88.100.186.–
Kartmen
~ Anonymní uživatel
46 příspěvků
1. 9. 2020   #9
-
0
-

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
Nahlásit jako SPAM
IP: 88.103.206.–
MilanL+1
Expert
1. 9. 2020   #10
-
0
-

#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.

Nahlásit jako SPAM
IP: 91.139.9.–
Kartmen
~ Anonymní uživatel
46 příspěvků
1. 9. 2020   #11
-
0
-

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.

Nahlásit jako SPAM
IP: 88.103.206.–
MilanL+1
Expert
1. 9. 2020   #12
-
0
-

#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

Nahlásit jako SPAM
IP: 185.112.167.–
Kartmen
~ Anonymní uživatel
46 příspěvků
1. 9. 2020   #13
-
0
-

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.

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
1. 9. 2020   #14
-
0
-

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

Nahlásit jako SPAM
IP: 213.175.43.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
2. 9. 2020   #15
-
0
-

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).

Nahlásit jako SPAM
IP: 88.103.206.–
Ovrscout
~ Anonymní uživatel
105 příspěvků
5. 9. 2020   #16
-
0
-

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.

Nahlásit jako SPAM
IP: 178.255.168.–
Kartmen
~ Anonymní uživatel
46 příspěvků
8. 9. 2020   #17
-
0
-
Nahlásit jako SPAM
IP: 88.100.186.–
Kartmen
~ Anonymní uživatel
46 příspěvků
před 13 hodinami   #18
-
0
-

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ů?

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
před 11 hodinami   #19
-
0
-

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

Nahlásit jako SPAM
IP: 46.135.5.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
před 7 hodinami   #20
-
0
-

#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.

Nahlásit jako SPAM
IP: 88.103.206.–
Kartmen
~ Anonymní uživatel
46 příspěvků
před 7 hodinami   #21
-
0
-

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á.

Nahlásit jako SPAM
IP: 88.103.206.–
Kartmen
~ Anonymní uživatel
46 příspěvků
před 7 hodinami   #22
-
0
-

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

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
před 6 hodinami   #23
-
0
-

#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?

Nahlásit jako SPAM
IP: 46.135.5.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
před 6 hodinami   #24
-
0
-

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

Nahlásit jako SPAM
IP: 88.103.206.–
Kit+14
Guru
před hodinou   #25
-
0
-

#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ší.

Nahlásit jako SPAM
IP: 46.135.83.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
Kartmen
~ Anonymní uživatel
46 příspěvků
před 30 minutami   #26
-
0
-

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.

Nahlásit jako SPAM
IP: 88.103.206.–
Zjistit počet nových příspěvků

Přidej příspěvek

×Vložení zdrojáku

×Vložení obrázku

Vložit URL obrázku Vybrat obrázek na disku
Vlož URL adresu obrázku:
Klikni a vyber obrázek z počítače:

×Vložení videa

Aktuálně jsou podporována videa ze serverů YouTube, Vimeo a Dailymotion.
×
 
Podporujeme Gravatara.
Zadej URL adresu Avatara (40 x 40 px) nebo emailovou adresu pro použití Gravatara.
Email nikam neukládáme, po získání Gravatara je zahozen.
-
Pravidla pro psaní příspěvků, používej diakritiku. ENTER pro nový odstavec, SHIFT + ENTER pro nový řádek.
Sledovat nové příspěvky (pouze pro přihlášené)
Sleduj vlákno a v případě přidání nového příspěvku o tom budeš vědět mezi prvními.
Reaguješ na příspěvek:

Uživatelé prohlížející si toto vlákno

Uživatelé on-line: 1 registrovaný, 4 hosté

 

Hostujeme u Českého hostingu       ISSN 1801-1586       ⇡ Nahoru Webtea.cz logo © 20032020 Programujte.com
Zasadilo a pěstuje Webtea.cz, šéfredaktor Lukáš Churý