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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Performance » Performance improvement tips

Use indexes effectively Next Page

Use keys to improve query performance

Primary keys and foreign keys, while used primarily for validation purposes, can also improve database performance.


The following example illustrates how primary keys can make queries execute more quickly.

FROM Employees
WHERE EmployeeID = 390;

The simplest way for the database server to execute this query would be to look at all 75 rows in the Employees table and check the employee ID number in each row to see if it is 390. This does not take very long since there are only 75 employees, but for tables with many thousands of entries a sequential search can take a long time.

The referential integrity constraints embodied by each primary or foreign key are enforced by SQL Anywhere through the help of an index, implicitly created with each primary or foreign key declaration. The EmployeeID column is the primary key for the Employees table. The corresponding primary key index permits the retrieval of employee number 390 quickly. This quick search takes almost the same amount of time whether there are 100 rows or 1,000,000 rows in the Employees table.

For more information about how primary and foreign keys work, see Relations between tables.

Using primary keys to improve query performance
Using foreign keys to improve query performance
Separate primary and foreign key indexes