Database Changes for Increased Performance

Michael Neel – FabGuard Product Manager, Newton MA

Database Settings and FabGuard Changes

For FabGuard versions 8.5.7 and higher, three changes were made to the database schema that significantly reduce the complexity of queries. As a result, database queries run more efficiently and return results in about 50% less time. First, the three table join needed to retrieve Figures of Merit has been reduced to a two table join. Second, additional context has been added to the Figure of Merit table to make row selection more efficient. Lastly, testing on other Database Management Systems has identified that creating indexes to target queried Figures of Merit can have a significant impact on the overall query speed.

Step 1 (Oracle Only)

We recommend that Oracle settings in the database be reverted to default settings. Next, baseline metrics on database performance should be collected so the impact of the changes can be measured.

Step 2

To make more efficient use of the indexes, we recommend that database queries be modified to reduce the use of wild cards. Text comparisons are already slow, text comparisons using wildcards (* and ?) are even slower. Exact matches are expected be significantly faster. For example, “TOOLID01, TOOLID02, TOOLID03, TOOLID04”, a list of exact matches, uses a bitmap OR comparison against the index. This should be faster than a bit-by-bit comparison used to find LIKE matches such as “TOOLID0?”.

It may be easier to set up specific Tool Groups so that a query is made against the Tool Group “ETCH LAM OXIDE” instead of trying to list each individual tool in the query field. In the case where a tool needs to be added or removed from the group, individual reports would not have to be edited if Tool Groups are used. This can be done prior to the database schema change and should have a positive impact on query speeds assuming that tool groups are not too broad, for example, “ETCH” vs. “ETCH LAM OXIDE” or “ETCH AMAT DPS POLY”.

Example

Using the same queries in FabGuard with changes to the Tool ID field from “TOOLID0?” to “TOOLID01, TOOLID02, TOOLID03, TOOLID04” yielded faster overall query times for the same number of rows. This test was performed where average query time per day was reduced by approximately 15% for one test database and 27% for another test database, Figures 1 and 2. This can be attributed to the fact that it is much easier to do an exact match on a field than to check for likeness.

Figure 1: Database 1. The average query time per day was reduced by approximately 15% when the Tool ID field was changed from “TOOLID0?” to “TOOLID01, TOOLID02, TOOLID03, TOOLID04”.

Figure 2: Database 2. The average query time per day was reduced by approximately 27% when the Tool ID field was changed from “TOOLID0?” to “TOOLID01, TOOLID02, TOOLID03, TOOLID04”.

Step 3

Next, we want to add new compound indexes to minimize the number of rows pulled from the Process and Figure of Merit tables. This should reduce the number of rows identified for the table join by the number of distinct Figure of Merit names for a specific process.

The following indexes should be added to optimize the user experience. These examples are for an Oracle database where the number of parallel processors used to build the index is dependent upon the number available for the production system.

create index ix_fom_fk_run_detail_name ON figure_of_merit (fk_run, detail_name)   COMPRESS 2 LOCAL PARALLEL (degree 4) nologging COMPUTE STATISTICS;

create index ix_p_tool_id_chamber_id on process (tool_id, chamber_id) COMPRESS 2 Local PARALLEL (degree 4) nologging COMPUTE STATISTICS;

create index ix_p_tool_id_tool_recipe_id on process (tool_id, tool_recipe_id) COMPRESS 2 Local PARALLEL (degree 4) nologging COMPUTE STATISTICS;

create index ix_p_tool_group_chamber_id on process (tool_group, chamber_id) COMPRESS 2 Local PARALLEL (degree 4) nologging COMPUTE STATISTICS;

create index ix_p_tool_group_tool_id on process (tool_group, chamber_id) COMPRESS 2 Local PARALLEL (degree 4) nologging COMPUTE STATISTICS;

create index ix_p_tool_group_tool_recipe_id on process (tool_group, tool_recipe_id) COMPRESS 2 Local PARALLEL (degree 4) nologging COMPUTE STATISTICS;

Example

This example shows the benefit of the new Figure of Merit index. If there are 5 figures of merit and 5 steps, the current implementation would pull all 25 (5 FoM * 5 steps) for each process record identified.  Process record 2222 is being requested for detail_name ccc. The old index is only on fk_run so the best it can do is provide links to all the Figure of Merit rows that match fk_run regardless of the detail_name. All Figure of Merit records that match the fk_run will be pulled and the ones that do not match the rest of the filters or are the wrong detail name will be dropped during the join process.

With the new index, it is easier to identify the records that match the fk_run and the detail name. This “should” only pull the correct detail_name records. Records matching all steps will be retrieved but since step number is a numeric, it is easier to compare and drop during the join than a text field would be.

Step 4

The last change we recommend is to upgrade to FabGuard version 8.5.7 or higher to utilize the new schema which eliminates the Run table. This effectively reduces the join count in the query from 3 to 2 as described earlier. Additional context was also added to the Figure of Merit table to help with selection and filtering. This step has been shown to more than double query speeds.

Summary

These database changes will increase overall database performance by 50% regardless of the database management system used, (Oracle, PostGreSQL or MS SQL Server). Consult with your local INFCION support team to determine which changes are best for your factory implementation.