Course intended for:

The training is intended for programmers and database administrators wishing to develop their knowledge on enhancement of performance and scalability of database applications.

Course objective:

The participants will find out how to write queries and organize data to make the best use of the database system owned. The participants will also get familiar with the internal structure of the modern database systems, which will allow them to set the configuration parameters and foresee the impact of the design decisions made on performance. Weaknesses of selected database systems and remedies to these will also be discussed.

Requirements:

The participants are expected to have the basic knowledge of issues associated with relational databases, knowledge of Java language and basic knowledge of object-relational mapping (JPA or Hibernate).

Course parameters:

3*8 hours (3*7 net hours) of lectures and workshops (with a visible emphasis on workshops). During the workshops, the participants will apply the learned techniques, developing an exemplary application operating in the selected database system.

Group size: no more than 8-10 participants.

Course curriculum

  1. The database system architecture

    1. Data access layer (DAO)

    2. Cache of the application

    3. JDBC controller

    4. SQL query compiler/parser

    5. Query copying module

    6. Query optimizer

    7. Execution layer

  2. The physical data layer

    1. Data organization in tables

    2. Table ‚Äěhealth" diagnostics

    3. Indexes: B+ trees, mixing, span, spatial

    4. Grouping indexing

    5. Full text indexing

    6. Index repair

  3. Query performance algorithms and their characteristics

    1. Information browsing algorithms

    2. Join algorithms: hash join, merge join, nested loops, index

  4. nested loops

    1. grouping algorithms

    2. query plan clarification (EXPLAIN, AUTOTRACE)

  5. writing of effective queries and designing of physical data structure

    1. query optimizer limitations

    2. use of advanced optimizer capabilities in Oracle / MSSQL / DB/2

    3. maintenance of optimizer statistics

    4. selection of indexes

    5. selection of materialized perspectives

    6. multi-dimensional table clustering

    7. horizontal and vertical table partitioning, division into many carriers

    8. use of tools that support performance tuning

    9. structures to be avoided

  6. Buffering

    1. Buffering of blocks in the files system

    2. Buffering of data blocks in shared memory

    3. Buffering at the record level

    4. Buffering of query results

    5. Buffering of query performance plans (prepared statements)

  7. Transactions

    1. Concurrence management strategies - 2PL,MVCC

    2. Table, record level locks

    3. Writing a transaction safe code

    4. Reduction of deadlocks/ number of cancelled transactions

    5. Ensuring data durability, transaction withdrawal and renewal (transaction log, withdrawal segment)

    6. Problems associated with frequent data modification and long transactions

    7. Distributed transactions

  8. Replication and HA systems

    1. Synchronous, asynchronous replication strategies

    2. masterslave, mastermaster

    3. replication in MySQL, PostgreSQL, Oracle, DB/2...

    4. load balancing

    5. Hibernate shards

  9. Development of an effective code in Java

    1. lazy/eager fetching

    2. bulk update/delete

    3. buffering in JPA/Hibernate

    4. distributed buffering

Any questions?

* Required.


Contact
Phone +48 22 2035600
Fax +48 22 2035601
Email