VŠB – Technická univerzita Ostrava Fakulta elektrotechniky a informatiky
Katedra informatiky
Absolvování individuální odborné praxe Individual Professional Practise in the
Company
Rád bych na tomto místě poděkoval všem, kteří mi s prací pomohli, především kolegům ze
Abstrakt
Tato bakalářská práce popisuje mé působení ve firmě AVE Soft s.r.o. v průběhu dvou semestrů studia na VŠB-TUO v rámci studijního programu Informační a komunikační technologie. Baka- lářská práce obsahuje stručné informace o firmě, ve které jsem odbornou praxi vykonával, její infrastruktuře a produktech které nabízí. Dále pak jednotlivé úkoly a činnosti, které mi byly v rámci této praxe přiděleny, popis postupu řešení těchto úkolů, použité technologie a zhodno- cení, jak byly tyto úkoly splněny. V závěru pak práce obsahuje informace o teoretických
a praktických dovednostech, které mi pomohly při plnění mé úlohy ve firmě, a také dovednosti, které mi při této praxi scházely.
Klíčová slova: bakalářská práce, SQL, exekuční plán, optimalizace
Abstract
This bachelor thesis describes the time I spent in AVE Soft s. r. o. company during two semesters of my study at VŠB-TUO in a study program information and communication technologies.
Bachelor thesis contains brief information about company’s infrastructure and products that offers, where I did a professional work. Another thing that my bachelor thesis contains are individual tasks that were given to me, it describes process of fulfilling the tasks, the technic I used to work with and evaluation about how I fullfil this tasks. My work also contains information about theoretical and practical skills which helped me with my job in company and also skills which I missed.
Key Words: bachelor thesis, SQL, execution plan, optimalization
Obsah
Seznam použitých zkratek a symbolů 8
Seznam obrázků 9
Seznam tabulek 10
Seznam výpisů zdrojového kódu 11
1 Úvod 12
2 Popis a informace o společnosti AVE Soft s.r.o. 13
2.1 Představení firmy . . . 13
2.2 Mé působení ve firmě . . . 13
3 Hlavní firemní komerční produkty 14 3.1 Informační systém EVOLIO – EXEKUTORSKÝ ÚŘAD . . . 14
3.2 Informační systém EVOLIO – SPRÁVA POHLEDÁVEK . . . 14
4 Charakteristika mnou řešených úkolů 16 4.1 Převod exekučních spisů mezi konkurenčním systémem a E-EÚ . . . 16
4.2 Přenos exekučních spisů mezi exekutorskými úřady . . . 16
4.3 Optimalizace, ladění a monitorování SQL dotazů . . . 16
4.4 Tvorba nové funkcionality databáze a Diagnostika běhu jobů . . . 17
5 Postup řešení jednotlivých úkolů 18 5.1 Převod exekučních spisů mezi konkurenčním systémem a E-EÚ . . . 18
5.2 Přenos exekučních spisů mezi exekutorskými úřady . . . 19
5.3 Optimalizace, ladění a monitorování SQL dotazů . . . 22
5.4 Tvorba nové funkcionality databáze a Diagnostika běhu jobů . . . 24
6 Uplatněné a scházející znalosti, dovednosti získané v průběhu studia 26 6.1 Uplatněné znalosti a dovednosti získané v průběhu studia . . . 26
6.2 Scházející znalosti a dovednosti získané v průběhu studia . . . 26
7 Dosažené výsledky a celkové zhodnocení praxe 27
Literatura 28
Seznam použitých zkratek a symbolů
SQL – Structured Query Language
T-SQL – Transcript-Structured Query Language
SSMS – SQL Server Management Studio
SSMA – SQL Server Migration Assistant
XML – eXtensible Markup Language
ARES – Administrativní registr ekonomických subjektů RUIAN – Registr územní identifikace, adres a nemovitostí
CEE – Centrální evidence exekucí
8
Seznam obrázků
1 IS EVOLIO – EXEKUTORSKÝ ÚŘAD . . . 15
2 IS EVOLIO – SPRÁVA POHLEDÁVEK . . . 15
3 Operace spojené s použitím view . . . 23
4 Operace spojené s bázovými tabulkami a indexem . . . 23
5 Náročnost neoptimalizované procedury . . . 23
6 Náročnost optimalizované procedury . . . 24
7 Ukázka timeline v Diagnostice . . . 25
Seznam tabulek
1 Časové náročnosti jednotlivých úkolů . . . 17
10
Seznam výpisů zdrojového kódu
1 Query pro načtení dat z xml souboru . . . 20 2 Příklad vytváření číselníkových tabulek . . . 20 3 Konverze času . . . 21
1 Úvod
Pro absolvování odborné praxe, jako alternativu ke klasické bakalářské práci, jsem se rozhodl hned z několika důvodů. Jako hlavní důvod bych uvedl především získání praktických zkušeností a využití nabytých vědomostí ze studia při vývoji softwaru v profesionální firmě. Dále samozřejmě díky absolvované praxi, budoucí lepší pozici při výběru vhodné práce po ukončení studia. Jako poslední důvod bych uvedl také finanční stránku věci, jakožto zajímavý zdroj příjmů při studiu, zvláště když se jedná o praxi v mém oboru a je zde jako bonus možnost získat důležité zkušenosti.
Ve druhé kapitole této práce je popsána firma AVE Soft s.r.o., její úspěchy a také mé zařazení v této firmě. Ve třetí kapitole pak popisuji jednotlivé produkty firmy, na jejichž vývoji či údržbě jsem se sám podílel. Čtvrtá kapitola je věnovaná popisu jednotlivých úkolů, které mi byly v rámci odborné praxe přiděleny a také důvodům, kvůli kterým bylo tyto úkoly nutné řešit.
Se čtvrtou kapitolou je spojena kapitola pátá, která obsahuje popis postupu řešení jednotlivých úkolů a technologie použité při jejich řešení. Další kapitoly jsou pak věnovány zkušenostem, které jsem nabyl během studia na VŠB-TUO, a jejich přínosu pro moje působení ve firmě. Jsou zde také vypsané dovednosti, které mi během praxe scházely. Následuje zhodnocení dosažených výsledků z praxe a jejího přínosu pro mne jako studenta.
12
2 Popis a informace o společnosti AVE Soft s.r.o.
2.1 Představení firmy
Společnost AVE Soft s.r.o. je na trhu více než 15 let. Společnost sídlí ve Vědecko-technologickém parku Ostrava (VTPO), konkrétně v budově VIVA. Jejich produkty jsou určeny především pro advokátní kanceláře a také exekutorské úřady. Mezi klienty společnosti patří především české advokátní kanceláře a exekutorské úřady. Největším klientem AVE Softu pak je jistě skupina McGrath Arthur⃝R group (MCGA), která využívá produkty společnosti ve třech zemích, kon- krétně se jedná o Slovensko, Chorvatsko a Filipíny.
2.2 Mé působení ve firmě
Nástup na odbornou praxi začal tím, že mi byla přestavena celá společnost, její pravidla a také postupy. Prvních několik dní jsem se seznamoval s produkty společnosti, které popíšu v kapitole 3. Byly mi také představeny databáze používané těmito systémy, kdy jsem měl za úkol seznámit se s jejich strukturou, což bylo pro moje další působení ve firmě klíčové. Toto seznamování probíhalo mimo jiné například psaním různých uživatelských pohledů a filtrů podle zadání konzultantů, díky čemuž jsem byl schopný v poměrně krátké době zjistit, jak s danou databází pracovat, a kde hledat ta správná data.
Po seznámení se s firemními produkty a databázemi jsem se již začal podílet na samotném vývoji, což zahrnovalo např.: vytváření nových procedur, triggerů, tabulek atd... Mimo to jsem řešil požadavky konkrétních zákazníků ve spolupráci s konzultanty, kteří měli daného zákazníka na starosti. Tyto aktivity týkající se vývoje a také implementace pro konkrétní zákazníky budou později podrobněji rozepsány. Mimo to jsem dostal na zodpovědnost udržování verzí databází a jejich rozdílových scriptů. Tímto jsem se seznámil s verzovacím systémem zvaným Git.
3 Hlavní firemní komerční produkty
3.1 Informační systém EVOLIO – EXEKUTORSKÝ ÚŘAD
Informační systém EVOLIO – EXEKUTORSKÝ ÚŘAD (dále jen E-EÚ) je informační systém založený na síťové architektuře client-server, využívá MS SQL databázi a umožňuje přístup přes internet pro pracovníky úřadu. Díky tomu, že jde o modulární systém, si jednotliví zákazníci mohou vybrat, kterou všechnu nabízenou funkcionalitu budou využívat, a tím si systém upravit podle svých potřeb. E-EÚ patří v České republice mezi tři nejpoužívanější informační systémy pro exekutorské úřady. Celkově používá E-EÚ více než třicítka velkých, středních i menších úřadů, jejichž spisy tvoří významné procento exekutorských spisů v České republice[3]. Několik funkcionalit systému:
1. Insolvenční rejstřík – přímé propojení s insolvenčním rejstříkem umožňuje rychle odhalit osoby v insolvenci.
2. Import z obchodního rejstříku - podle IČO je možné snadno zadat podnikatelský subjekt včetně adres a kontaktních osob.
3. Import z ARESu (na základě názvu subjektu nebo IČ).
4. Vytváření vyúčtování – lze jej velmi snadno vytvořit, a to na spis nebo na klienta.
5. Tvorba splátkových kalendářů.
6. Modul pro výměnu dat s účetními programy.
7. Modul manažer-manažerské statistiky (úspěšnost vymáhání ve vztahu k době a oprávně- ným, Modul On line-přístup oprávněných k údajům).
3.2 Informační systém EVOLIO – SPRÁVA POHLEDÁVEK
Informační systém EVOLIO – SPRÁVA POHLEDÁVEK (dále jen E-SP) je jakýmsi modernějším nástupcem informačního systému E-EÚ. Tento systém je stále ve fázi vývoje. Přesto jej již někteří zákazníci využívají. Tento systém taktéž využívá MS SQL databázi. Vývoj a správa této databáze byly mou nejčastější pracovní činností. E-SP přebírá z E-EÚ osvědčené funkcionality, ale zároveň již implementuje některé nové. Struktura databáze tohoto systému je taktéž z velké části podobná jako u E-EÚ, což mi značně usnadnilo se v krátké době zorientovat v obou databázích. Mezi hlavní přednosti E-SP patří zejména rozšířená možnost tvorby splátkových kalendářů, jež jsou hlavní součástí vymáhacích procesů. Díky správnému nastavení splátkových kalendářů je možné nastavit upomínkový systém, který dokáže dlužníka ve správném okamžiku upozornit[2].
14
Obrázek 1: IS EVOLIO – EXEKUTORSKÝ ÚŘAD
4 Charakteristika mnou řešených úkolů
4.1 Převod exekučních spisů mezi konkurenčním systémem a E-EÚ
4.1.1 Popis problému
Stává se, že klient používá konkurenční informační systém a přechází na nový. Klient si žádá přenést data z onoho konkurenčního systému na nově používaný, náš. Konkrétně E-SP. V tomto případě je tedy nutné naimportovat stará data do nové databáze tak, aby strukturou odpovídala nové databázi. Dbá se na to, aby se důležitá data naimportovala správně, jelikož slouží také k vykazování u případného soudu a obsahují citlivé informace o zařazených subjektech.
4.2 Přenos exekučních spisů mezi exekutorskými úřady
4.2.1 Popis problému
Exekutorské úřady si mohou mezi sebou své spisy přeposílat, pokud jeden z těchto úřadů používá náš software, řeší tyto převody naše firma. Dostal jsem tedy za úkol přenést spisy od jednoho úřadu na úřad jiný. Předání dat o spisech mezi úřady proběhlo formou zaslání xml souborů, kdy každý jeden xml soubor obsahoval data pro jeden konkrétní spis.
S touto formou předání nastalo hned několik problémů, které se musely pro úspěšný převod do systému E-EÚ vyřešit. Prvním problémem byla velikost xml souborů, kdy některé soubory dosahovaly velikosti stovek MB. Další problém byl ten, že k zaslaným xml souborům nebylo žádné xsd schéma, které by se hodilo obzvláště v případě, že tyto soubory byly vyexportovány z konkurenčního softwaru. Také struktura xml souboru nebyla všeříkající. Jednotlivá jména elementů nabývala pouze hodnot: container, id, key, keykod, value, name, object a objects. Což při zanoření, které bylo někdy i třiceti úrovňové, znesnadňovalo orientaci v souboru. Úřad, od kterého soubory pocházely, nebyl příliš nakloněn další spolupráci při výskytu těchto problémů.
Celý úkol se dá tedy shrnout takto. Dostali jsme několik GB dat v xml souborech, u kterých bylo nutno prostudovat strukturu, případně tyto soubory převést do člověku pochopitelnější formy, a poté data převést do naší databáze u zákazníka.
4.3 Optimalizace, ladění a monitorování SQL dotazů
4.3.1 Popis problému
Optimalizace dotazů, které jdou na databázi, je časově náročná, avšak důležitá a zajímavá práce.
Je nutné zjistit, které dotazy nepřiměřeně zatěžují výkon MS-SQL databáze, a tyto pak zrevido- vat. Optimalizací je myšlena snaha zredukovat zápis a čtení do datových stránek při vykonávání dotazu a pokud možno také snížit dobu vykonávání dotazu. Do optimalizace mě zasvětil kolega, který disponuje mnohaletými zkušenostmi v této oblasti. S optimalizací samozřejmě souvisí již
16
samotná tvorba SQL dotazů, kdy je potřeba přemýšlet nad tím, jak bude dotaz výkonově nejú- spornější. Jedním z nejzákladnějších doporučení, které má velký vliv na optimalizaci dotazu při vytváření, je vyhnutí se kurzorům a cyklům, jelikož SQL server je navržen pro relační operace, a takovéto sekvenční procházení je pro výkon velký problém. Dalším takovýmto příkladem, na který jsem při optimalizaci narazil, může být nesmyslné používání view. View totiž mnohokrát spojuje více tabulek, které nejsou vždy potřeba či vypočítávané sloupce závislé například na security contextu. U view, pokud nemá vázané schéma, je také problém v nemožnosti použití indexů.
4.4 Tvorba nové funkcionality databáze a Diagnostika běhu jobů
4.4.1 Popis problému
Spolu s tím, jak se náš software postupně vyvíjí, přibývá nová funkcionalita, přidávají se nové moduly do systémů, je potřeba, aby tyto změny měly pevný základ v podobě databáze. Vyví- jel jsem tedy nové databázové objekty pro nově vyvíjené části systému. Tyto nové části jsem konzultoval s programátory, kteří se u nás starají o aplikační a prezenční vrstvu našich systémů.
Diagnostika běhu jobů je utilita k E-EÚ pro firemní konzultanty, která na timeline zobrazuje čas běhu jobů E-EÚ, díky čemuž je možné nastavit spouštění jobů tak, aby joby, které by neměly běžet současně, běžely v rozdílnou dobu. Joby jsou vnitrofiremní označení pro add-iny systému, které se starají například o získávání nových informací z pojišťoven, bank, ARES, RUIAN či CEE. Vytvoření této utility byl jen jednorázový úkol hned na začátku mého působení ve firmě, proto mu není věnovaná samostatná kapitola.
Tabulka 1: Časové náročnosti jednotlivých úkolů
úkol časová náročnost
Převod exekučních spisů mezi konkurenčním systémem a E-EÚ 15 dní
Přenos exekučních spisů mezi exekutorskými úřady 18 dní
Optimalizace, ladění a monitorování SQL dotazů průběžně po dobu praxe
Tvorba nové funkcionality databáze průběžně po dobu praxe
Diagnostika běhu jobů 4 dny
5 Postup řešení jednotlivých úkolů
5.1 Převod exekučních spisů mezi konkurenčním systémem a E-EÚ
5.1.1 Použitý software
Seznam softwaru nutného pro splnění zadaného úkolu:
1. SSMS – je softwarová aplikace, která slouží ke konfiguraci, správě a administraci všech komponent v rámci MS SQL Serveru. Nástroj obsahuje jak editor skriptů, tak grafické nástroje, které pracují s objekty a funkcemi serveru.
2. MySQL Workbench – je nástroj, který integruje konfiguraci, správu a administraci do jediného integrovaného vývojového prostředí pro databázový systém MySQL[1].
3. SSMA – je freeware nástroj společnosti Microsoft, který zjednodušuje proces migrace da- tabáze z MySQL na MS SQL Server.
5.1.2 Konkrétní postup řešení
Tento úkol se týkal více klientských portfolií, takže některé kroky popsané v tomto postupu bylo nutno zopakovat pro každé portfolio zvlášť. Konkrétně se jednalo o jedenáct portfolií.
Od zákazníka mi byly poskytnut MySQL script pro vytvoření MySQL databáze. K tomuto byl zapotřebí program MySQL Workbench, díky kterému jsem byl schopný vytvořit databázi ze zaslaného scriptu. Po vytvoření databáze bylo nutné se zorientovat v tabulkách, vazbách a celkové struktuře takto vytvořené databáze.
Po dostatečném seznámení se s databází přišla na řadu komunikace se zákazníkem, kdy bylo třeba dohodnout, jaká data se budou přenášet a jak mají být v novém systému prezentována.
Toto bylo potřeba domluvit především pro sjednocení číselníkových tabulí, kdy například starý systém obsahoval více stavů, kterých může nabývat konkrétní spis. V těchto případech byly většinou dvě možnosti. První možností bylo přidání těchto stavů do E-SP, druhou pak to, že si zákazník pro stavy, které nebyly v E-SP, vybral takový stav v E-SP, který mu vyhovoval.
Dalším příkladem toho, co bylo potřeba dohodnout, bylo nastavení typů plateb. V E-SP existuje několik typů plateb. Jelikož při tomto úkolu byly použity jen dva typy, tak zde popíšu jen tyto dva. Jedná se o typ OPL a PSU. Typ OPL se používá, pokud si zákazník nepřeje aktualizovat dlužnou částku u spisu při zadání příchozí platby do systému. O aktualizaci dlužné částky se pak stará importér, který aktualizuje jak dlužnou částku, tak i další finanční položky.
Toto se děje s nastavenou iterací při příchodu dat od konkrétního klienta, kterému náš zákazník vymáhá pohledávky. Při typu plateb PSU je při zadání příchozí platby do systému tato platba automaticky rozúčtována do jednotlivých finančních položek.
18
Po získání všech důležitých informací od zákazníka jsem převedl MySQL databázi pomocí programu SSMA na MSSQL server, kde byla následně použita jako zdrojová databáze pro import dat do cílové databáze systému E-SP.
Pro převod dat mezi zdrojovou a cílovou databází jsem vytvořil nové schéma v cílové da- tabázi, které obsahovalo synonyma na tabulky z databáze zdrojové, kvůli zjednodušení psaní procedur, dále vazební tabulky, které obsahovaly vazby mezi oběma databázemi a také pro- cedury, ve kterých byla logika celého importu.
Import byl vyzkoušen na cvičné databázi, kde si po takto provedeném importu zákazník zkontroloval naimportovaná data a dal mi povolení k importu ostrému.
Samotné procedury používané pro import se musely pro každé další porfolio upravovat kvůli faktu, že každá mně dodaná databáze měla nějaké rozdíly oproti ostatním. Tyto rozdíly však nebyly velké a od druhého importu již bylo potřeba vždy jen pár úprav.
Na tomto úkolu bylo časově nejnáročnější se zorientovat v dodané databázi, jelikož k ní nebyla žádná dokumentace.
5.2 Přenos exekučních spisů mezi exekutorskými úřady
5.2.1 Použitý software
Seznam softwaru nutného pro splnění zadaného úkolu:
1. SSMS
2. Altova XMLSpy – je nástroj pro práci s xml soubory. Tento software umožňuje kromě klasického textového pohledu na xml také pohled tabulkový, což mi značně usnadnilo pochopit strukturu xml. Tento program jsem používal ve 30denní zkušební verzi.
3. Microsoft Visual Studio Enterprise 2017 – jedno z nejpoužívanějších vývojových prostředí s podporou hned několika jazyků.
5.2.2 Konkrétní postup řešení
Po zadání úkolu jsem řešil problém s tím, jak nahlédnout do zaslaných xml souborů. Jako nejvhodnější řešení jsem vyhodnotil použít program Altova XMLSpy, díky kterému bylo alespoň minimálně možné získat přestavu, jak a na jakých místech jsou data v souborech uložena. Dále bylo nutno zvolit další postup. Po konzultaci s kolegy mi bylo doporučeno tyto soubory převést do nějaké srozumitelnější podoby. Tuto část úkolu jsem vyřešil napsáním procedury, která brala jednotlivé soubory po sobě a z těchto pak vytvořila jakousi generovanou databázi, která poté byla použita jako zdrojová databáze pro import dat. V této databázi byl pro datové typy sloupců
set @sqlXml =
’declare @xmlData xml set @xmlData = (
select * from openrowset (
bulk ’’’+ @Path + ’’’, single_clob ) as xmlData
)
insert into #tmpTable select
Tbl.Col.value(’’../../container[1]’’, ’’nvarchar(290)’’) as container , Tbl.Col.value(’’../../id[1]’’, ’’nvarchar(290)’’) as id
, Tbl.Col.value(’’key[1]’’, ’’nvarchar(290)’’) as [key]
, Tbl.Col.value(’’../../../../../../id[1]’’, ’’nvarchar(290)’’) as idParent , Tbl.Col.value(’’../../../../../../container[1]’’, ’’nvarchar(290)’’) as
parentContainer
, Tbl.Col.value(’’name[1]’’, ’’nvarchar(290)’’) as name , Tbl.Col.value(’’value[1]’’, ’’varchar(max)’’) as value , Tbl.Col.value(’’keykod[1]’’, ’’nvarchar(max)’’) as keykod from
@xmlData.nodes(’’//item’’) Tbl(Col)’
exec(@sqlXml)
Výpis 1: Query pro načtení dat z xml souboru
Vygenerovat takovouto databázi zabralo spoustu času. Celý proces trval necelé čtyři dny.
Databázi jsem proto generoval na záložním vývojovém serveru, kde tento proces mohl běžet ne- přetržitě. Generování takovéto databáze bylo z velké části nutno realizovat pomocí dynamického SQL. Podle xml souborů bylo zapotřebí také vyhodnotit, pro které tabulky ve výsledné databázi se budou generovat vazební tabulky a také číselníky.
-- cislenikove tabulky
if @keykod is not null and @key != @keykod begin
if object_id(@name) is null begin
set @sql = ’create table ’ + @name + ’ (klic nvarchar(max) , hodnota nvarchar(max))’
execute(@sql) set @sql = null end
20
set @sql = ’if(not exists (select 1 from ’+@name+’ where klic = ’’’+@key+
’’’)) begin
insert into ’ + @name + ’ values( ’’’ + isnull(@key,’NULL’) +’’’, ’’’+
isnull(@keykod,’NULL’) + ’’’ ) end’
execute(@sql) set @sql = null end
Výpis 2: Příklad vytváření číselníkových tabulek
Zároveň jsem si vygeneroval databázi jen ze sta souborů na lokálním serveru, abych v prů- běhu toho mohl vytvářet procedury pro samotný import dat z vygenerované zdrojové databáze do databáze systému E-EÚ. Tyto procedury se staraly vždy o import dílčích částí dat. Takže například jedna procedura importovala informace o spisech. Další k těmto spisům přidávala jednotlivé subjekty. Bylo samozřejmě nutné zajistit, aby se data v databázi neduplikovala. Na- příklad pokud v databázi již existuje subjekt, který figuruje ať již jako oprávněný nebo povinný v jiném spise, tak se tento subjekt naimportovat nesmí, a místo toho se použije jeho stávající záznam. V případě subjektů se duplicity vylučovaly pomocí rodného čísla, IČa či jména, příjmení a trvalé adresy, pokud IČO nebo rodné číslo chybělo.
Rozdílný postup byl ovšem u importu dokumentů případu. Ve vygenerované databázi byly dokumenty reprezentovány jako nvarchar v kódování base64 a zazipovány, ale v databázi, se kterou pracuje E-EÚ, jsou dokumenty uloženy do sloupce typu varbinary. Tyto dokumenty bylo tedy nutno převést. Tento krok byl vyřešen tím, že do každé tabulky, která obsahovala data souborů, byl přidán sloupec typu varbinary, do kterého se pomocí jednoduché konzolové aplikace tato data zkonvertovala. Při importu pak byl použit tento nový sloupec.
Také všechny sloupce, které ve zdrojové databázi obsahovaly záznam o datu, byly uložené ve formátu Unix Timestamp. Tato data bylo nutné převést na typ datetype používaný v cílové databázi. Unix Timestamp ze zdrojové databáze se převedl na odpovídající datum, ovšem bylo také nutné brát v potaz časový posun vůči UTC.
DateAdd( hour, 2, DateAdd(s, convert(bigint,k.DatumNarozeni),’1970-01-01 00:00:00’)) as Narozen
Výpis 3: Konverze času
5.3 Optimalizace, ladění a monitorování SQL dotazů
5.3.1 Použitý software
Seznam softwaru nutného pro splnění zadaného úkolu:
1. SSMS
2. SQL Server Profiler – je grafické uživatelské rozhraní pro sledování dění nad instancí da- tabázového enginu.
5.3.2 Konkrétní postup řešení
Prvním krokem při optimalizaci bylo dohledání těch dotazů, které nejvíce zatěžují celkovou výkonnost databáze. Důležitým faktorem pro takovéto vyhledávání byl poměr mezi četností spuštění a náročností konkrétního dotazu, dále také zda tento dotaz je spouštěn nad databází v čase, kdy je potřeba s databází pracovat, či běží například v noci, kdy není databáze vyu- žívána. Pro takovéto dohledávání jsem používal SQL Server Profiler, který může být spuštěn permanentně, a při spuštění trace je možné si navolit, jaké události se budou odchytávat. Při optimalizaci mě pak zajímaly hlavně hodnoty:
1. CPU – čas procesoru nutný pro vykonání dotazu. (v milisekundách) 2. Reads – počet nutných čtení. (1 Read = 1 datová stránka = 8kb) 3. Writes – počet nutných zápisů. (1 Write = 1 datová stránka = 8kb) 4. Duration – celkový čas vykonání dotazu. (v milisekundách)
Na serveru zákazníka byl tedy spuštěn SQL Server Profiler, ve kterém jsem následně, podle výše popsaných faktorů, dohledal dotaz či proceduru, která zatěžovala databázi. Tato procedura (dotaz) byla pak dohledána ve vývojové databázi, kde byla následně optimalizována, pokud nebylo dostatek dat pro optimalizaci na vývojové databázi, musela optimalizace probíhat přímo u zákazníka.
Jako příklad zde uvedu postup optimalizace procedury PostaPrichoziPoctySp, která má za úkol dohledat počty pošt všech druhů (emaily, listinné pošty, SMS/IVRS...), pro konkrétního zaměstnance. Tato procedura je volána při každém přístupu zaměstnance do podatelny a při velkém množství pošt brzdila systém.
První změnou v proceduře bylo odstranění joinů na view, ze kterého se braly jen sloupce, které byly přímo obsaženy v bázové tabulce. Toto view joinovalo několik dalších tabulek a také obsahovalo sloupec závislý na security contextu, takže použití indexu pro toto view bylo vyloučené. Jelikož byly potřeba jen sloupce z bázové tabulky tohoto view, pak napojení takového view v dotazu procedury bylo příčinou mnoha zbytečných operací, které musel server vykonat, toto je vidět na exekučním plánu:
22
Obrázek 3: Operace spojené s použitím view
Místo view se použily přímo bázové tabulky, díky čemuž se také použil již existující index tabulky PostaPrichozi. A z operací, které je potřeba vykonat, zbyl jen Clustered Index Scan nad indexem PK_PostaPrichozi, což je primární klíč tabulky. Jelikož join na view byl v proceduře celkem třináctkrát, tato úprava znamenala velkou úsporu I/O operací a času pro vykonávání do- tazu. Dále byl vytvořen index IX_PostaPrichozi_IdPostaUcet_DruhDoruceni_JeOdstranena, díky čemuž byl při vykonávání dotazu použit Index Seek nad tímto indexem. Po změně pak část exekučního plánu vypadala následovně:
Obrázek 4: Operace spojené s bázovými tabulkami a indexem
Přidání indexu IX_PostaOdchozi_DruhDoruceni_Stav_DatumOdeslani_IdObalka byla pak další úprava, která eliminovala Clustered Index Scany nad tabulkou PostaOdchozi. Což přineslo další úsporu v čase vykonávání dotazu. Celkově se podařilo tuto proceduru několikanásobně zrychlit a také zredukovat počet čtení. Pro porovnání:
Obrázek 6: Náročnost optimalizované procedury
Optimalizace dotazů byl úkol, který jsem plnil průběžně po celou dobu mého působení ve firmě. Časová náročnost se pohybovala průměrně kolem tří hodin na jeden dotaz či proceduru, jelikož bylo potřeba zjistit, co konkrétně daný dotaz provádí, a také průběžně kontrolovat vý- sledky, zda odpovídají původnímu dotazu.
5.4 Tvorba nové funkcionality databáze a Diagnostika běhu jobů
5.4.1 Použitý software
Seznam softwaru nutného pro splnění zadaného úkolu:
1. SSMS
2. SQL Server Profiler
3. Microsoft Visual Studio Enterprise 2017 5.4.2 Konkrétní postup řešení
Samotnému vytváření nových databázových objektů vždy předcházela konzultace o tom, jaké sloupce bude tabulka obsahovat, zda budou v tabulce cizí klíče do ostatních tabulek (v případě tvorby tabulek), či jaké vstupní parametry bude mít procedura/funkce/ a jaké výsledky bu- dou vráceny (při tvorbě procedur a funkcí). Dále pak následoval samotný vývoj nad vývojovu databází. Vytvářené procedury musely být řádně otestovány a zoptimalizovány. Konečnou fází pak bylo vytvoření skriptu, který tabulku, proceduru atd... přidal do databáze. Tyto změnové skripty musely být znovuaplikovatelné, to znamená, že v případě opakovaného spuštění nesmí nastat chyba. Takovýto skript byl pak pomocí verzovacího nástroje GITu zařazen k ostatním změnovým skriptům pro právě vyvíjenou verzi databáze. Takovéto úpravy a rozšiřování databáze jsem řešil po celou dobu praxe.
Z této oblasti bych zmínil proceduru pro slučování pošty v systému E-EÚ, která dohledává listinné pošty, které mají být poslány na stejnou adresu a sloučí tyto pošty do jedné tak, že fyzicky je odeslaná jen jedna obálka se všemi dokumenty, které obsahovaly ostatní pošty.
S touto procedurou pracuje add-in slučování hybridní pošty, který úřadům i díky této proceduře 24
značně snížil náklady na poštovné a také spoustu času, kdy se o slučování pošty starali samotní zaměstnanci úřadu a jeden den v týdnu tuto poštu slučovali ručně.
Jelikož jsem tuto proceduru vytvářel již s lepším know-how, jak efektivně psát dotazy nad databází, je add-in pracující s touto procedurou jedním z nejrychlejších add-inů v celém systému.
Z celého dne, kdy zaměstnanci úřadu slučovali poštu ručně, se tedy díky tomuto novému add-inu čas pro sloučení pošty snížil na necelou vteřinu (pro průměrně šest set sloučených pošt). Dia- gnostika běhu jobů je utilita pro systém E-EÚ, díky které mohou konzultanti nastavit spouštění jobů tak, aby nedocházelo ke kolizím. Jedná se o jednoduchou aplikaci vyvíjenou ve frameworku WPF, kdy si aplikace bere data z logu běhu jobů za určité období a následně je vizualizuje do timeline, kde jde pouhým okem vidět, zda joby běží současně, zda proběhly v pořádku, či stále běží. Za zmínku stojí použití NuGet balíčků pro tento projekt. Konkrétně Dapper pro jednoduché ORM a OxyPlot, který je použit pro vykreslování timeline.
Obrázek 7: Ukázka timeline v Diagnostice
6 Uplatněné a scházející znalosti, dovednosti získané v průběhu studia
6.1 Uplatněné znalosti a dovednosti získané v průběhu studia
Při vykonávání praxe jsem použil znalosti hned z několika předmětů. Vzhledem k tomu, že převážná část mé praxe se týkala vývoje a údržby databází, uvedl bych zejména předměty spojené s tímto tématem, jako je Úvod do databázových systémů, Databázové a informační systémy, ale také Technologie databázových systémů I, díky kterému jsem získal certifikát od ORACLE. Ten mi pomohl získat odbornou praxi, i přes to že se zaměřoval na jiný systém než ten, který jsem na praxi využíval. Dalším užitečným předmětem byl Vývoj informačních systémů, ve kterém jsem získal představu o jednotlivých fázích vývoje informačních systémů, což jsem si mohl následně na praxi vyzkoušet.
6.2 Scházející znalosti a dovednosti získané v průběhu studia
Ze znalostí a dovedností, které mi scházely, uvedu především metody optimalizace dotazů, na- příklad čtení exekučního plánu. Při studiu bych uvítal větší zaměření na toto téma, které je pro vývoj databází důležité. Také projekty vypracovávané v rámci výuky by mohly z větší části být skupinové, což by vedlo k vyzkoušení si práce v týmu.
26
7 Dosažené výsledky a celkové zhodnocení praxe
Absolvování praxe hodnotím jako velice přínosné. Měl jsem možnost vyzkoušet si práci v týmu na komerčních produktech, což se jistě bude hodit při hledání zaměstnání po studiu. Díky praxi jsem se naučil také mnoho nových věcí, převážně takové, které se týkají návrhu, vývoje a údržby databází. Mohl jsem také v praxi uplatnit teoretické znalosti získané při studiu a podílet se na vývoji rozsáhlejších projektů než těch, které jsem plnil při studiu, jako byly projekty do různých předmětů. Zkušenosti, které jsem získal praxí, mi určitě z velké části pomohou v dalších letech studia.
Literatura
[1] MySQL AB:MySQL Workbench [online]. 2018 [cit. 2018-04-20].
Dostupné z: https://www.mysql.com/products/workbench
[2] AVE Soft s.r.o.:EVOLIO – SPRÁVA POHLEDÁVEK [online]. 2018 [cit. 2018-04-20].
Dostupné z: http://avesoft.cz/evolio/evolio-sprava-pohledavek
[3] AVE Soft s.r.o.:EVOLIO – EXEKUTORSKÝ ÚŘAD [online]. 2018 [cit. 2018-04-20].
Dostupné z: http://avesoft.cz/evolio/evolio-exekutorsky-urad
28
