Database Application Development.

Assignment: Database Performance Tuning

Student’s name

Prof’s name

Course title

Date

Introduction

Performance tuning refers to a group of activities that help to optimize performance of a database. Performance tuning helps to minimize response time for queries and to maximize throughput of the database through utilization of resources like network traffic, CPU time or access to disk. A fully optimized database depends on the type of database management system used as well as the database environment configuration. From the situation given, the database System at the County of Everstone is characterized by high amounts of latency, high downtimes, queries taking long to run leading to locking up of the server, data duplication among others. This database therefore needs to be optimized using a variety of ways discussed below.

Performance tuning

It can be achieved through Performance test, Load test, stress test, and Capacity test. Each of the aforementioned have been discussed below.

Performance testing is a technique applied when you want to investigate the responsiveness, speed or scalability of the database. Responsiveness here refers to the time it takes to receive feedback from the server after sending a request. Scalability is the ability to meet a couple number of needs. We need to understand the problem we are facing before we start the process of tuning the database. It is reported that the system in place has experienced tremendous increase in traffic as the county takes most of its services online. As a result we have seen that there has been high amounts of latency experienced in the web application as well as locking u the database access or even being inaccessible. Need for testing the database is therefore very crucial in order to know how to optimize it

Load test helps to verify database behavior during normal and peak conditions. Test can determine the breaking point of the database which can then be re-configured so as to meet the objective thus ensuring the system is up all the time for the people within the county to access it all the time. Load test also helps to determine throughput rates and resource utilization levels. Endurance testing can be used to find the Mean Time Between Failure (MTBF) and Mean Time To Failure (MTTF) among other essential metrics.

Stress testing. This test will help to validate the application or database’s behavior when subjected beyond the normal conditions. Stress testing will ensure that the system will not fail when under high load. The database administrator at the county has noted that the system currently fails when a high number of users access it and therefore it is good if this stress testing is done with extreme data values so that it will ensure the system does not fail even during those peak hours.

Capacity testing – it helps determine the number of users that the system can support at about given time without failing. This testing helps in planning for the future growth and demands as well as to identify the scaling strategy. In the scenario given the demand of the online services has been on the rise and there is need for capacity testing to ensure the database is able to support current number of people at the moment and also in the future due to the nature of the increase In demand.

Query Design

Queries are used to retrieve data from a database. When performance of a database is of great concern then best practices in query design have to be considered. The database of the county of Cornerstone seem to have a problem with the design of the queries considering the analysis from the senior system administrator who reported that the queries takes long to be executed and finally leading to locking of the web server and thus frustrating the users. These queries must optimized so as to improve performance of the database. There are a couple of ways to achieve this. One is to create indexes properly. Indexing allows queries to access database very fast thus improving on speed which has been a problem in the current scenario. Indexing makes it faster for insert, update and select statements thus improving database efficiency. Another way to achieve is to retrieve only needed data. In small database * is normally used to retrieve data. In large database like County database, data retrieval must involve specifying columns. This makes retrieval fast and therefore improving the efficiency. Another technique is avoiding functions on the left hand side of the operator. Functions can be used in select and where clause but when used in in where clause can lower performance hence the administrator should ensure there are no functions in the queries and if they exist then there is need to reduce them. Correlated queries can also be the source of slow queries. A correlated query is a query that operates on the data obtained in outer query from its WHERE clause. Wildcard characters at the start of the Like pattern should be eliminated. For instance the use of the % wildcard prevents use of index on a column and this will compromise the speed.

Data duplication and redundancy

Data duplication refers to existence of same data in more than one table. This normally occurs because of poor design of the database that is characterized by poor normalization. A fully normalized database up to the 3NF eliminates data redundancy. Redundant data leads to wastage in storage capacity as well as reducing efficiency in searching or look up that the county database mostly use. Normalization is achieved with logical design of the database while keeping the end user at the back of the mind. This is about understanding the needs of the users and what they will be doing in the system, in this case, the people making payments for the tax. The design will consider things like the kind of data to be stored in a database, how the user will access, privileges granted to user data commonly accessed among others. This logical design will finally reduce the data repetition.

Concurrency

Concurrency in database can be thought to be the ability of more than one process to gain access and modify the shared data at ago. Concurrency issues in a database can easily compromise the integrity of data hence need to control it. Two methods of concurrency control are in use. One is pessimistic control while the other is optimistic. In pessimistic control the DBMS system locks the data being accessed by one user after which it releases so that other users can access it. This is more applicable in places where data contention is high. This method is very easy to implement and ensures that the changes made to the database are securely and consistent. Since this method is mostly not scalable it may not be applicable to the problem at hand and we therefore shift our focus to the next one, optimistic concurrency control. With this method, data being accessed is not locked. When data is updated, system makes a check to determine if another user changed data after the last read operation. If the case is true then an error is raised and the user is forced roll back the transaction and retry again. Considering the nature of the tasks performed in this scenario involving multi-user access, undoubtedly this method is most appropriate.

It is reported that the web application constantly locks up as the number of users increase. This is an issue of concurrency access where many people try to access many records and because the queries are poorly designed, execution time exceeds the set application access time thus causing locking. Optimistic approach will help solve this problem when the queries will have been modified as have been suggested above. Another way we can handle concurrency is through serialization. This refers to isolating actions of different database users that are accessing the same data at once. A database user is made to feel like he/she is the only one accessing it. When one transaction is started, isolation from any changes occurring to its data coming from subsequent transactions begins.

Basic time stamping is another mechanism which eliminates deadlock from occurring as it makes no use of locks in order to control concurrency. It works by creating a unique timestamp assigned for each transaction, and bearing time it was started. This essentially allows age to be assigned to transactions and an order to be assigned. Data items are assigned read-timestamp and a write-timestamp. These timestamps are updated regularly when data item is read or updated. This technique can surely resolve the locking problem that is experienced in the current database.

Conclusion.

Various concepts have been discussed concerning the scenario given. If all these are taken into consideration, it is no doubt the optimization of the database will be achieved and the problems currently experienced will come to an end.

Reference

msdn.microsoft.com (2016). Types of Performance Testing. Retrieved from: