Overview | Example | SQL | Optimization | Tips

The SQL statements that allow ActiveCounts are fairly ordinary. They are primarily a succession of grouped aggregations, with an additional piece of criteria added at each step. The key to ActiveCounts lies in the optimization of these queries. Omnidex has special optimizations for this particular kind of queries.

The first optimization is Omnidex is specifically designed as a count engine. By allowing many indexes to be used together on the same search, and by maintaining a count at each step of the search, it is ideally suited to providing fast counts. As long as all of the criteria and the aggregations can be satisfied using Omnidex indexing, Omnidex will be able to provide fast counts along the way.

As Omnidex processes SQL statements for a connection, it watches for queries that are very similar to the previous query. If the current query only differs from the previous query by the addition of an additional piece of criteria, it will recognize that it does not have to repeat the original criteria. Omnidex will simply append the additional criteria to the existing search, greatly speeding the search.

For example, if the original SQL statement presents genders and their counts, filtered by state codes …

select        GENDER, count(*)
  from        LIST
  where       STATE in ('AZ','CA','CO','NM','OR','UT','WA')
  group by    GENDER;

… and then the subsequent SQL statement presents educations and their counts, filtered by state codes and genders …

select        EDUCATION, count(*)
  from        LIST
  where       STATE in ('AZ','CA','CO','NM','OR','UT','WA') and 
              GENDER = 'F'
  group by    EDUCATION;

… Omnidex will recognize that the state codes were processed in the previous search. Rather than repeating this search, it will start with the existing search and simply add the criteria for gender. This can save a great deal of resource.

Consider an user who steps through five pieces of criteria. The first search has one criteria. The second has two … the original criteria plus the new one. The third search has three criteria … the original two criteria plus the new one. And so forth. By the time the user submits the SQL statement with the fifth piece of criteria, fifteen index searches will have been performed.

Since Omnidex can recognize this type of query sequence and reuse previous qualification, it will only process five pieces of criteria. Each new criteria is simply appended to the previous search. This greatly reduced the time for the query, as well as reducing the load on the system resources.

This optimization is automatic. Application developers can simply submit queries in sequence, and Omnidex will recognize when it can use this optimization. No special syntax is required from the developer.

This optimization relies on the succession of queries occurring on the same connection. Sometimes connections are unexpectedly closed. If this happens, Omnidex will automatically reprocess all of the criteria so that the correct results are returned.

final search that has five criteria. The first search had one criteria; the second had two; the third had three, and so on. If all criteria was processed each time, there would be a total of fifteen index searches across all of the

Prev | Next

See also: