RE: record size in modern non SE, databases
Marco, I've got many more grey hairs than you have, and when I first started using Informix there was a well-known accounts system that used pad characters to ensure that floats and integers aligned to word boundaries. The filesystem design pre-dated C-ISAM and it was believed that by aligning to these boundaries that it would get improved performance. This presented a whole raft of problems for people who tried to map SQL over these C-ISAM files, and even more for users who tried to write 4GL as they couldn't specify where variables would start and end in a 4GL program. The initial correspondent was well-known to me in those days as I provided support to him in those dim and distant days. As far as I am aware the way that most cpus work nowadays there is no difference as to whether anything starts on a boundary or not. But I stand to be corrected on that statement. And even that old accounts system has done away with the PAD characters in recent versions. Regards Malcolm Weallans (20 years on Informix engines and still going strong) -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Marco Greco Sent: 17 August 2007 09:28 To: ian; informix-list@iiug.org Subject: Re: record size in modern non SE, databases ian wrote: > Hi All; > > Is the row size the actual size layed out on the disc drive/storage > medium? Errrm, no, not necessarely. varchars have a byte count and are truncated. decimals, datetime and intervals are compressed. smart and dumb blobs only sport a descriptor, although to be fair, it's the descriptor size that's used to calculate the row size. raw types are aligned to word bundaries, ie padding is involved. and we haven't even gotten to to the fact that in IDS, information is stored in 'pages' which among other things have other information that describes them, and there are special 'pages' that are used to keep track of how the information is laid out on disk (oversimplifying thing considerably here) except that what I am talking about here is an engine that you are most probably not using! I guess, before anyone can give you an answer, you really need to be more specific about - which engine (if any) are you using, and - what are you trying to achieve. in general people care about the layout of tables on disk, not how each row is aligned. > > In the SE I remember that each record was terminated. > > In trying to make each record start on a binary boundary, would > something like > int > char(1) > char(1) > char(2) > have sector size / 8 records, nice to the binary world ? > This means I can keep each int ( foreign key to a primary one in > several tables) on the 64 bit word. > > I have never worked out why there is no '.bin" in the "definition" > world to pad records out to solve this. > > Correct ? > > Regards > Ian > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Ciao, Marco ____________________________________________________________________________ __ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On Aug 17, 5:02 am, "malcolm.iiug" <mali...@btopenworld.com> wrote: > Marco, Malcolm, Even more grey that you, but it doesn't matter. IDS does NOT align vars within the row in disk. When the data is returned through the ESQL/C or ODBC library it is realigned to the alignments required by the host variables. If you write any DYNAMIX SQL using sqlda structures, you have to adjust the address offsets for each field in the incoming record to match the default alignments in whatever data structures you are using. The library provides functions for calculating these alignments in a client system dependent way. Since the server and client could be in different architectures, the choices would be to align to worst case offsets (see the Motorola description below) or don't align on the server at all and let the clients handle correct alignment. Informix chose the latter method, making the tools and their libraries align by default during row deblocking. To show this just create a table with a CHAR(1) column followed by an INT column and see what the reported row size is. As to alignment and performance, there are still processors, Intel and Sparc for example, which perform slightly better if the data are aligned on word size (ie 32bit) boundaries. There were until very recently processors, notably those from Motorola, for which you had to generate special assembler to handle vars not aligned on type size boundaries (ie 16bits for shorts, 32bits for integers and floats, 64bits for double) without getting an alignment error at runtime which behaved like a SEGV and crashed your app. Compilers for Motorola chips had compile time flags to generate this special code and the apps ran noticably slower if you enabled the code, even if all vars were actually aligned. Art S. Kagel > I've got many more grey hairs than you have, and when I first started using > Informix there was a well-known accounts system that used pad characters to > ensure that floats and integers aligned to word boundaries. The filesystem > design pre-dated C-ISAM and it was believed that by aligning to these > boundaries that it would get improved performance. This presented a whole > raft of problems for people who tried to map SQL over these C-ISAM files, > and even more for users who tried to write 4GL as they couldn't specify > where variables would start and end in a 4GL program. > The initial correspondent was well-known to me in those days as I provided > support to him in those dim and distant days. > As far as I am aware the way that most cpus work nowadays there is no > difference as to whether anything starts on a boundary or not. But I stand > to be corrected on that statement. And even that old accounts system has > done away with the PAD characters in recent versions. > > Regards > > Malcolm Weallans > (20 years on Informix engines and still going strong) > > -----Original Message----- > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] > > On Behalf Of Marco Greco > Sent: 17 August 2007 09:28 > To: ian; informix-l...@iiug.org > Subject: Re: record size in modern non SE, databases > > ian wrote: > > Hi All; > > > Is the row size the actual size layed out on the disc drive/storage > > medium? > > Errrm, no, not necessarely. > varchars have a byte count and are truncated. > decimals, datetime and intervals are compressed. > smart and dumb blobs only sport a descriptor, although to be fair, it's the > descriptor size that's used to calculate the row size. > raw types are aligned to word bundaries, ie padding is involved. > > and we haven't even gotten to to the fact that in IDS, information is stored > in 'pages' which among other things have other information that describes > them, and there are special 'pages' that are used to keep track of how the > information is laid out on disk (oversimplifying thing considerably here) > > except that what I am talking about here is an engine that you are most > probably not using! > > I guess, before anyone can give you an answer, you really need to be more > specific about > - which engine (if any) are you using, and > - what are you trying to achieve. > > in general people care about the layout of tables on disk, not how each row > is > aligned. > > > In the SE I remember that each record was terminated. > > > In trying to make each record start on a binary boundary, would > > something like > > int > > char(1) > > char(1) > > char(2) > > have sector size / 8 records, nice to the binary world ? > > This means I can keep each int ( foreign key to a primary one in > > several tables) on the 64 bit word. > > > I have never worked out why there is no '.bin" in the "definition" > > world to pad records out to solve this. > > > Correct ? > > > Regards > > Ian > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > -- > Ciao, > Marco > ____________________________________________________________________________ > __ > Marco Greco /UK /IBM Standard disclaimers > apply! > > Structured Query Scripting Languagehttp://www.4glworks.com/sqsl.htm > 4glworkshttp://www.4glworks.com > Informix on Linuxhttp://www.4glworks.com/ifmxlinux.htm > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list