Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 468EA475DDC for ; Tue, 22 Oct 2002 03:50:29 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 9BE0F475D39 for ; Tue, 22 Oct 2002 03:50:27 -0400 (EDT) Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.11.6/8.11.6) with ESMTP id g9M83vJ18411; Tue, 22 Oct 2002 11:03:57 +0300 Date: Tue, 22 Oct 2002 11:03:56 +0300 (EEST) From: Achilleus Mantzios To: Josh Berkus Cc: vernonw@gatewaytech.com, Subject: Re: Fwd: Re: Can I search for an array in csf? In-Reply-To: <200210212002.52843.josh@agliodbs.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/270 X-Sequence-Number: 9987 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: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr