Most performance gains come from query tuning, based on good database design. This guide is devoted to an explanation of Adaptive Server internals with query processing techniques and tools to maintain high performance.
Issues at the application layer include the following:
Decision Support System (DSS) and online transaction processing (OLTP) require different performance strategies.
Transaction design can reduce performance, since long-running transactions hold locks, and reduce the access of other users to data.
Relational integrity requires joins for data modification.
Indexing to support selects increases time to modify data.
Auditing for security purposes can limit performance.
Options to address these issues include:
Using remote or replicated processing to move decision support off the OLTP machine
Using stored procedures to reduce compilation time and network usage
Using the minimum locking level that meets your application needs
