WARNING: This web is not used anymore. Please use PDBService.CMSECALConditionDatabase instead!
 

General information

The coordinator is Francesca Cavallari (Francesca.Cavallari@cernNOSPAMPLEASE.ch).
The main developer of this application is Rick Egeland (Ricky.Egeland@cernNOSPAMPLEASE.ch).
Some documents discussing the project are here: http://agenda.cern.ch/fullAgenda.php?ida=a053843

Recommendations

1. For numeric condition data use BINARY_FLOAT and/or BINARY_DOUBLE:

  • much better perofmance in case of server side calculations like variance or standard deviation.
  • smaller space consumption (5 or 9 bytes while NUMBER type consumes 1 byte for each pair of digits + 1byte)
  • no need for conversions when storing datawith C,C++ programs

2. For bool condition data CHAR(1) should be used

3. ChannelView, ViewDescription tables are strongly denormalize wich is usually not good:

  • possibly other reprezentations shoud be investigated

4. COND_type, CNDC_type

a. Till column could be update by a trigger - it would be especially efficient with channels stored in CLOB

b. Maintaining condition data in two different forms (values in rows and in clobs) is probably not the best idea. Unified form would be desired.

c. Instead of storing data in CLOB you should try such possibilities like IOT tables, complex column types:

  • using CLOBs to store encoded values is not much different from storing data in flat files with names including since and till dates - you loose many advantages that relational DB gives you; particularily selecting a value for a single channel is not possible or very expensive;
  • instead of having the primary key in the form (logic_id, since, till) you should try (since, till, logic_id); this should eliminate full index scans and replace them with range scans in case of reading data for all channels for a given period.
  • try to use index-organised tables for storing values in rows (index created as in the previous point)
  • if you will not achieve desired performance try to use 1 row per IoV with values stored in a column of a complex type; one possibility would be to use VARRAY and map somehow local_id to table indexes; you can also use VARRAY of Objects with 2 attributes: local_id and value the other possibility is to created so-called nasted table;

d. Marking a set of columns as a primary key causes compound index creation on this set of columns so usually you do not need to create any additional indexes on those columns; you should keep number of indexes as small as possible; insert to a table with 3 indexes is ~10 times slower than to the same table without indexes.

e. For the real production we need to consider range partitioning of COND_type, CNDC_type tables and their indexes to keep this objects relatively small.

f. SELECT * FROM COND_table WHERE logic_id = id AND since <= t AND rownum = 1 ORDER BY since DESC

  • I am not sure but is seems to me that this query is relively fast because it takes the first row meeting logic_id = id AND since <= t criteria and returns it.
  • sort operations on big sets of data is usually quite expensive.
Edit | Attach | Watch | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r3 - 2005-11-29 - unknown
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    PSSGroup All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback