Обсуждение: Trigger vs web service
I receive a long string (about 1 per second) than content many information and 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
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
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.
> Date: Mon, 4 Apr 2011 09:57:11 -0700
> From: pierce@hogranch.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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?
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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.
Re: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
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
Re: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
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:12 PM, Leif Biberg Kristensen wrote: > Probably something starting with P. Pascal? Prolog?? PL/I ? ! ? :)
Re: ..horribly documented, inefficient, user-hostile, impossible to maintain interpreted language..
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> 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