pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Vernon Wu <vernonw@gatewaytech.com>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: Re: Can I search for an array in csf?
Date: Tue, 22 Oct 2002 03:54:56 -0700
Message-ID: <KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko> (raw)
In-Reply-To: <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com>


Hi, Achilleus and Josh,

I know three ways to store dynamic array in DB: object, xml or csv, and sub-table. It seems to me there are some 
problems of using the first method in Java. I don't know how the third method work out. That is the reason I use the 
second method.

In my project, the simplest array type is an array of characters or user IDs. The complexest array is an array of data type 
of userid and another text field. All operations on the arrays are simple: either element look up, add or delete an element. 
Which method is the most suitable for those different operations.

Thanks very much for all your helps.

Vernon


Thanks for your information. See below.

10/22/2002 1:03:56 AM, Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:

>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
>
>






view thread (9+ messages)  latest in thread

Message-ID: <KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko>
Permalink:  ../KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko/
Also on:    postgresql.org/message-id/KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: vernonw@gatewaytech.com, achill@matrix.gatewaynet.com
  Subject: Re: Fwd: Re: Can I search for an array in csf?
  In-Reply-To: <KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko>
  References: <Pine.LNX.4.44.0210221050120.18386-100000@matrix.gatewaynet.com> <KHC91Y84TR3Y6ZOJWRQND9D0FC76SQZ.3db52e80@kimiko>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox