FWD » Core Development » Database

We have found on occasion that our default approach of normalizing extent fields of like extent into a secondary table and mapping these to an associated list in Hibernate can cause a performance problem with some queries. OTOH, denormalizing across the board can cause problems with table design (very wide tables when large extent fields are exploded into separate columns) and potentially cause their own performance problems. Also, the associated list approach has caused no end of lazy initialization problems with Hibernate over the years.

Rather than choosing between the normalized and denormalized approaches we use today, we should investigate the implications of converting extent fields in the most natural/analogous way: to array columns.

I had discarded this approach early on, because Hibernate has limitations in how it deals with array columns. I don't recall ATM what those limitations were (possibly related to managed flushes and dirty checking?), but it may be time to revisit this decision. Perhaps the limitations aren't really issues for us after all, given that we use these fields in very specific and well-defined ways.

Note that this task is created in the context of investigating changes that may improve performance, but I don't know that using array columns would be any more or less performant than our current approaches. This requires investigation.

Also note that there is quite a bit of complexity in how we convert where clauses and sorting and how we preprocess where clauses at runtime, which is hard-wired to the current normalized and denormalized approaches. Thus, changing this to support array columns may be a relatively complicated proposition. So, we need to understand whether it is worth the effort from a performance standpoint, before undertaking any changes.

DBAs and BI developers may prefer using array columns from a design perspective, though, in that they would represent the closest design analog to the current schema in Progress.

I believe our testing showed that array columns are faster than using "normalized" (secondary) array tables. We are already moving to "expanded extents" as our default. We can close this task, right?

I think this is dialect dependent. If all supported dialects had a fast native support for this, then the mapping would be perfect. But they don't, or use workarounds (like storing in a JSON format) which will degrade performance unnecessary (because of parsing and conversion to/from string).

With #7020, the full record 4GL is stored in a single SQL record so we can query it directly and fetched in a single SQL statement. So I think we can reject this idea now. At least until all our supported dialects cleanly and faster handle this natively, if ever.

Recently we found out that:

• Most databases have a limit on the number of columns in a table.
  • PostgreSQL: 1600
  • H2: no limit
  • MariaDB: 4096
  • SQLServer: 1024
• Some customers (see #8982) have extext columns and blow past these limits.

With that in mind there is a real value to using native array column support. Even if it is only in the subset of dialects that support it (PostgreSQL does, H2 does, MariaDB does not, SQLServer does not).

Using the stupid JSON packing idea in a varchar is not going to happen because it would make the SQL ridiculous and it would kill performance. So the idea here is to implement native array support where possible but as an optional feature that can be enabled by customers that are willing too accept the following:

• Only use databases that support the feature. OR
• Run multiple conversions (for array column and non-array column cases).

This is only a limitation if the converted code is actually different in these cases. If the code is the same, then this is easily handled as a standard feature instead of an option.

I wanted to investigate this a little bit. 7020d branch makes it easier to implement this. Most places are already using array[index] syntax. For native array support when expand_extents=true is enough to just not replace array[index] with array_index. I tested the syntax in PostgreSQL and initially it seems to work well. SELECT and UPDATE should work ok. I haven't tested INSERTs yet. In DB the fields look something like this:

int_array(int[]) = {1,2,3,4,5,6,7,8,9,10, ...}
text_array(text[]) = {"text1","text2","","","","", ...} 
etc...

I am working on a prototype implementation for this, that I activated only for PostgreSQL dialect by adding a "useArrayField" flag. Some changes I encountered:

create table pt (
   recid int8 not null,
   f1_1 text,
   f1_2 text
   f1_3 text
   f1_4 text
   f1_5 text
   primary key (recid)
);

reate table pt (
   recid int8 not null,
   f1 text[5],
   primary key (recid)
);

Update statements: UPDATE pt SET f1[1] = 'x'. So, Insert and updates should be easy to generate, f1_1 just replaced with f1[1].

Another big change related with DDLs are for Word Table support. Currently we have a trigger that looks like this for an individual field:

create trigger pt__f1_4__upd after
update of f1_1
   on
   pt for each row execute procedure pt__f1_4__trg();

create trigger pt__f1_4__upd after
update of f1_2
   on
   pt for each row execute procedure pt__f1_4__trg();
...
etc.

create trigger pt__f1__upd after
update of f1
   on
   pt for each row execute procedure pt__f1__trg();

create or replace function pt__f1__trg()
returns trigger
language plpgsql
as
$$
begin
   delete from pt__f1 where pt__f1.parent__id = new.recid;
   insert into pt__f1 select * from words(new.recid, 1, new.f1[1], true);
   insert into pt__f1 select * from words(new.recid, 2, new.f1[2], true);
   insert into pt__f1 select * from words(new.recid, 3, new.f1[3], true);
   insert into pt__f1 select * from words(new.recid, 4, new.f1[4], true);
   insert into pt__f1 select * from words(new.recid, 5, new.f1[5], true);
   return new;
end;
$$;

A very important test to do early: does this resolve the issue in #8982?

Greg Shah wrote:

A very important test to do early: does this resolve the issue in #8982?

Yes, it should solve it. I did this:

CREATE TABLE random_letters (
    field1  text[],
    field2  text[],
    field3  text[],
    field4  text[],
    field5  text[],
    field6  text[],
    field7  text[],
    field8  text[],
    field9  text[],
    field10 text[],
    field11 text[],
    field12 text[],
    field13 text[],
    field14 text[],
    field15 text[],
    field16 text[],
    field17 text[],
    field18 text[],
    field19 text[],
    field20 text[]
);

My fingers are crossed for a performance improvement!

Performance doesn't seem to be a big issue, is similar to what we have now.
I could also improve performance for SELECT statements by retrieving the entire array at once:

SELECT field1 FROM public.test_array
ORDER BY recid ASC;

SELECT field1[1], field1[2], field1[3], field1[4], field1[5],
field1[6], field1[7], field1[8], field1[9], field1[10],
field1[11], field1[12], field1[13], field1[14], field1[15]
FROM public.test_array
ORDER BY recid ASC;

That improvement is only possible in native array column mode, right? So there is a potential win here for the native mode.