ca.portal.admin

FW: Different flavors of relational database

Discussion created by ca.portal.admin on May 3, 2006
Richard has raised some interesting points here, which of course has
generated more questions on my part:

1) I think the default index in IDMS/SQL serves the purpose below, but
we always drop ours as soon as an alternate key (CALC or index) is
defined. I am now questioning the wisdom of that. Perhaps the default
index should be kept in some cases. Would a CALC chain serve the same
purpose?

If the record type is a sparsely populated record - then retaining the default index is a good idea. However - to make the determination the optimizer needs current statistics. For example - it needs to know that there are fewer record occurrences in the area than there are pages in the area in order to select the index. Obviously if it choses the area sweep in this case then there will be more IO's than there are records.

2) The table in question was stored CALC. The area was only about 20%
full for the test, so we would have been doing a lot more I/O than with
a smaller area. Do you think this extra I/O could account for the large
difference in response time?

CALC without the default or other index is OK if you're providing the CALC key (as an ""equality"" in the query). If you're not providing 'where ... calc-key = calc-keyvalue(s)"" in the query then you'll end up with an area sweep - because there is no default index to use instead of an area sweep. I believe that having the calc key values in an IN ( v1, v2, v3 ...etc) clause works - but there have been issues if the IN () clause contains a sub-query to drag the calc key values out in another query. Don't know how complex your query is so don't know if tis might be an issue.

3) I don't think either SQL Server nor DB2 has a hash storage method.
Does anyone know if their storage methods leave free space embedded
within the data? I am trying to determine if their database would have
been physically smaller, thus requiring less I/O.

Sorry - can't help here.

4) This table was originally stored via an index. We experienced
terrible page contention at INSERT time, and switched to CALC to
alleviate that, which worked beautifully. I have to wonder if SQL
Server or DB2 could handle the online production INSERT volume.

As you discovered - storing via an index is seldom a terrific idea due to page contention unless activity is quite low. Going to CALC was a good idea but as discussed retaining a ""default"" index would have been a good idea - non-keyed (unsorted) but in DBKEY sequence - I don't know how the specifications go but you get the idea.

Outcomes