Project

General

Profile

Bug #2402

Strategy to update denormalized data, cache, pages

Added by Michael Lee over 16 years ago. Updated almost 16 years ago.

Status:
New
Priority:
Normal
Assignee:
Category:
performance
Target version:
Start date:
04/06/2006
Due date:
% Done:

0%

Estimated time:
Bugzilla-Id:
2402

Description

We have a series of items that are stored data, views, etc., that help with performance. These items need to be updated at various times when data changes or is added to the database. We need some strategy for how and when to update these denormalizations.


Related issues

Blocks VegBank - Bug #2002: add VegBranch downloads to cache and fix infinite loop problemResolved03/07/2005

Blocks VegBank - Bug #1861: fix denorm SQL approachResolved01/10/2005

History

#1 Updated by Michael Lee over 16 years ago

A number of fields are "denorm" fields. These are indicated as attModel='denorm' in /vegbank/docs/xml/db_model_vegbank.xml.

Denorm fields are updated in two ways. The first approach was a .sql file (see /vegbank/src/sql/denorm). Then we added a java utility. I don't know what that's called. The utility uses SQL snippets from the SQLStore.properties file (/vegbank/src/java/org/vegbank/common/SQLStore.properties). We used AJaX to update the denorm fields with this utility or with custom jsp tags (vegbank:denorm). The AJaX approach failed because of http timeouts since the updates take a while sometimes.

#2 Updated by Michael Lee over 16 years ago

We also have certain views (stored html which was saved from .jsp files that are dynamic). These are built currently using ant when we build the site (because I didn't know of a better way to do it). This is done by the ant target "cache" in /vegbank/web/build.xml. The views are stored in the "raw" directory, because their contents are just raw, unstyled tables.

#3 Updated by Michael Lee over 16 years ago

XML Representations of our plots take some time to write. We haven't yet developed a system to store these in a cache, but we need to (see bug 2002). Once that is developed, we will also have to update the XML cache when plots are updated and/or added.

#4 Updated by Michael Lee about 16 years ago

We might be able to use triggers to handle all the denorms. Then it's out of sight and out of mind. Update anything and the triggers handle fixing the denorms to match.

#5 Updated by Michael Lee almost 16 years ago

I think we decided against triggers as they proved to be slow and potentially circular. This bug remains of high importance. I think it processes correctly after XML Load, but updates should also be triggered after someone interprets a plant or plot. (and that should update the XML_Cache, too)

#6 Updated by Michael Lee almost 16 years ago

Currently denormalizations are kicked off only when someone loads data. Any subsequent changes to the database need to tie in to the denorm utility and run updates on tables that are affected by the change in question. e.g. when interpreting a plant, you should run denorm update to taxonobservation, which reads some of the denorms from taxoninterpretation, where a new record was just added.

#7 Updated by Redmine Admin over 9 years ago

Original Bugzilla ID was 2402

Also available in: Atom PDF