Hi Ann,
Thanks a lot for your reply. I'm happy to know that I shouldn't worry too much
about the compound keys. I will look deeper into the queries.
Thanks and best regards, Steve
On Tuesday, August 11, 2015 4:09 PM, "Ann Harrison [email protected]
[firebird-support]" <[email protected]> wrote:
On Aug 11, 2015, at 4:59 AM, [email protected] [firebird-support]
<[email protected]> wrote:
...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 ?
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...
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.
Firebird's index key generator is sensitive to major datatypes and tries to
minimize the key size by eliminating trailing blanks in strings - which won't
exist in your case because the actual data is zero filled on the left, not
blank filled on the right. Numeric keys are represented as a mangled double
precision float. The mangling causes the number to compare correctly bytewise
(moving and inverting the sign) and eliminates trailing zeros after the decimal
point.
Compound keys are handled so they also compare bytewise and should be
reasonably quick.
So, yes, I do think you'd be better off with integer keys (skip the small int)
but no, I don't think that's the problem with your queries. Things that would
help figure out why looking up stuff in a 30K record table is slow might
include: query plan, indexes on non-primary keys, query stats specifically
reads vs. fetches, and probably other things I'm not thinking of now.
Generally, you're right that lying to Firebird by calling a number a variable
length character string will lead to unhappiness... just not this particular
unhappiness.
Good luck,
Ann #yiv3561983098 #yiv3561983098 -- #yiv3561983098ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3561983098
#yiv3561983098ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3561983098
#yiv3561983098ygrp-mkp #yiv3561983098hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv3561983098 #yiv3561983098ygrp-mkp #yiv3561983098ads
{margin-bottom:10px;}#yiv3561983098 #yiv3561983098ygrp-mkp .yiv3561983098ad
{padding:0 0;}#yiv3561983098 #yiv3561983098ygrp-mkp .yiv3561983098ad p
{margin:0;}#yiv3561983098 #yiv3561983098ygrp-mkp .yiv3561983098ad a
{color:#0000ff;text-decoration:none;}#yiv3561983098 #yiv3561983098ygrp-sponsor
#yiv3561983098ygrp-lc {font-family:Arial;}#yiv3561983098
#yiv3561983098ygrp-sponsor #yiv3561983098ygrp-lc #yiv3561983098hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3561983098
#yiv3561983098ygrp-sponsor #yiv3561983098ygrp-lc .yiv3561983098ad
{margin-bottom:10px;padding:0 0;}#yiv3561983098 #yiv3561983098actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3561983098
#yiv3561983098activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3561983098
#yiv3561983098activity span {font-weight:700;}#yiv3561983098
#yiv3561983098activity span:first-child
{text-transform:uppercase;}#yiv3561983098 #yiv3561983098activity span a
{color:#5085b6;text-decoration:none;}#yiv3561983098 #yiv3561983098activity span
span {color:#ff7900;}#yiv3561983098 #yiv3561983098activity span
.yiv3561983098underline {text-decoration:underline;}#yiv3561983098
.yiv3561983098attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv3561983098 .yiv3561983098attach div a
{text-decoration:none;}#yiv3561983098 .yiv3561983098attach img
{border:none;padding-right:5px;}#yiv3561983098 .yiv3561983098attach label
{display:block;margin-bottom:5px;}#yiv3561983098 .yiv3561983098attach label a
{text-decoration:none;}#yiv3561983098 blockquote {margin:0 0 0
4px;}#yiv3561983098 .yiv3561983098bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv3561983098
.yiv3561983098bold a {text-decoration:none;}#yiv3561983098 dd.yiv3561983098last
p a {font-family:Verdana;font-weight:700;}#yiv3561983098 dd.yiv3561983098last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3561983098
dd.yiv3561983098last p span.yiv3561983098yshortcuts
{margin-right:0;}#yiv3561983098 div.yiv3561983098attach-table div div a
{text-decoration:none;}#yiv3561983098 div.yiv3561983098attach-table
{width:400px;}#yiv3561983098 div.yiv3561983098file-title a, #yiv3561983098
div.yiv3561983098file-title a:active, #yiv3561983098
div.yiv3561983098file-title a:hover, #yiv3561983098 div.yiv3561983098file-title
a:visited {text-decoration:none;}#yiv3561983098 div.yiv3561983098photo-title a,
#yiv3561983098 div.yiv3561983098photo-title a:active, #yiv3561983098
div.yiv3561983098photo-title a:hover, #yiv3561983098
div.yiv3561983098photo-title a:visited {text-decoration:none;}#yiv3561983098
div#yiv3561983098ygrp-mlmsg #yiv3561983098ygrp-msg p a
span.yiv3561983098yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3561983098
.yiv3561983098green {color:#628c2a;}#yiv3561983098 .yiv3561983098MsoNormal
{margin:0 0 0 0;}#yiv3561983098 o {font-size:0;}#yiv3561983098
#yiv3561983098photos div {float:left;width:72px;}#yiv3561983098
#yiv3561983098photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv3561983098
#yiv3561983098photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3561983098
#yiv3561983098reco-category {font-size:77%;}#yiv3561983098
#yiv3561983098reco-desc {font-size:77%;}#yiv3561983098 .yiv3561983098replbq
{margin:4px;}#yiv3561983098 #yiv3561983098ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv3561983098 #yiv3561983098ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3561983098
#yiv3561983098ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3561983098
#yiv3561983098ygrp-mlmsg select, #yiv3561983098 input, #yiv3561983098 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv3561983098
#yiv3561983098ygrp-mlmsg pre, #yiv3561983098 code {font:115%
monospace;}#yiv3561983098 #yiv3561983098ygrp-mlmsg *
{line-height:1.22em;}#yiv3561983098 #yiv3561983098ygrp-mlmsg #yiv3561983098logo
{padding-bottom:10px;}#yiv3561983098 #yiv3561983098ygrp-msg p a
{font-family:Verdana;}#yiv3561983098 #yiv3561983098ygrp-msg
p#yiv3561983098attach-count span {color:#1E66AE;font-weight:700;}#yiv3561983098
#yiv3561983098ygrp-reco #yiv3561983098reco-head
{color:#ff7900;font-weight:700;}#yiv3561983098 #yiv3561983098ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv3561983098 #yiv3561983098ygrp-sponsor
#yiv3561983098ov li a {font-size:130%;text-decoration:none;}#yiv3561983098
#yiv3561983098ygrp-sponsor #yiv3561983098ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv3561983098
#yiv3561983098ygrp-sponsor #yiv3561983098ov ul {margin:0;padding:0 0 0
8px;}#yiv3561983098 #yiv3561983098ygrp-text
{font-family:Georgia;}#yiv3561983098 #yiv3561983098ygrp-text p {margin:0 0 1em
0;}#yiv3561983098 #yiv3561983098ygrp-text tt {font-size:120%;}#yiv3561983098
#yiv3561983098ygrp-vital ul li:last-child {border-right:none
!important;}#yiv3561983098
