Tips on optimizing primary index in Axapta

Primary index plays an important role in gaining optimum performance in Axapta. The Primary Index of a table is the main index that is used to uniquely identify records in it.
No duplicated values are allowed in this index. When caching records, primary index is used as the caching key if it exists.
When you designing a table’s primary index, follow these rules:
  1. Keep the “width” of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
  2. If possible, try to create indexes on columns of Integer data type instead of string.  Integer values have less overhead than string values.
  3. Don’t use Real data types for primary keys, as they add unnecessary overhead and can hurt performance.
  4. Indexes on narrow columns are preferable to indexes on wide columns. The narrower the index, the more entries SQL Server can fit on a data page, which in turn reduces the amount of I/O required when accessing the data.
  5. Reduce the size of the index, thus decreasing read I/O during the join process, and increasing overall performance.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: