Performance optimization for Sybase database

In a large database, performance has become one of the focus of attention, how to make the database efficient and efficient operation of the vast number of database managers and developers must consider the problem.

Performance is a measure of efficiency when an application or multiple applications run in the same environment. Performance is often represented by response time and productivity. Response time is the time it takes to complete a task, and you can reduce response time in the following three ways:

· Reduce competition and wait times, especially disk read and write waits

· Take advantage of faster parts

· Reduce the time required to leverage resources

Most of the performance is derived from excellent database design, accurate query analysis and appropriate indexing. The best performance can be achieved by establishing a good database design and learning to use the SQL Server query optimizer at development time.

In order to achieve better database performance, we need to optimize the database, reduce the competition of system resources, such as data cache, process cache, system resources and CPU competition.

In SQL Server, there is a level of optimization:

• Application Layer-Most of the performance is derived from the optimization of queries in your SQL application, which must be based on a good database design.

• Database Layer-Applies resources that are shared in the database tier, including hard drives, transaction logs, and data cache.

• Server Layer--there are many shared resources at the server level, including data caching, process caching, locks, CPUs, and so on.

• Device Layer-refers to the disk where the data is stored and its controller, where you should pay particular attention to disk I/O.

• Network Layer-A network that connects users and SQL Server.

• Hardware Layer-refers to the available CPU.

• Operating system Layer-Ideally, SQL Server is the only major application of a machine, and it must share the processor, memory, and other resources with the operating system and other Sybase software, such as backup server or SQL Server monitor.

In most cases, we are optimizing the application layer, because the application performance optimization is the most acceptable function, the results can be observed and tested, query performance is a key to the overall performance of SQL applications.

Issues on the application tier include the following:

• Decision Support vs. and online transaction processing (OLTP) requires different performance strategies

• Transactional design reduces concurrency because long transactions hold locks and reduces access to related data by other users

• Association consistency requires join operations for data modification

• Indexes that support select operations increase the time to modify data

• Audits established for security limit performance

The options that are optimized at the application tier include:

• Remote processing or replication processing enables the separation of decision support from the OLTP machine

• Use of stored procedures to reduce compilation time and network utilization

• Use minimal locks to meet your application needs

Problems with the database layer include:

• Establish backup and recovery scenarios

• Distribute storage data on the device

• Audit operations affect performance; Audit only what you need

• Daily maintenance activities will result in reduced performance and result in user inability to manipulate database tables

Optimizing selections on the database layer includes:

• Automatically dump transaction logs using the transaction log's thresholds to prevent them from exceeding use space

• Monitoring space use with thresholds in data segments

• Use partitioning to speed up data loading

• Object positioning to avoid competition on the hard drive

• Put the important table and index into the cache, to ensure that at any time to obtain