Prabath, I agree that triggers make comprehending code a bit more difficult. But triggers are a mechanism to achieve active database features, something that is very difficult to get without triggers. Thus, I would not say that they should not be used. I don't understand the statement that triggers are executed outside of SQL transactions: in case your trigger only makes use of SQL statements (in contrast to generating effects outside of the DBMS) the trigger actions are included in the embracing transaction - at least in DB2, which I am familiar with.
Furthermore, the use of stored procedures can be an excellent means to avoid network latency by shuffling data between a client and the DBMS. Thus, it is a means to improve response time, throughput etc. Also, an SQL CALL is part of the embracing transaction. Again, I would not banish the use of stored procedures at all... Best regards, Frank 2014-09-12 10:25 GMT+02:00 Prabath Abeysekera <praba...@wso2.com>: > In addition, triggers, usually, are like stored procedures in its context > and at times, might store some good portion of your business logic within > the database layer which makes them less visible to someone who's > evaluating a particular high-level business use-case just by looking at > some code level implementation. So, they usually go unnoticed, I would say, > a lot more than it is with a stored procedure. So, IMO, it would be good if > we can avoid them whenever possible. > > Cheers, > Prabath > > On Fri, Sep 12, 2014 at 1:21 PM, Asitha Nanayakkara <asi...@wso2.com> > wrote: > >> According to an offline chat I had with PrabathA SQL triggers should be >> avoided since SQL triggers execute outside SQL transactions. >> >> On Thu, Jul 24, 2014 at 6:30 PM, Hasitha Hiranya <hasit...@wso2.com> >> wrote: >> >>> Hi, >>> >>> According to mail arch@ "Removing Global Queue from MB", maybe we need >>> to update this design. >>> Better we discuss upfront if so. >>> >>> It will bring changes to messageStore interface. >>> >>> Thanks >>> >>> >>> On Thu, Jul 24, 2014 at 3:04 PM, Asitha Nanayakkara <asi...@wso2.com> >>> wrote: >>> >>>> With the addition of message expiration feature RDBMS design for >>>> Metadata in MB needs to be changed. >>>> Updated design is as follows. >>>> >>>> >>>> >>>> >>>> * Design considerations* >>>> >>>> - Only a subset of messages comes with message expiration and a >>>> separate thread handles deletion of expired messages. >>>> - Periodically expired message deletion thread queries for a chunk >>>> of expired messages. The subset of messages to go through would be less >>>> when a separated Expiration table is used. However when deleting a >>>> message >>>> from Expiration table should trigger a delete of all the messages with >>>> the >>>> respective message_id from Metadata table. >>>> >>>> In addition, to update reference count of messages an SQL trigger will >>>> be created. Is there any performance hit using triggers? >>>> >>>> >>>> >>>> >>>> On Thu, Jul 24, 2014 at 12:06 PM, Asitha Nanayakkara <asi...@wso2.com> >>>> wrote: >>>> >>>>> With current design in memory message store will be used only in >>>>> single node mode. >>>>> >>>>> >>>>> On Wed, Jul 23, 2014 at 11:36 AM, Dhanuka Ranasinghe <dhan...@wso2.com >>>>> > wrote: >>>>> >>>>>> Also, normally publisher mention whether to persist or not messages >>>>>> in message itself (delivery mode). So based on that MB will process >>>>>> messages in memory and/or persist to a persistence store. So if it's >>>>>> process in memory How do we communicate through the MB cluster? >>>>>> >>>>>> >>>>>> http://activemq.apache.org/what-is-the-difference-between-persistent-and-non-persistent-delivery.html >>>>>> >>>>>> >>>>>> *Dhanuka Ranasinghe* >>>>>> >>>>>> Senior Software Engineer >>>>>> WSO2 Inc. ; http://wso2.com >>>>>> lean . enterprise . middleware >>>>>> >>>>>> phone : +94 715381915 >>>>>> >>>>>> >>>>>> On Mon, Jul 21, 2014 at 12:36 PM, Dhanuka Ranasinghe < >>>>>> dhan...@wso2.com> wrote: >>>>>> >>>>>>> IMO, If we gonna keep huge messages as chunks in memory and insert >>>>>>> into DB as bulk it will heavily affect on MB heap memory. My suggestion >>>>>>> is >>>>>>> we need to handle this case by case. For example, if it's small >>>>>>> messages it >>>>>>> will be efficient to keep in memory while huge messages it will be >>>>>>> efficient to insert into DB early as possible and let others to use heap >>>>>>> memory. For this we will have to make this functionality more >>>>>>> configurable >>>>>>> but again we will have to think about how gonna support fail over >>>>>>> (probably >>>>>>> have to change db schema). >>>>>>> >>>>>>> *Dhanuka Ranasinghe* >>>>>>> >>>>>>> Senior Software Engineer >>>>>>> WSO2 Inc. ; http://wso2.com >>>>>>> lean . enterprise . middleware >>>>>>> >>>>>>> phone : +94 715381915 >>>>>>> >>>>>>> >>>>>>> On Mon, Jul 21, 2014 at 9:00 AM, Asitha Nanayakkara <asi...@wso2.com >>>>>>> > wrote: >>>>>>> >>>>>>>> We are planning to insert message chunks as batch insert queries. >>>>>>>> >>>>>>>> >>>>>>>> On Sat, Jul 19, 2014 at 11:00 AM, Dhanuka Ranasinghe < >>>>>>>> dhan...@wso2.com> wrote: >>>>>>>> >>>>>>>>> Are we going to insert whole message or as chunks >>>>>>>>> On 18 Jul 2014 18:06, "Asitha Nanayakkara" <asi...@wso2.com> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Hi, >>>>>>>>>> >>>>>>>>>> Following is the RDBMS design for WSO2 MB 3.0.0 >>>>>>>>>> >>>>>>>>>> Messages model >>>>>>>>>> >>>>>>>>>> Message metadata model >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Following are the concerns came across in the discussion >>>>>>>>>> >>>>>>>>>> *- Why we use reference counting for message meta data?* >>>>>>>>>> >>>>>>>>>> Reference counting is needed to delete topic messages from the >>>>>>>>>> database reliably in a cluster deployment >>>>>>>>>> >>>>>>>>>> *- How to manage a large tables like Messages table?* >>>>>>>>>> >>>>>>>>>> for Messages table use database partitioning >>>>>>>>>> >>>>>>>>>> For Metadata queries there will be no SQL joins, hence even if >>>>>>>>>> the table would grow large that won't be an issue. >>>>>>>>>> >>>>>>>>>> Inserts and delete operation can be done as batch operations. >>>>>>>>>> >>>>>>>>>> *- Following option to save metadata was rejected due to >>>>>>>>>> following reasons* >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> In the above design MB instance will create Node_Queue tables and >>>>>>>>>> Topic_Node_Queue >>>>>>>>>> tables when each node connects to a cluster. This design was >>>>>>>>>> rejected due to following reasons. >>>>>>>>>> It's DB admins tasks to create and delete tables. MB should not >>>>>>>>>> modify schema when joining to the cluster. There will be instances >>>>>>>>>> where MB >>>>>>>>>> users might not have privileges to create tables. >>>>>>>>>> >>>>>>>>>> *- Supporting several SQL implementations.* >>>>>>>>>> >>>>>>>>>> Since we are using simple SQL operations those will not become an >>>>>>>>>> issue. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Thanks >>>>>>>>>> -- >>>>>>>>>> *Asitha Nanayakkara* >>>>>>>>>> Software Engineer >>>>>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>>>>> Mob: + 94 77 85 30 682 >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> _______________________________________________ >>>>>>>>>> Architecture mailing list >>>>>>>>>> Architecture@wso2.org >>>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>>> >>>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Architecture mailing list >>>>>>>>> Architecture@wso2.org >>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *Asitha Nanayakkara* >>>>>>>> Software Engineer >>>>>>>> WSO2, Inc. http://wso2.com/ >>>>>>>> Mob: + 94 77 85 30 682 >>>>>>>> >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> Architecture mailing list >>>>>>>> Architecture@wso2.org >>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> Architecture@wso2.org >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Asitha Nanayakkara* >>>>> Software Engineer >>>>> WSO2, Inc. http://wso2.com/ >>>>> Mob: + 94 77 85 30 682 >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Asitha Nanayakkara* >>>> Software Engineer >>>> WSO2, Inc. http://wso2.com/ >>>> Mob: + 94 77 85 30 682 >>>> >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> Architecture@wso2.org >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> *Hasitha Abeykoon* >>> Senior Software Engineer; WSO2, Inc.; http://wso2.com >>> *cell:* *+94 719363063* >>> *blog: **abeykoon.blogspot.com* <http://abeykoon.blogspot.com> >>> >>> >>> _______________________________________________ >>> Architecture mailing list >>> Architecture@wso2.org >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> *Asitha Nanayakkara* >> Software Engineer >> WSO2, Inc. http://wso2.com/ >> Mob: + 94 77 85 30 682 >> >> > > > -- > Prabath Abeysekara > Associate Technical Lead, Data TG. > WSO2 Inc. > Email: praba...@wso2.com > Mobile: +94774171471 > > _______________________________________________ > Architecture mailing list > Architecture@wso2.org > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > >
_______________________________________________ Architecture mailing list Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture