Re: Fwd: Re: Can I search for an array in csf?

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(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr