Collection - Denormalization
Discussion rather than a problem report: the poster asked whether denormalizing with Informix collection types (SET/LIST/MULTISET, optionally with ROW) brings real performance gains, given drawbacks like no indexes, no aggregates, unpredictable row size and update statistics behaviour. Other participants mainly asked for his use case. The one substantive answer reported collections work well as SPL return values (an older -9628 bug fixed around 11.0), no performance problems in schemas, but poor client/tool support led him to switch to storing XML instead. No firm conclusion or benchmark was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Anyone here already experience some situation where doing a Denormalization using Collection data types ( SET,LIST,MULTISET) and using or not ROW data type have notice some gain of performance or other issues? Some disadvantages is clear, like no indexes, no aggregate functions and unpredictable row size, maybe some incompatible with the language used. Indexes, in some situations is possible to workaround using functional indexes... There is some issues with update statistics? Anyway, there is real vantage? ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Your questions sound somewhat ambiguous. What I can say about denormalizing is that it usually increases row size because there's an increase in redundant data. If you could provide sample schemas before and after the denorm, perhaps we could better help you.
Cesar Inacio Martins wrote: > Hi, > > Anyone here already experience some situation where doing a Denormalization > using Collection data types ( SET,LIST,MULTISET) and using or not ROW data > type have notice some gain of performance or other issues? > > Some disadvantages is clear, like no indexes, no aggregate functions and > unpredictable row size, maybe some incompatible with the language used. > Indexes, in some situations is possible to workaround using functional > indexes... > There is some issues with update statistics? If you can't index it, how can it cause issues with update stats? :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Exactly what I saying , lets use very simple example table - employee - a | Smith - b | John table - work_experience - 1 dba - 2 developer - 3 manager table _ emp_we - a | 1 - a | 2 - b | 3 - b | 1 Changes to table - employee - a | Smith | { dba, developer } - b | John | { manager, dba } using SET data type (just an example) --- Em sáb, 3/4/10, FRANK COMPUTER <frank@frankcomputer.com> escreveu: De: FRANK COMPUTER <frank@frankcomputer.com> Assunto: Re: Collection - Denormalization [19490] Para: ids@iiug.org Data: Sábado, 3 de Abril de 2010, 20:22 Your questions sound somewhat ambiguous. What I can say about denormalizing is that it usually increases row size because there's an increase in redundant data. If you could provide sample schemas before and after the denorm, perhaps we could better help you. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
I just asked about update statistcs because I not found any documentation talking about them. And just for curiosity I not found any information how calculate the row size with collections data type... --- Em sáb, 3/4/10, Obnoxio The Clown <obnoxio@serendipita.com> escreveu: De: Obnoxio The Clown <obnoxio@serendipita.com> Assunto: Re: Collection - Denormalization [19491] Para: ids@iiug.org Data: Sábado, 3 de Abril de 2010, 20:25 Cesar Inacio Martins wrote: > Hi, > > Anyone here already experience some situation where doing a Denormalization > using Collection data types ( SET,LIST,MULTISET) and using or not ROW data > type have notice some gain of performance or other issues? > > Some disadvantages is clear, like no indexes, no aggregate functions and > unpredictable row size, maybe some incompatible with the language used. > Indexes, in some situations is possible to workaround using functional > indexes... > There is some issues with update statistics? If you can't index it, how can it cause issues with update stats? :o) -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
What is your fundamental reason for wanting to denormalize?.. Simplfying your queries, updates, etc?.. You say you would loose ability to index certain columns. On what tables/columns are you currently indexing?.. No entiendo lo que voce esta buscando?
backing to the question in the first post.. Anyone here already experience *some situation* where doing a Denormalization ...... have notice some gain of performance or other issues? --- Em sáb, 3/4/10, FRANK COMPUTER <frank@frankcomputer.com> escreveu: De: FRANK COMPUTER <frank@frankcomputer.com> Assunto: Re: Collection - Denormalization [19494] Para: ids@iiug.org Data: Sábado, 3 de Abril de 2010, 22:24 What is your fundamental reason for wanting to denormalize?.. Simplfying your queries, updates, etc?.. You say you would loose ability to index certain columns. On what tables/columns are you currently indexing?.. No entiendo lo que voce esta buscando? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Cesar, what is your purpose for using a complex data-type (collection of elements) with no ordinal position?
Hi Frank, There a several ways to use Collections Data tyte, isn't all no ordinal position (LIST) and when use together with ROW datatypes your have something like as subtables. But to use it, depends what the situation required. I know some issues what you can have using Collection data types (already cited in last posts) , but maybe you can have some gain in other hand what recompense they use... mainly , performance.... Is this what I looking for, someone what implements in a production environment this and did a big difference for the system performance. If someone did this, how they implements and how much of gain they have versus the difficult to implement the code and administer the table / indexes. I don't pretend implement this now, just looking for examples and who already do that. Maybe I try create a test environment to try discovery if is possible to gain performance implementing this... and how hard is administrate tables with this kind data types (variable row sizing, update statistics, indexes, sorts, etc) Cesar --- Em dom, 4/4/10, FRANK COMPUTER <frank@frankcomputer.com> escreveu: De: FRANK COMPUTER <frank@frankcomputer.com> Assunto: Re: Collection - Denormalization [19498] Para: ids@iiug.org Data: Domingo, 4 de Abril de 2010, 2:44 Cesar, what is your purpose for using a complex data-type (collection of elements) with no ordinal position? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
On Sun, 2010-04-04 at 09:35 -0400, Cesar Inacio Martins wrote: > Hi Frank, > There a several ways to use Collections Data tyte, isn't all no ordinal > position (LIST) and when use together with ROW datatypes your have something > like as subtables. But to use it, depends what the situation required. > I know some issues what you can have using Collection data types (already > cited in last posts) , but maybe you can have some gain in other hand what > recompense they use... mainly , performance.... > Is this what I looking for, someone what implements in a production > environment this and did a big difference for the system performance. If > someone did this, how they implements and how much of gain they have versus > the difficult to implement the code and administer the table / indexes. I use collection data types as return results from SPLs. They work very well in that case if your computed result is complex. Prior to 11.0 however heavily using collection types would eventually result in SPLs failing with -9628 errors. That bug was fixed in 11.0UCsomething(?). I've used collection types in schema before - never noticed any performance issues - but the collections didn't contain key values. However I abandoned them because client support is essentially non-existant. As a replacement we just store XML as values in some tables; every client platform and toolset can process XML. > I don't pretend implement this now, just looking for examples and who already > do that. > Maybe I try create a test environment to try discovery if is possible to gain > performance implementing this... and how hard is administrate tables with this > kind data types (variable row sizing, update statistics, indexes, sorts, etc)
Hi Adam, Nice to know. Is exactly this information what I looking for... difficulties where discourage developers to use this resource. You tried working with functional indexes before abandoned this resource? Can you tell for what kind of data you use it? For relationship or just a real collection data. Do your remember the table definition of what collection data type used? What program language you use? César --- Em dom, 4/4/10, Adam Tauno Williams <adam@morrison-ind.com> escreveu: De: Adam Tauno Williams <adam@morrison-ind.com> Assunto: Re: Collection - Denormalization [19500] Para: ids@iiug.org Data: Domingo, 4 de Abril de 2010, 12:17 On Sun, 2010-04-04 at 09:35 -0400, Cesar Inacio Martins wrote: > Hi Frank, > There a several ways to use Collections Data tyte, isn't all no ordinal > position (LIST) and when use together with ROW datatypes your have something > like as subtables. But to use it, depends what the situation required. > I know some issues what you can have using Collection data types (already > cited in last posts) , but maybe you can have some gain in other hand what > recompense they use... mainly , performance.... > Is this what I looking for, someone what implements in a production > environment this and did a big difference for the system performance. If > someone did this, how they implements and how much of gain they have versus > the difficult to implement the code and administer the table / indexes. I use collection data types as return results from SPLs. They work very well in that case if your computed result is complex. Prior to 11.0 however heavily using collection types would eventually result in SPLs failing with -9628 errors. That bug was fixed in 11.0UCsomething(?). I've used collection types in schema before - never noticed any performance issues - but the collections didn't contain key values. However I abandoned them because client support is essentially non-existant. As a replacement we just store XML as values in some tables; every client platform and toolset can process XML. > I don't pretend implement this now, just looking for examples and who already > do that. > Maybe I try create a test environment to try discovery if is possible to gain > performance implementing this... and how hard is administrate tables with this > kind data types (variable row sizing, update statistics, indexes, sorts, etc) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ________________________________________________________________________________ ____ Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com