The Omnidex Application Lifecycle

As with most applications, this is the most important step of the entire application lifecycle. During the design step, key decisions are made about how to approach Omnidex queries and updates in the application. These decisions can lead to high-performing applications, and they can also lead to poor performance. It is worth spending the needed time on this step.

This article focuses on the design steps for an Omnidex Application. It does not focus on the steps for application design in general. These steps should be interwoven with the design steps needed for the overall application. Designing an Omnidex Application has six important steps:

1. Deciding on an architecture
2. Understanding the data model
3. Designing an indexing strategy
4. Prototyping on the data server
5. Optimizing queries
6. Prototyping the application

An Omnidex architecture is simply a plan for where the various components of the application will reside and how they will talk to each other. In the simplest applications, everything resides on a single server. There may not even be a web server or an application server. More traditional applications have a web server, an application server and a data server. Omnidex complex applications can add Omnidex index servers, Omnidex Snapshots and Omnidex Grids.

Omnidex can accommodate most of the needs of traditional application architectures. Omnidex Network Services provide standard ODBC and JDBC interfaces that can connect to Omnidex-enhanced databases, allowing traditional segregation between the web, application and data layers. When considering an Omnidex architecture, a primary question is whether Omnidex provides new opportunities to improve the architecture. Omnidex introduces three new concepts to application architects: Omnidex index servers, Omnidex Snapshots and Omnidex Grids.

Omnidex index servers are simply replications of the Omnidex indexes onto different servers. Omnidex can often resolve most queries using only the indexes. This is especially true when performing a lot of queries obtaining counts as users go through the search process. To aid in performance and scalability, some architectures direct count queries to index servers and fulfillment queries to the data server. This can aid in the performance of both steps.

Omnidex Snapshots are physical copies of the database stored in flat files that can be indexed and queried as an independent database. Omnidex Snapshots are very convenient because they can be heavily indexed, easily copied and distributed to different servers. Many businesses direct a great deal of their query traffic to Omnidex Snapshots that are heavily indexed and placed on independent servers. This assures the highest performance without requiring changes to the data model.

Omnidex Grids are databases that have been partitioned to allow parallelism, either across multiple processes on one server or across multiple servers. Omnidex Grids provide many opportunities to direct …

To learn more about Omnidex Architectures, please read the Omnidex Architecture article.

Design begins with obtaining three key pieces of information:

The database schema should show all of the tables, their respective columns and datatypes, and their respective primary and foreign constraints. This schema provides an understanding of the table relationships that is necessary for optimizing table joins. The schema also provides a list of columns for identifying likely candidates for indexing.

Database schemas can be obtained from the underlying relational database, or can be obtained using the Omnidex Administrator program.

The table and column cardinalities help determine the best approach for Omnidex indexing. Tables cardinalities simply show the number of rows in each table. Column cardinalities show the number of distinct values in a column. For example, an INDIVIDUALS table may have a table cardinality of 300 million, meaning that there are 300 million rows in the table. The GENDER column may have a column cardinality of two, meaning that there are two distinct values in the column.

Cardinalities can be obtained from the underlying relational database, or can be obtained using the Omnidex Administrator after an UPDATE STATISTICS statement has been completed on a table.

Sample SQL queries are necessary to effectively determine the appropriate indexing strategy. Some designers may elect to index everything using Omnidex, but this may result in over-indexing, and it also doesn't insure that the correct indexing options are used on each column. It also does not insure that table joins and aggregations will be properly optimized.

The best approach to obtaining sample queries is to log the queries for an application for a period of time. Analyzing these queries allows the designer to understand the patterns of queries, and which types of queries are most common. It also reveals which queries tend to take the most time. Typically, a designer comes up with somewhere between 20 and 100 queries that are to be well optimized by Omnidex.

When analyzing SQL queries, there are several patterns to recognize:

• Table Joins and Nested Queries - It is important to identify which tables are being accessed and how they are being joined together. Omnidex frequently optimizes table joins. Also note any nested queries, including the select item of the inner query and the criteria column in the outer query.

• Criteria Columns - Columns found in the WHERE clause of a SQL statement usually correlate with Omnidex indexes. The literal values in the criteria are not particularly important, unless they contain LIKE operators and wildcards. These indicate a likely use for Omnidex's text indexing features.

• Aggregations - Queries that use the COUNT, SUM, AVERAGE, MIN or MAX functions indicate opportunities for optimization in Omnidex. In these cases, note the columns being aggregated and the columns referenced in the GROUP BY clause.

• Ordering - Queries that have ORDER BY clauses indicate opportunities for optimization in Omnidex. In these cases, note the columns use in the ORDER BY clause. Note that at the present time, only ascending ORDER BY clauses are optimized.

• Select Items - In some situations, Omnidex may satisfy the select items from its own indexes. Note any patterns of queries that return the same few columns for a large number of queries.

1. Prototyping on the data server
2. Optimizing queries
3. Prototyping the application