Catherine Ronsal

The abstract

Of master's work:

"Distributed databases organization methods research"

By Catherine Ronsal

Modern capacities of stored data, mandatory requirements to its accessibility and processing speed, the dynamics of system’s development cause the importance of investigation of factors, which affect on databases quality, which underline up-to date informational systems.

The output is considered to be the main question at the point of maintenance, as long as system doesn’t perform the requested functions if the required response time does not provided.

From the point of view of system’s functionality the main criterion is the response time when accessing the database. The response time is formed from the duration of the following processes: delivery of the database query; transaction and searching of the requested information; delivery of the output result to the client. For the purpose of minimizing this value it’s necessary to study all the sub-systems, realizing this function. So, in such a way, the purpose of master's thesis is the investigation of characteristics of distributed system, particularly InterBase server, which will enable to recognize possible ways of optimisation for databases.

Here is some conclusions that were made during the review the works of different specialists in the sphere of database according to the creation of effective applications.

Before optimizing the database application it is essential to find the system’s bottle-necks and to make its optimization first of all. If application hasn’t created yet, then you have to think about its optimization on design stage. Because later on it’s much more difficult to fix the bugs. Here is the list of the aspects affecting the application’s output:

1. Selection of data access strategy. [1]
2. Normalization of DB datasheets. [2]
3. Selection of components for data access.
4. Making effective database queries. [3]
5. Correct use of the transactions tool.
6. Changing the values of parameters of server’s configuration file [4]. It is ibconfig file in InterBase.
7. Setting the volume of database cache.
8. Back up and recovery.
9. Using the user defined functions (UDF), triggers, stored procedures.

This leads to increasing of computation speed and lowering the network load.

Experiments were carried out (insert test and test of deleting) purposely to determine how indices can have effect on speed executing of insert and delete queries for records (INSERT, DELETE) in InterBase DB.

Here is only the general concept of algorithm, without every detail of realization.

The main idea of insert test is: to insert records in table, to note the execution query time and save it. In addition, you should insert records in table with active indices and also inactive. So it is possible to define, how indices is affects on the execution time of INSERT statement.

Test of deleting are executed in the same manner, with taking into consideration InterBase's multi generational Architecture, however.

Insert test

Tests has shown that if table hasn’t any active indices, then execution time is short (30 s – for 50000 insert records, 55 s – for 100000 insert records, 2 min – for 200000 insert records) and this time depends on table population.

If table has indexed fields, then execution times increase fast.

So, active indices slow down records inserting. Of course if it is a big amount of records. In this case you should DROP indices and than CREATE indices.

Test of deleting

Tests has shown that records deleting in table with active indices takes much time in comparison with deleting in table without active indices. So you should temporarily make indices inactive. And after deleting you should execute SELECT query or backup/restore to collect the garbage.

As a result of studying present researches by master’s work theme was made the summary about developing effective database applications.

During the own research execution was determine that indexation in InterBase databases affects on the system performance. Both tests show that indexed fields slow down the execution time of queries. So it is recommended to temporarily make indices inactive. It means that during the developing and maintenance the database you should take into account particular qualities of database server.

1. Optimization I: Optimizing InterBase Applications, by Craig Stuntz, 2003
   (http://bdn.borland.com/borcon2004/article/paper/0,1963,32255,00.html).
   

2. Tips for improving InterBase server performance
   (http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1133335039:381637&page=ibp_tip_perf).
   

3. Optimization II: Optimizing InterBase SQL and Metadata, Craig Stuntz, Borland Convention 2003.
   (http://blogs.teamb.com/craigstuntz/articles/IBOptimization2.aspx)

4. InterBase configuration parameters, by Ann Harrison
   (http://ibphoenix.com/main.nfs?a=ibphoenix&s=1132891400:150223&page=ibp_config).
   

The master's work isn't accomplished yet. It will be ready in January 2007. The full text of work and all the materials could be given by author or his leader of work after this date.