Omnidex specializes in query performance. Omnidex's first goal is to try to optimize the widest number of queries with the simplest installation. This allows administrators to develop, test and deploy applications as quickly as possible. Administrators usually place Omnidex or QuickText indexes on all of the columns involved in criteria, table joins, aggregations, grouping and ordering. Omnidex's SQL Engine then evaluates each statement and optimizes the query using the indexes available. This simple strategy optimizes a large number of queries.
Most companies also a few particularly complex and difficult queries. Surprisingly often, companies have responded to performance issues by changing the database or customizing the SQL based on the suggestions for their underlying database. Experts in Oracle, SQL Server and MySQL all have their favorite optimization techniques. Sometimes tables are denormalized. Sometimes, queries are converted to have many nested queries that are all joined together. Sometimes, queries are broken into many steps, each depositing their results into temporary tables that are subsequently accessed by a later query. Sometimes these lead to performance gains in the underlying database, but often, they can confuse the data model or confuse the queries.
When optimizing queries, the first task is to understand what the query is trying to accomplish. Administrators who try to optimize the query as is often struggle to find a good solution because they are trying to fix their previous solution. Understanding what the query is trying to accomplish allows administrators to fix the underlying problem. Many times, the best approach to optimizing a query with Omnidex is to return to the simpler approach and let the indexes to their best work.
That being said, Omnidex also has it's collection of optimization techniques. Like the underlying database, some of these may involved denormalizing tables or adjusting the SQL statement. Other times, it simply requires using indexing techniques that are highly specific to a particular performance issue. This section will describe these techniques, but keep in mind that the first step is always to understand what the query was originally trying to accomplish, and optimize that problem whenever possible.
The first step to improving optimization is to understand the Omnidex query plans.
See also: