Re: datatype for string
OK, explanation: You have a base row that's just under 1010 bytes, if the LVARCHAR contains more than a couple of bytes then only one row will fit on a 2K page (or 3 on a 4K page) even with MAX_FILL_DATA_PAGES set and you will be wasting close to 1000 bytes per page or close to 50% on 2K, 25% on 4K, 12% on 8K, etc. If the rows that do fit on the page have LVARCHAR columns that are more than trivially full, then the row may span multiple pages or there may be even more waste on the home pages if the pagesize is larger. Also, during transmission (OK not sure about this part) IB that the LVARCHARs have to be expanded to full length and NULL padded adding to transmission time and reducing bandwidth. If instead you were to use a sub-table with a 4 byte foreign key, a two byte sequence number, and an 80, 120, or 256 byte comment column then the same base rows fit on their home page and the pagesize can be accurately calculated to minimize waste. The comments sub-table will only transfer the data that is actually there with at most 79, 119, or 255 bytes of waste per parent row (not comment row) on disk or during transfer and half that on average. On top of that, you get the bonus benefit of virtually unlimited comments instead of the 2K or 4K or even 32K limited by the size of the LVARCHAR column that's been defined. Good programming design that fetches the comments in a separate nested loop query instead of joining to the parent table will not expand the parent data, so that's not an issue if your programmers aren't dense. The extra coding comprises about 12 lines of ESQL/C to fetch and reassemble the comments into a single string and often, depending on the application, having the multiple lines will simplify display coding making up for the extra code to fetch the comments from the child table. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 9, 2011 at 12:11 PM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > On 09/08/2011 17:09, Art Kagel wrote: > >> It helps, but that assumes that the LVARCHAR even allows more than one >> row to sit on a page without waste! >> > > I'm missing something.... > > On Tue, Aug 9, 2011 at 12:04 PM, Obnoxio The Clown >> <obnoxio@serendipita.com <mailto:obnoxio@serendipita.**com<obnoxio@serendipita.com>>> >> wrote: >> >> On 09/08/2011 16:58, Art Kagel wrote: >> >> Personally I prefer using a sub-table of smallish CHAR columns to a >> large mostly empty LVARCHAR. That is most efficient in space and >> processing. The only time I would use a large LVARCHAR is when >> the vast >> majority of rows will have the column mostly filled but when >> there are a >> small but significant number of rows that have no content or >> very small >> content. Then I think it is justified. If the size of the actual >> content of the column varies widely, with most rows having >> partially >> full content, I would go the sub-table route. >> >> >> What's wrong with MAX_FILL_DATA_PAGES? >> >> -- >> Cheers, >> Obnoxio The Clown >> >> http://obotheclown.blogspot.__**com <http://obotheclown.blogspot.**com<http://obotheclown.blogspot.com>; >> > >> >> I will now proceed to pleasure myself with this fish. >> >> >> > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.**com <http://obotheclown.blogspot.com>; > I will now proceed to pleasure myself with this fish. >
Hi Art, Just another point of view. Setting aside the performance advantage of sub-tables. With compression we have reduced the used pages some 50 to 80% on tables with varchars. Regards. On Aug 9, 6:45 pm, Art Kagel <art.ka...@gmail.com> wrote: > OK, explanation: You have a base row that's just under 1010 bytes, if the > LVARCHAR contains more than a couple of bytes then only one row will fit on > a 2K page (or 3 on a 4K page) even with MAX_FILL_DATA_PAGES set and you will > be wasting close to 1000 bytes per page or close to 50% on 2K, 25% on 4K, > 12% on 8K, etc. If the rows that do fit on the page have LVARCHAR columns > that are more than trivially full, then the row may span multiple pages or > there may be even more waste on the home pages if the pagesize is larger. > Also, during transmission (OK not sure about this part) IB that the > LVARCHARs have to be expanded to full length and NULL padded adding to > transmission time and reducing bandwidth. > > If instead you were to use a sub-table with a 4 byte foreign key, a two byte > sequence number, and an 80, 120, or 256 byte comment column then the same > base rows fit on their home page and the pagesize can be accurately > calculated to minimize waste. The comments sub-table will only transfer the > data that is actually there with at most 79, 119, or 255 bytes of waste per > parent row (not comment row) on disk or during transfer and half that on > average. On top of that, you get the bonus benefit of virtually unlimited > comments instead of the 2K or 4K or even 32K limited by the size of the > LVARCHAR column that's been defined. Good programming design that fetches > the comments in a separate nested loop query instead of joining to the > parent table will not expand the parent data, so that's not an issue if your > programmers aren't dense. The extra coding comprises about 12 lines of > ESQL/C to fetch and reassemble the comments into a single string and often, > depending on the application, having the multiple lines will simplify > display coding making up for the extra code to fetch the comments from the > child table. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog:http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Tue, Aug 9, 2011 at 12:11 PM, Obnoxio The Clown > <obno...@serendipita.com>wrote: > > > > > On 09/08/2011 17:09, Art Kagel wrote: > > >> It helps, but that assumes that the LVARCHAR even allows more than one > >> row to sit on a page without waste! > > > I'm missing something.... > > > On Tue, Aug 9, 2011 at 12:04 PM, Obnoxio The Clown > >> <obno...@serendipita.com <mailto:obnoxio@serendipita.**com<obno...@serendipita.com>>> > >> wrote: > > >> On 09/08/2011 16:58, Art Kagel wrote: > > >> Personally I prefer using a sub-table of smallish CHAR columns to a > >> large mostly empty LVARCHAR. That is most efficient in space and > >> processing. The only time I would use a large LVARCHAR is when > >> the vast > >> majority of rows will have the column mostly filled but when > >> there are a > >> small but significant number of rows that have no content or > >> very small > >> content. Then I think it is justified. If the size of the actual > >> content of the column varies widely, with most rows having > >> partially > >> full content, I would go the sub-table route. > > >> What's wrong with MAX_FILL_DATA_PAGES? > > >> -- > >> Cheers, > >> Obnoxio The Clown > > >> http://obotheclown.blogspot.__**com<http://obotheclown.blogspot.**com<http://obotheclown.blogspot.com>; > > >> I will now proceed to pleasure myself with this fish. > > > -- > > Cheers, > > Obnoxio The Clown > > >http://obotheclown.blogspot.**com<http://obotheclown.blogspot.com>; > > I will now proceed to pleasure myself with this fish.- Hide quoted text - > > - Show quoted text -