Performance Tuning in iBase Designer

iBase automatically indexes certain system tables when an iBase SQL Server database is created or upsized. It will also index those columns within user-defined tables where the

Before you begin

Running Performance Tuning on an SQL Server database requires VIEW DEFINITION permission on the SQL Server database. You need to grant this permission to the user mapped to the SQL Server log in. You can use an SQL script similar to this:

GRANT VIEW DEFINITION TO username

For example, if users connect to iBase using Windows™ authentication, and the user who is running Performance Tuning is called iBaseAdmin and is a member of the YourDomain domain:

GRANT VIEW DEFINITION TO [YourDomain\iBaseAdmin]

You should revoke this permission after you run Performance Tuning:

REVOKE VIEW DEFINITION TO username

In addition, if present remove the Full-Text Search index. It is not possible to run the Performance Turning wizard while a Full-Text Search index exists.

About this task

Query optimized indexing is of significant benefit even if your database has no user-defined indexes. The index rules are used whenever you:

  • Create an SQL Server database.

  • Upsize an existing database to SQL Server.

Note: Upsizing removes any indexes that were created manually in Microsoft™ Access.

Procedure

  1. In iBase Designer, select Tools > Database Setup > Performance Tuning.

    Note: If necessary, you can stop the process and resume it later. However, until you complete this process, the database is only partially indexed and some parts of iBase might perform slowly. Also, certain commands such as Schema Integrity Check do not display, check, or repair the indexes.

  2. You can use the Schema Integrity Check to restore missing indexes on user-defined tables in an SQL Server database. In iBase Designer, log on as a database administrator but do not open the database, and from the Tools menu, select Database Administration > Schema Integrity Check. For information on using this dialog, see Checking the integrity of the schema. If the database uses the original iBase index rules, the command restores the indexes to conform to those rules. If the database uses the query optimized index rules, the command restores the indexes to conform to the query optimized rules.