This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
admin:indexing:activecounts:optimization [2011/01/22 20:40] els |
admin:indexing:activecounts:optimization [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 15: | Line 17: | ||
| ==== The Optimization Behind ActiveCounts ==== | ==== The Optimization Behind ActiveCounts ==== | ||
| - | 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 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 query. |
| 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 Omnidex is set up so that all criteria and aggregations can be satisfied within the indexes, Omnidex will be able to provide fast counts along the way. | 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 Omnidex is set up so that all criteria and aggregations can be satisfied within the indexes, Omnidex will be able to provide fast counts along the way. | ||
| Line 27: | Line 29: | ||
| For example, if the original SQL statement presents genders and their counts, using state codes as criteria ... | For example, if the original SQL statement presents genders and their counts, using state codes as criteria ... | ||
| + | |||
| + | <code> | ||
| select GENDER, count(*) | select GENDER, count(*) | ||
| from LIST | from LIST | ||
| where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | where STATE in ('AZ','CA','CO','NM','OR','UT','WA') | ||
| group by GENDER; | group by GENDER; | ||
| + | |||
| + | </code> | ||
| ... and then the subsequent SQL statement presents educations and their counts, using state and gender codes as criteria ... | ... and then the subsequent SQL statement presents educations and their counts, using state and gender codes as criteria ... | ||
| + | |||
| + | <code> | ||
| select EDUCATION, count(*) | select EDUCATION, count(*) | ||
| Line 39: | Line 47: | ||
| GENDER = 'F' | GENDER = 'F' | ||
| group by EDUCATION; | group by EDUCATION; | ||
| + | </code> | ||
| + | |||
| ... Omnidex will recognize that the second query is similar to the first query, except for the addition of new criteria against GENDER. It will recognize that the index pointers from the first search, which represent the Western United States, can be reused. The current query simply requires the addition of the new criteria against GENDER. This saves a great deal of resources. | ... Omnidex will recognize that the second query is similar to the first query, except for the addition of new criteria against GENDER. It will recognize that the index pointers from the first search, which represent the Western United States, can be reused. The current query simply requires the addition of the new criteria against GENDER. This saves a great deal of resources. | ||
| Line 55: | Line 65: | ||
| This optimization does rely on the succession of queries occurring on the same connection. If a connection unexpectedly closes, Omnidex will simply reprocess all of the criteria, delivering the correct answer without any intervention by the application. | This optimization does rely on the succession of queries occurring on the same connection. If a connection unexpectedly closes, Omnidex will simply reprocess all of the criteria, delivering the correct answer without any intervention by the application. | ||
| + | |||
| + | === Rollup Tables === | ||
| + | |||
| + | ActiveCounts on large databases will also benefit from the use of Rollup Tables. A Rollup Table pre-aggregates another table and stores the summary results. Rollup Tables can then be indexed on their own, and can be used to automatically optimize queries. With ActiveCounts, it can be quite helpful to pre-aggregate a large database using the commonly used criteria. | ||
| + | |||
| + | For example, a Rollup Table that contained the STATE, GENDER, EDUCATION, EMPLOYMENT and INCOME codes could entirely satisfy the query described above. The original table could contain hundreds of millions of rows, but the Rollup Table may contain only 100,000 rows. This ensures the performance of these queries. | ||
| + | |||
| + | Consult the section on [[admin:features:rollups:home|Rollup Tables]] for instruction on designing and setting up Rollup Tables. | ||
| + | |||
| + | |||