Tips for improving performance

The cache is an area of memory used by the database server to store database pages for repeated fast access.

Concurrent access of information stored in the database by other transactions is prevented by locks that maintain the reliability of information.

The statement performance summary feature returns execution times for statements when troubleshooting slow statement performance. The SQL Anywhere Profiler uses this feature to display the statement performance summary results.

You can choose whether to optimize query processing to return the first row quickly, or to minimize the cost of returning the complete result set.

To continually improve optimizer performance, the database server automatically updates column statistics during the processing of any SELECT, INSERT, UPDATE, or DELETE statement.

The database server can evaluate search conditions with the aid of indexes.

You can improve your index performance by reorganizing composite indexes or increasing the page size.

Use the optimization_workload option to specify optimization preference.

Use strategic sorting of query results to reduce the amount of unnecessary sorting of data.

Specify the correct cursor type to improve performance.

Although user defined estimates can improve performance, avoid supplying explicit user-defined estimates in statements that are to be used on an ongoing basis.

Consider using materialized views for frequently executed, expensive queries, such as those involving intensive aggregation and join operations.

The WITH EXPRESS CHECK option with the VALIDATE TABLE statement, or the -fx option with the Validation utility (dbvalid) can significantly increase the speed at which your tables validate

Work tables are materialized temporary result sets that are created during the execution of a query.

To improve query processing performance, consider building more efficient queries.

Several tools for testing the performance of queries are provided.

Reducing expensive user-defined functions in queries where they have to be executed many times can improve performance.

Instead of running in autocommit mode, consider grouping your SQL statements so each group performs one logical task.

If your application can tolerate the loss of committed transactions after the most recent checkpoint, then your application may benefit from using in-memory mode.

The lagtime mirror option can impact performance on mirrored systems.

Using a transaction log can provide data protection, and can improve the performance of the database server.

Delayed commits determine the rate of response a database provides to a COMMIT statement.

By default, the database server creates statistics for tables with more than five rows. To create statistics for a table with less than five rows, use the CREATE STATISTICS statement.

Fragmentation occurs as you make changes to your database, and performance can suffer if your files, tables, or indexes are excessively fragmented.

Database tables may contain multiple copies of the same information (for example, a column that is repeated in several tables), and your table may need to be normalized.

Cascading referential actions are costly because they cause updates to multiple tables for every transaction. This can affect performance.

Declaring primary key and foreign key relationships and their constraints can improve performance.

You can improve database performance by putting different database files on different physical devices or drives.

Rebuilding your database can improve performance.

Primary keys and foreign keys can also improve database performance.

Reducing the number of columns in your primary keys can improve performance.

If you have wide tables, and find performance slow consider further normalizing your tables to reduce the number of columns.

The order of the columns in a table affects performance.

Evaluate the use of triggers to see if some of the triggers could be replaced by features available in the database server.

The database page size can affect the performance of your database.

Using the AUTOINCREMENT feature to generate primary key values is faster than other methods because the value is generated by the database server.

You can improve performance by using the appropriate data type for your data.

If you load large amounts of information into your database, you can benefit from the special tools provided for these tasks.

Resource governors can be used to improve performance.

Use the LazyClose and PrefetchOnOpen network connection parameters to reduce the number of requests between the client and server.

Enabling compression for one connection or all connections, and adjusting the minimum size limit at which packets are compressed can offer significant improvements to performance.

In some cases, increasing the packet size can improve performance.