http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660The query plan does multiple updates in sequence. For aggregation views it actually aggregates the changed rows for each different view. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment11http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment11Mon, 30 Aug 2010 13:00:11 GMT@ Justin +1 for materialized / schema bound views, being "sytem shortcuts" for triggers. Ultimately the DB needs to infer if _ANY_ object insert / update / delete would alter the output of _ANY_ of the materialized views (Which I guess are little more than tables anyway). I suspect this is the reason they are fairly limited in complexity, otherwise they could cripple modification performance, but in a way that is completely non-obvious. Whilst triggers too maybe less than obvious, at least they are tied to the table you find the bottleneck on. Would be interesting to see the query plan for the insert / updates on tables that form part of the materialized views. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment10http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment10Sun, 29 Aug 2010 18:35:37 GMTHave you tried using count(1) instead of count(*) and seeing if it makes a difference to your execution time? Also if it is ok for the count to be a little stale you could store it as a normal numerical field that gets updated by a batch process (database job in sql server) thats runs every so often? http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment9http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment9Sat, 28 Aug 2010 21:07:44 GMT@Frans Bouma: Materialized views are not only used for aggregation. You can also save tons of join with them. Example: A forum thread. At least the following tables contribute one row for each post: ForumPosts, Users, UserAvatarImages. Then the resultset has to be sorted by datetime. All of this can be materialized so that the forum page needs exactly one range seek. Very cheap, can do that 2k times per second per core. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment8http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment8Sat, 28 Aug 2010 14:49:42 GMT"many people think data has to be 'non-stale' at any given moment, but they forget that only some data has to be 'non-stale'. All the rest can be consumed from for example materialized/indexed views. The post count is a good example for data which is totally acceptable to be 'stale': who's going to check whether user X has indeed 103 posts? no-one." Word! http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment7http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment7Sat, 28 Aug 2010 13:08:35 GMT@Tobi: materialized/indexed views are by definition used in functionality which consumes large buckets of data and processes them for a lengthy period of time for readonly purposes. This alone makes it already not necessary to have the views be 'up to date' at all time, as that's only possible with a live query. many people think data has to be 'non-stale' at any given moment, but they forget that only some data has to be 'non-stale'. All the rest can be consumed from for example materialized/indexed views. The post count is a good example for data which is totally acceptable to be 'stale': who's going to check whether user X has indeed 103 posts? no-one. It therefore comes down to: - decide what data elements can be stale - make sure the non-stale data is fast and easy to retrieve and manipulate. And with 'stale' I don't mean data which is a year old, but for example materialized views (or your own tables with duplicated denormalized data, same thing, if your db doesn't support materialized views) which are refreshed every 10 minutes. For reports about monthly sales etc., that's perfectly fine. This also leads to the point where one could decide to use two distinct databases instead of 1: one for the non-stale data and one for the stale data, which is for example updated every night. It's true that this means one has to think about what the data in a system really _means_ at time T and time T+t. But IMHO that's a good thing, it makes one realize that a database is more than just a bucket to store bits in, and actually a machine which can make things really easy. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment6http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment6Sat, 28 Aug 2010 09:28:25 GMTI'm with Justin - relational databases have an existing, proven, built-in mechanism to implement what you're trying to accomplish; they're called triggers. On one occasion I remember fiddling around with indexed views for a week before giving up and implementing a trigger solution. Quickest, easiest, most sensible and most performant change I ever made to that system. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment5http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment5Sat, 28 Aug 2010 07:47:53 GMTOn the other hand If you _want_ a deferred update system then the automatic view matching in the query optimizer must come in very handy. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment4http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment4Fri, 27 Aug 2010 18:13:34 GMT"This is a hard problem to solve" It is not. It can be solved by using stacked indexed views (search on google to find a pdf from MS; alas not implemented). I was appalled as I learned about oracles deferred materialized views. I believe there is no synchronous option which makes them worthless for many applications. What an unnecessary crippling. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment3http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment3Fri, 27 Aug 2010 18:06:01 GMTAyende, Put both tables in the same table cluster, so that finding the employees is not another disk seek. This gets close to document data bases. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment2http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment2Fri, 27 Aug 2010 17:58:29 GMTProbably one of the biggest deficiencies in the various RDBMS offerings out there is their inability to materialize complex views. This is a hard problem to solve but could be improved and fits very well into the relational model. If you went with a eventually consistent model with a background view generation like some NOSQL products, the the complex view update could be deferred, but you could get stale results. You should try a trigger based solution with MSSQL or Oracle at least as a comparison. Not sure what the aversion to triggers is, they are ideal for creating complex user defined "indexes" such as what you are doing with the Post Count. Materialize views are essentially just declarative system managed triggers that update another table. http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment1http://ayende.com/4609/database-assisted-denormalization-oracle-edition#comment1Fri, 27 Aug 2010 15:12:21 GMT