Precomputed Tables

InterMine can make use of precomputed tables (analagous to materialised views) for faster execution of queries - see a TechnicalOverview. These can represent any SQL query (or InterMine query) and can automatically be substituted into incoming queries by our own cost-based query optimiser. For example, a precompute that joins three tables could be used in a larger query that includes that join thus reducing the total number of tables joined in the query. Template queries can be precomputed completely so that for any any value entered in an editable constraint the query will be executed from a single database table.

There are several ways to create precomputed tables.

Manual specification of queries

You can specify any IQL query to precompute in the file <mine>/dbmodel/resources/genomic_precompute.properties. See an example in flymine. These allow you to design queries you think are likely to be created commonly or be parts of larger queries. It is the place to put queries that will be used in list upload and widgets to ensure they run fast. In <mine>/webapp run:

ant precompute-queries

Template queries - command line

Precomputing template queries makes sure that public templates will always run quickly. You can precompute all templates saved as the superuser in your userprofile database from the command line. This checks each template first to see if it is already precomputed. In <mine>/webapp run:

ant -Dsummarise=false precompute-templates

summarise attempts to restrict dropdowns in templates to only values that will produce results. For example, if there are ten organisms in the database and a template has a dropdown to select the organism, summarising will make that template only allow you to choose those organisms that will produce results. Summarising can be slow so it is better to run it for selected templates from a link on the superuser MyMine saved templates page.

Sometimes it can be slow to precompute complex templates, an ignore flag lets you specify a comma separated list of template names not to precompute. For example:

ant -Dsummarise=false -Dignore=template1,template2 precompute-templates

Template queries - webapp

As the superuser, when you create a new template or edit an existing one there is a 'precompute' link on the MyMine saved templates list. Clicking this will create a precomputed table for just this query. It can take some time to create the tables and requests aren't put in a queue so it is not a good idea to click many of these links at once. The 'precompute' link will change to 'precomputed' once there is a precomputed table created.

Dropping precomputed tables

To drop all precomputed tables in a database in <mine>/webapp run:

ant drop-precomputed-tables