Speeding up Searches and Filters

Delphi Database, Delphi Components from ComponentAce

Speeding up Searches and Filters

Previous Top Next

Absolute Database search and filter optimizations are based on the use of available indexes.
So, to speed up searching or filtering, you should create an index which corresponds to the search or filter condition.

Absolute Database Engine can use both ascending and descending indexes for optimization.

If multiple indexes could be used to optimize the search, the Absolute Database Engine estimates the time cost of using each available index and chooses the fastest way.

Always create unique (primary) indexes when possible, as the unique index allow much better optimization then non-unique index.

To speed up a single-field case-sensitive search, such as

ABSTable1.Locate('Company', 'Professional Divers, Ltd.', []);

you should create a case-sensitive index on the search field:

ABSTable1.AddIndex('idxCompany', 'Company', []);

To improve peformance of a single-field case-insensitive filter, such as

FilterOptions := [foCaseInsensitive];

Filter := 'State = ' + QuotedStr('CA');

you should create a case-insensitive index on the search field:

  ABSTable1.AddIndex('idxState_CaseIns', 'State', [ixCaseInsensitive]);

If you have multiple search (filter) conditions joined by an AND operator, such as

  (FirstName = 'Mark') AND (LastName = 'Brown')

Absolute DB will use index on FirstName field or on LastName field for optimization if possible, but the best way to optimize this search is to create a compound index on both fields used in joined conditions:

  ABSTable1.AddIndex('idxFirstNameLastName', 'FirstName;LastName', []);

Note:
To improve peformance of a LIKE condition, you can create an appropriate index, but note that this index can improve performance
for patterns like 'str%' only:

an index speeds up (Name LIKE 'Jo%')

but an index doesn't speed up (Name LIKE '%Jo%') or (Name LIKE '_all')

Please note that indexes will not speed up the following search conditions:

Conditions joined by the OR operator: (State = 'CA') OR (State = 'NY')

Not Equal operator: (State <> 'CA')

IN: (Color IN ('red','blue'))

Condition with expression: (FirstName+LastName = 'Mark Brown')

Note: Each additional index causes slowing down insert / update / delete record operations.

Nov 21, 2024