Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Performance improvements, diagnostics, and monitoring » Performance » Tips for improving performance

Tip: Supply explicit selectivity estimates sparingly

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

Should the data change, the explicit estimate may become inaccurate and may force the optimizer to select poor plans.

If you have used selectivity estimates that are inaccurate as a workaround to performance problems where the software-selected access plan was poor, you can set user_estimates to Off to ignore the values.

Occasionally, statistics may become inaccurate. This condition is most likely to arise when only a few queries have been executed since a large amount of data was added, updated, or deleted. Inaccurate or unavailable statistics can impede performance. If the database server is taking too long to update the statistics, try executing CREATE STATISTICS or DROP STATISTICS to refresh them.

The database server also updates some statistics when executing LOAD TABLE statements, during query execution, and when performing update DML statements.

In unusual circumstances, however, these measures may prove ineffective. If you know that a condition has a success rate that differs from the optimizer's estimate, you can explicitly supply a user estimate in the search condition.