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?