Each attribute can have one of several different search types and the search type influences the SQL that is generated when the attribute is used in a filter.

The WILDCARD is the default search type for UPPER, ALN datatypes and is used because it has the highest chance of finding the data which is great for new users and sales demonstrations.

WILDCARD works by adding a SQL like clause e.g. LOCATION like ‘%ABC123%’

While this has the best chance of finding data it can have a dramatic impact on performance because the leading % prevents indexes from being used so the database's query engine performs a Full Table Scan.

I have improved the performance of a number of systems by reconfiguring the search type.

A better alternative would be to use the EXACT search type. This doesn’t insert a % in the where clause. If users wants the flexibility of a LIKE then they can put a % at the end and the where clause will be changed to include the LIKE.

This means that an index can be used to identify a group of rows to search through.

Technote 1375684 provides information on search types.

If you decide to change the search type to EXACT then it is important to prepare users and teach them that they will need to enter % at the end of the value to use a LIKE. You don’t want to swap one set of helpdesk calls about poor performance for another set of calls about users being unable to find their records.

Are there scenarios where WILDCARD could be the most appropriate search type?

Surprisingly yes. If a SQL where clause has a lot of clauses e.g. LOCATION LIKE ‘A%’ or LOCATION LIKE ‘B%’ then it may be more efficient for the database to use a LIKE. In some cases the database may decide to do a full table scan even if indexes are available. ORACLE have previously said that the database is so efficient that it will only use an index if it returns less than 7% of the rows in the table.

The important thing is to look at the warnings that are being generated over an extended period of time and see how the attribute is being used. Database tools can be used to evaluate the cost of individual SQL statements and determine if a LIKE would be better.

It is important to remember that changing the search type affects all users whenever this attribute is used in a filter.

Vetasi customisation

When we analyse logs we generate a HTML report that highlights inefficient SQL and the report suggests that the search type may have been set to WILDCARD.

The search type setting is one of several different areas to investigate when you have performance problems. It is possible to tune individual pieces of SQL but there other Maximo specific factors that can affect performance. We run SQL performance training courses for our staff and customers to explain the different areas and how to influence the SQL that is generated.