On Mon, 21 Oct 2002, Josh Berkus wrote: > > Vernon, > > > >> One field of a table stores an array of characters in a string fromat as > > >> "a,b,c,d". Is anyway to apply a select statement without using stored > > >> procedure? > > > The reason I use this format for an array is that the array is dynamic. I > have quite few cases of this type of situation. The > > maximize length in some cases is known, is unknown in others. I have learnt > the comment separated format is one way > > to solve the problem. Someone also suggested to store the array as an > object. I am not sure whether it works or not. > > The application is written in Java, by the way. > > You should store this data in a sub-table linked through a foriegn key. > Period. Messing with arrays will only lead you to heartache ...
It depends. I can tell you of situations that doing it with child tables will hurt performance really bad. Its just a matter of complexity. One of the apps we run over here, deals with bunker analysis of the vessels of our fleet. For each vessel there are 4 formulas that describe the parameters of the consumption of fuel oil under some given conditions. I have implemented this using arrays. The app is written in J2EE. On a dual xeon 2.2 GHz with 1 GB for postgres, it takes about 900 miliseconds to compute some statistics (average, std deviation,etc..) of the consumption of all vessels (about 20 of them) for a period of 3 years (the values are stored for each day). Before going with the formulas, we had a rather primitive scheme originated from the previous cobol application, based on subtable look ups, (and there was no serious computations involved just table lookups). I can tell you the performance boost was remarkable. > > Try the book "Database Design For Mere Mortals" for a primer on SQL DB design. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
