Trigger vs web service

On 04/04/11 8:47 AM, Marc-André Goderre wrote:

1- Program a trigger function detecting the orginal insert, split the
string and fill the other field.

2- Program a web service for receiving the string, split it and insert
the informations in the db.

Witch is the fastest one (in performance).

I would expect parsing and splitting your string into fields before
handing it to SQL would be faster than handing it into SQL, then using a
trigger to hack it into 2 fields. This would, of course, at least
partially depend on what sort of language that web service is written
in, if its in some hypothetical horribly inefficient interpreted
language, all bets are off.

Does all your data go through a web service now? if not, what data
path IS it coming from?

I receive a long string (about 1 per second) than content many information. For the moment it is directly inserted in the database (1 column).
I have to treat the hole string every time i need information in it.

Now, I want split the sting and save the informations in differents fields.
I have 2 solutions and would like to have your opinion on them.

1- Program a trigger function detecting the orginal insert, split the string and fill the other field.
2- Program a web service for receiving the string, split it and insert the informations in the db.

Witch is the fastest one (in performance).

Thanks

Marc-Andre Goderre
TI Analyst

..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
to whom might you be alluding to
???

Martin
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

On Monday 04 April 2011 21:07:38 Martin Gainty wrote:

..horribly documented, inefficient, user-hostile, impossible to maintain
interpreted language.. to whom might you be alluding to
???

Probably something starting with P.

On 04/04/11 12:07 PM, Martin Gainty wrote:

..horribly documented, inefficient, user-hostile, impossible to
maintain interpreted language..
to whom might you be alluding to

I only used a few of those adjectives, and prefixed them by
hypothetical. to be honest, I would expect most languages commonly
used in web service environments to be more efficient at string
processing than pl/pgsql, and I really can't think of a counterexample
off the top of my head.

John R Pierce <pierce@hogranch.com> Monday 04 April 2011 21:20:51

On 04/04/11 12:07 PM, Martin Gainty wrote:

..horribly documented, inefficient, user-hostile, impossible to
maintain interpreted language..
to whom might you be alluding to

I only used a few of those adjectives, and prefixed them by
hypothetical. to be honest, I would expect most languages commonly
used in web service environments to be more efficient at string
processing than pl/pgsql, and I really can't think of a counterexample
off the top of my head.

Java is such funny example, splitting even large strings is faster then in C,
because string is wrapper around char[], and splited string will be only
wrapper around same array, but with updated start, and len. But other
operations, like manual search, or creating string from array may be slower.

In any case if you think application will "grow", then I suggest you to use
higher language then triggers. You will get access to better libraries, code
is simpler to maintain, as well application is simpler to deploy. From Java
point of view, PG is currently only one, and if you put there processing, even
if you will get 10-20% boost, then with new users you may need to buy new and
_replace_ old server, in Java you may add new server to cluseter. Same with
PHP, just use Apache load balancer. Choice is yours.

Regards,
Rdek

On 04/04/11 12:12 PM, Leif Biberg Kristensen wrote:

Probably something starting with P.

Pascal?

Prolog??

PL/I ? ! ?

:)

On Monday 04 April 2011 21:20:51 John R Pierce wrote:

On 04/04/11 12:07 PM, Martin Gainty wrote:

..horribly documented, inefficient, user-hostile, impossible to
maintain interpreted language..
to whom might you be alluding to

I only used a few of those adjectives, and prefixed them by
hypothetical. to be honest, I would expect most languages commonly
used in web service environments to be more efficient at string
processing than pl/pgsql, and I really can't think of a counterexample
off the top of my head.

I had to move a piece of regexp/replace logic from PHP into pl/pgsql because
PHP couldn't handle more than abt. 50 replacements in one text unit, instead
it just dumped the text in the bit bucket. It was probably a memory allocation
problem. On the other hand pl/pgsql has had no problem with the logic.

Documentation here:

<http://solumslekt.org/blog/?p=23&gt;

regards, Leif

On Mon, Apr 4, 2011 at 2:20 PM, John R Pierce <pierce@hogranch.com> wrote:

On 04/04/11 12:07 PM, Martin Gainty wrote:

