On 03/07/2013 10:22 AM, Harald Leithner wrote:
> Hmm, SQL...
>
> Whats with triggers and holding this information hot?
>
> for example adding 3 columns to the mailbox table with msg_count,
> msg_count_seen, msg_count_recent
>
> adding a trigger to the messages table to updated this values on update
> in the messages table.
I've tried that. It will very quickly degrade into write-storms and
subsequent deadlocks in the sql server. It didn't scale at all in my
attempts and I didn't see a way around it. Stored procedures are
supposedly very effective on postgresql.
> Another thing are the VIEWs in MySQL are not MATERIALIZED so there is no
> performance gain using it, using a table with triggers would bring much
> better performance.
I didn't even know materialized views existed.
> Or is there a reason not using this, for example your sql schema for
> oracles doesn't use MATERIALIZED VIEWs.
I don't maintain the oracle tables.
Personally I've come to the point were I'd prefer to first define an API
to use that allows swapping in and out different solutions, be it in the
required local database, or in optional external facilities (sharded
sql, key-value stores, solr, varnish).
Some of the current hotspots are easier dealt with than others. The one
you've located (mailbox counters) looks like a hard nut to crack because
of the required atomicity.
.... I always like to postpone working on hard problems, and look for
similar but simpler ones until I feel ready to tackle the big one ...
cheers!
--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin
* Premium Hosting Services and Web Application Consultancy *
www.nfg.nl/[email protected]/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
