|Subject||Re: [firebird-support] Composite Primary Key in JOIN - performance|
In general, fixed size small textual fields should be CHAR rather than VARCHAR. Since the value stored is always the same length, the two bytes that describe the actual vs. declared length are wasted. And yes, an integer type would be better if the content will always be numeric. I have a personal problem with small int based on unpleasant experiences when they overflow, wrap around, and are generally a nuisance. But as long as you're certain you'll never have more than 32,767 of them...
...when I take a look at the small lookup tables used to store payment conditions, currency codes, titles, etc...., the primary key is always VARCHAR(4) to either store a generator number of a user defined CODE. But most of the time the value is just coming from a generator and with '0' as prefixes, like '0001', '0002'. It doesn't make much sense to me as the code is not shown anywhere. I guess the design would be better if a SMALLINT was used, am I right ? 2 Bytes instead of 8, correct ?
The PROJECT table has a primary key VARCHAR(7) and more worse the SUBPROJECT table has composite primary key made up of the PROJECTNUMBER VARCHAR(7) AND A UNIQUE SUBPROJECTNUMBER for each project, VARCHAR(4).
So JOINs look like this :
JOIN PROJECT PR ON PR.PR_NR = <AColumn>
JOIN SUBPROJECT SU ON SU.SU_NR = PR.PR_NR AND SU.SU_SUB = <AColumn>
The quotation table has only 30.000 records and is not performing too good. That SUBPROJECT JOIN has to JOIN on 2 fields because of the composite primary key. VARCHAR(7) + (4) = 11, sounds like 22 bytes to me, instead of 4 bytes of an Integer.