..horribly documented, inefficient, user-hostile, impossible to maintain
interpreted language..
to whom might you be alluding to

I only used a few of those adjectives, and prefixed them by hypothetical.
to be honest, I would expect most languages commonly used in web service
environments to be more efficient at string processing than pl/pgsql, and I
really can't think of a counterexample off the top of my head.

most language *are* more efficient at string processing but that's not
the whole story, since to get at that benefit you typically have to:

1. application makes query to get the data
2. database searches for data, converts it to wire format and sends it
through protocol to libpq
3. libpq wrapper converts it to language native string (unless you are in C)
4. language string processing takes place
5. data is re-stacked into queries and sent back to the database over
wire format via protocol
6. database writes it out

Now, if your data is not meant for consumption by the database then
the case for application side coding is stronger. But if you are just
manhandling data only to send it right back the database you should
think twice about introducing all those steps to access the benefits.
Not to mention, by introducing a client side procedural language you
are introducing a whole new set of data types, conditions, constraint
checking etc. Procedural languages are also defect factories (this
includes pl/pgsql if written in more procedural fashion, so you should
keep it to sql, or at least in relational style if you can).

pl/pgsql is perfectly fine for string processing as long as your
problem is such that you can avoid heavy iteration (string
concatenation in a loop is especially problematic, but work around
that using arrays is trivial and effective) and manage the strings
relationally and with the built in functions.

The better you are with sql, the less iteration you tend to need. The
server backend string api is fairly rich and can get you through most
light to moderate string processing tasks.

merlin

On 04/05/11 9:40 AM, Merlin Moncure wrote:

On Mon, Apr 4, 2011 at 2:20 PM, John R Pierce<pierce@hogranch.com> wrote:

I only used a few of those adjectives, and prefixed them by hypothetical.
to be honest, I would expect most languages commonly used in web service
environments to be more efficient at string processing than pl/pgsql, and I
really can't think of a counterexample off the top of my head.

most language *are* more efficient at string processing but that's not
the whole story, since to get at that benefit you typically have to:

1. application makes query to get the data
2. database searches for data, converts it to wire format and sends it
through protocol to libpq
3. libpq wrapper converts it to language native string (unless you are in C)
4. language string processing takes place
5. data is re-stacked into queries and sent back to the database over
wire format via protocol
6. database writes it out

in the OP's case, he was asking about strings he was inserting into
postgres, currently he was inserting them as a single long field, but he
wanted to break them up into multiple fields. So, he could send the
long string to a pgsql function that did the dicing up, or he could dice
up the string first then send the pieces to fields of a database. I
was expressing the opinion that its highly likely the 2nd solution would
work better, and I guess my bit of misplaced humor clouded that message.

On Tue, Apr 5, 2011 at 1:04 PM, John R Pierce <pierce@hogranch.com> wrote:

On 04/05/11 9:40 AM, Merlin Moncure wrote:

On Mon, Apr 4, 2011 at 2:20 PM, John R Pierce<pierce@hogranch.com>  wrote:

I only used a few of those adjectives, and prefixed them by hypothetical.
to be honest, I would expect most languages commonly used in web service
environments to be more efficient at string processing than pl/pgsql, and
I
really can't think of a counterexample off the top of my head.

most language *are* more efficient at string processing but that's not
the whole story, since to get at that benefit you typically have to:

1. application makes query to get the data
2. database searches for data, converts it to wire format and sends it
through protocol to libpq
3. libpq wrapper converts it to language native string (unless you are in
C)
4. language string processing takes place
5. data is re-stacked into queries and sent back to the database over
wire format via protocol
6. database writes it out

in the OP's case, he was asking about strings he was inserting into
postgres, currently he was inserting them as a single long field, but he
wanted to break them up into multiple fields.  So, he could send the long
string to a pgsql function that did the dicing up, or he could dice up the
string first then send the pieces to fields of a database.    I was
expressing the opinion that its highly likely the 2nd solution would work
better, and I guess my bit of misplaced humor clouded that message.

right -- it follows from my mantra to 'use built in functions when you
can' that string to array or regexp_split_to_array would probably work
for this case (maybe with some escaping, maybe not).

merlin