| Subject | Re: [firebird-support] AW: Performance comparision for primary keys - int vs. varchar |
|---|---|
| Author | Helen Borrie |
| Post date | 2015-10-03T08:13:12Z |
At 10:09 a.m. 3/10/2015, Köditz, Martin Martin.Koeditz@... [firebird-support] wrote:
Although I don't think a BIC (max. 77 bits, commonly 56 bits) would be too much slower than an integer (32 bits) I would not recommend abandoning your atomic keys for keys that are subject to changes by humans. BICs do not change often but they do change! Besides, humans will be entering them into the database, which also makes them a risk to data integrity.
Helen
>The one to give you the inside story on the relative performance of integer versus an 11-character key of 7-bit single-byte characters is Ann Harrison, so be patient and I am sure she will answer you when next she steps ashore.
>Im using integer IDs for primary keys in my tables. What if I would use varchar fields instead? Will I lose performance in that case, especially for big tables? Will joins still work as fast as they do for the integer column.
>
>In my case I have a table TBL_BANK (
>BANK_ID int,
>BIC varchar(11),
>
>)
>
>So what will happen if I switch the PK to BIC in that case? I would change the FKs to BIC too. I dont know how the index keys are build and stored in firebird. So I cannot say this way is faster than the other one. But maybe someone else can do.
Although I don't think a BIC (max. 77 bits, commonly 56 bits) would be too much slower than an integer (32 bits) I would not recommend abandoning your atomic keys for keys that are subject to changes by humans. BICs do not change often but they do change! Besides, humans will be entering them into the database, which also makes them a risk to data integrity.
Helen