1,126
edits
Difference between revisions of "IBM CMOD Composite Indexes"
m
Moved caveats section, demoted impacts.
m (Several updates and added section on CMOD Multiple Field Index cost in terms of I/O bandwidth.) |
m (Moved caveats section, demoted impacts.) |
||
| Line 17: | Line 17: | ||
[[File:RequiringIndexedFields.png|800px|Improving IBM CMOD Performance by Requiring Indexed Fields ]] | [[File:RequiringIndexedFields.png|800px|Improving IBM CMOD Performance by Requiring Indexed Fields ]] | ||
= Usage = | = Usage = | ||
| Line 33: | Line 30: | ||
When selecting which Application Group field to add to the composite index, the order of the fields is extremely important. Use the field that has the highest cardinality (the most unique values or, alternately the fewest repeated values) generally followed by the date. This combination allows searches to identify a specific customer's documents nearly instantly, and also zero-in on a specific date range as well. Optimizing the use of Content Manager OnDemand composite indexes requires research into the types of queries that users are performing in OnDemand. Your Database Administrator responsible for IBM CMOD will have access to utilities to help determine how best to achieve better Content Manager OnDemand performance, or you can rely on the experts at [https://cmod.cloud/query-optimization/ CMOD.Cloud]. | When selecting which Application Group field to add to the composite index, the order of the fields is extremely important. Use the field that has the highest cardinality (the most unique values or, alternately the fewest repeated values) generally followed by the date. This combination allows searches to identify a specific customer's documents nearly instantly, and also zero-in on a specific date range as well. Optimizing the use of Content Manager OnDemand composite indexes requires research into the types of queries that users are performing in OnDemand. Your Database Administrator responsible for IBM CMOD will have access to utilities to help determine how best to achieve better Content Manager OnDemand performance, or you can rely on the experts at [https://cmod.cloud/query-optimization/ CMOD.Cloud]. | ||
= Impact on Storage = | =Caveats= | ||
As mentioned above, it's important to use indexes on fields carefully, to ensure maximum benefit at minimum cost in terms of storage space, bandwidth, and time. Also, the more Content Manager OnDemand Application Group fields that have indexing enabled, the more time and effort it takes to create, update, and maintain them, possibly slowing down the load process, and extending maintenance windows for arsmaint. | |||
== Impact on Storage == | |||
Composite indexes will require that you store the contents of both columns, along with a list of database page identifiers. If your CMOD Application Group is especially large, this can cause the database tables for that specific App Group to consume anywhere from 10 to 50% more space. Check with your Database Administrators and System Administrators to ensure that you have enough space to complete the index creation. | Composite indexes will require that you store the contents of both columns, along with a list of database page identifiers. If your CMOD Application Group is especially large, this can cause the database tables for that specific App Group to consume anywhere from 10 to 50% more space. Check with your Database Administrators and System Administrators to ensure that you have enough space to complete the index creation. | ||
= Impact on I/O = | == Impact on I/O == | ||
You may think to yourself, "Storage is cheap, let's index everything!" but there's different cost -- storage bandwidth. In order for indexes to be the most effective, indexes must be '''compact''' - the fewest number of rows in the index with a relatively short list of records following after it. Building Multi Field Indexes in CMOD with a large number of fields could effectively DOUBLE the size of the database table -- making the task of searching it just as slow as not having an index at all. | You may think to yourself, "Storage is cheap, let's index everything!" but there's different cost -- storage bandwidth. In order for indexes to be the most effective, indexes must be '''compact''' - the fewest number of rows in the index with a relatively short list of records following after it. Building Multi Field Indexes in CMOD with a large number of fields could effectively DOUBLE the size of the database table -- making the task of searching it just as slow as not having an index at all. | ||