Application design is an important component of assuring relational database performance and efficiency. When performance problems persist it may become necessary to revisit application design. But redesigning and re-coding your applications can be time-consuming and costly, so it is better to properly address good design from the outset.
Design issues to examine when application performance suffers include:
- Type of SQL. Is the correct type of SQL (planned or unplanned, dynamic or static, embedded or stand-alone) being used for this particular application?
- Programming language. Is the programming language capable of achieving the required performance, and is the language environment optimized for database access?
- Transaction design and processing. Are the transactions within the program properly designed to assure ACID properties, and does the program use the transaction processor of choice appropriately and efficiently?
- Locking strategy. Does the application hold the wrong type of locks, or does it hold the correct type of locks for too long?
- COMMIT strategy. Does each application program issue SQL COMMIT statements to minimize the impact of locking?
- Batch processing. Are batch programs designed appropriately to take advantage of the sequential processing features of the DBMS?
- Online processing. Are online applications designed to return useful information and to minimize the amount of information returned to the user’s screen for a single invocation of the program?