Database Application Development.
nikkum
database_performance_tuning.pptxDatabase Performance Tuning
Performance tuning
Refers to a group of activities that help to optimize performance of a database and 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.
It can be achieved through the following ways
Performance testing - Applied when you want to investigate the responsiveness, speed or scalability of the database.
Stress testing - Helps to validate the application or database’s behavior when subjected beyond the normal conditions.
Capacity testing – It helps determine the number of users that the system can support at any given time without failing.
Load test helps to verify database behavior during normal and peak conditions.
Windows User (WU) -
Query Design
Queries are used to retrieve data from the database.
A query that is poorly designed takes a lot of time to be executed and this can be the source of the problems to the County’s database. To correct this, there are a number of ways;
Indexing - makes it faster for insert, update and select statements thus improving database efficiency through rapid random look ups.
Avoiding correlated sub - queries- These queries use values from parent query making them to run row-by-row, once for each row returned by the outer query and thus reduces database performance
Select only the required data. Using * when only a specific set of data is needed reduces performance. To make a query run faster then select only specific columns
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.
3
Windows User (WU) -
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
Redundant data leads to wastage in storage capacity as well as reducing efficiency in searching or look up
Normalization up to 3N level is done to eliminate duplication and redundancy and is achieved by logical design of the database while keeping the end user at the back of the mind. The designer must answer the following questions.
What kind of data is to be stored in the database?
How will the user access the database?
What privileges are granted to user?
What are the data commonly accessed ?
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.
4
Multi user access issues and errors
Multi-user access is normally associated with update anomalies leading to compromise in data integrity. A scenario is where one user modifies contents of a transaction and another user accesses the previous value of the same transaction not yet updated and uses for another transaction. Update or delete errors are therefore bound too occur. To deal with these, there are a number of ways;
Pessimistic concurrency 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 and its implementation is easy.
optimistic concurrency control. 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.
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.
5
Enhancements
Serialization – Refers to isolating actions of different database users that are accessing the same data at once and a user will feel like he/she is the only one accessing it.
Basic time stamping - 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 while encouraging multi-user access.
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
6
Reference
msdn.microsoft.com (2016). Types of Performance Testing. Retrieved from:
https://msdn.microsoft.com/en-us/library/bb924357.aspx
www.vertabelo.com (2016). 5 Tips to Optimize Your SQL Queries. Retrieved from:
http://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries
www.informit.com (2016). The Database Normalization Process. Adapted from:
http://www.informit.com/articles/article.aspx?p=30646
www.agiledata.org (2016). Introduction to Database Concurrency Control. Retrieved from:
http://www.agiledata.org/essays/concurrencyControl.html
