History

1. History Many teams have made great efforts to use relational databases in projects only to have to supplement them in order to meet performance demands.

2. Common 3-tier Architecture

3. Physical Architecture • Web Farm • Caching Server • CDN - Media • Application Server • Search Engine Server • Database Server • SANS or Disk Array

4. The Path: Education, Experiences, Vendor Influences • In the 90s Microsoft development best practices, code samples and designs started with designing the database first • Eventually ORMs NHibernate 2004, Linq to SQL Nov 2007, and Entity Framework August 2008

5. ORMs solve the problem of interacting with the database by mapping domain objects to relational tables (CRUD) • Effects with NHibernate focus more on domain (EF eventually follows) • We have taught BAs about CRUD and other technology terms to cope with constraints

6. Performance of SELECT mapping over 500 iterations - POCO serialization Method Duration Remarks Hand coded (using a SqlDataReader) 47ms Dapper ExecuteMapperQuery<Post> 49ms ServiceStack.OrmLite (QueryById) 50ms PetaPoco 52ms Can be faster BLToolkit 80ms SubSonicCodingHorror 107ms NHibernate SQL 104ms Linq 2 SQL ExecuteQuery 181ms Entity framework ExecuteStoreQuery631ms http://code.google.com/p/dapper-dot-net/

7. Performance of SELECT mapping over 500 iterations - typical usage Method Duration Remarks Linq 2 SQL CompiledQuery 81ms Not super typical involves complex code NHibernate HQL 118ms Linq 2 SQL 559ms Entity framework 859ms SubSonicActiveRecord.SingleOrDefault3619ms http://code.google.com/p/dapper-dot-net/

8. Common Performance Tweaks • Indexes • De-normalizing Tables • Pinning Tables • Full Text Search • Scaling Up or Out? • Scale Up with SQL Server is cheaper than scaling out (licensing) • SQL Azure

9. Our Efforts • ORMs learning curve • 30 to 40 % of the project for saving data • In pursuit of Normalization / Normal Form • Normalization in reality isn’t normalization at all! • First step in performance: Caching, caching, and more caching • Why did we do this again?

10. Changing your perspective • Simpler design tends to be better • MVC is a successful UI pattern because it is a simpler solution than say web forms • What are we doing today? • What is caching? A key/value database • Taking advantage of caching

11. Document Database is one solution • Mongo, CouchDB, RavenDB etc… • Changing your Model to deal with performance • Is this the answer? • No silver bullet • Great to have in the tool belt • One view one model

12. Advantages of document database CraigsList – scheme change took over a month to process using MySQL Farm Question: How is the data being used and what is the data being used for? Event based architecture – events happen in a system capture the changes

13. CQRS - Command Query Separation Separating your reads from your writes At its heart is a simple notion that you can use a different model to update information than the model you use to read information. This simple notion leads to some profound consequences for the design of information – Martin Fowler

15. Writes (Commands) • Are commands • Service Bus • Handlers on different machines • Publish event to db • Publish event to cache • Publish event to search engine • Events stored in event store • A true audit log • Reply events to populate cache • Reply events to find exact state of user!

16. Events as a Storage Mechanism Save data as binary in SQL Server or JSON in document database From Jonathan Oliver’s Event Store CREATE TABLE [dbo].[Commits] ( [StreamId] [uniqueidentifier] NOT NULL, [StreamRevision] [int] NOT NULL CHECK ([StreamRevision] > 0), [Items] [tinyint] NOT NULL CHECK ([Items] > 0), [CommitId] [uniqueidentifier] NOT NULL CHECK ([CommitId] != 0x0), [CommitSequence] [int] NOT NULL CHECK ([CommitSequence] > 0), [CommitStamp] [datetime] NOT NULL, [Dispatched] [bit] NOT NULL DEFAULT (0), [Headers] [varbinary](MAX) NULL CHECK ([Headers] IS NULL OR DATALENGTH([Headers]) > 0), [Payload] [varbinary](MAX) NOT NULL CHECK (DATALENGTH([Payload]) > 0), CONSTRAINT [PK_Commits] PRIMARY KEY CLUSTERED ([StreamId], [CommitSequence]) );

17. Events as a Storage Mechanism Snapshots to lower the level of mining on multiple events From Jonathan Oliver’s Event Store CREATE TABLE [dbo].[Snapshots] ( [StreamId] [uniqueidentifier] NOT NULL, [StreamRevision] [int] NOT NULL CHECK ([StreamRevision] > 0), [Payload] [varbinary](MAX) NOT NULL CHECK (DATALENGTH([Payload]) > 0), CONSTRAINT [PK_Snapshots] PRIMARY KEY CLUSTERED ([StreamId], [StreamRevision]) );

18. Events as a Storage Mechanism Aggregate: A collection of objects that are bound together by a root entity, otherwise known as an aggregate root. The aggregate root guarantees the consistency of changes being made within the aggregate by forbidding external objects from holding references to its members. Example: When you drive a car, you do not have to worry about moving the wheels forward, making the engine combust with spark and fuel, etc.; you are simply driving the car. In this context, the car is an aggregate of several other objects and serves as the aggregate root to all of the other systems.

19. Reads (Query) • Reads on cache machines • Reads on search indexes • Sharding available on some cache solutions • Transactions available on some solutions

20. Effects • Effects on business BAs – now talking about business • Simpler development – more productivity • Need scalability add machine for reads, writes, or bus • Audit log – exactly what the user was doing • Build cache • Build development environment • Task Based UI for writes

21. Physical Parts • Service Bus • Caching • Web Server • Servers for Handlers • Database

22. Task Based User Interface • Single URL application • More complicated • Pub / Sub with Node or SignalR

23. Resources Gregg Young UdiDahan Jonathan Oliver RinatAbdullin http://distributedpodcast.com/ http://www.cqrsinfo.com/ http://martinfowler.com/bliki/CQRS.html http://www.udidahan.com/2009/12/09/clarified-cqrs/ http://abdullin.com/cqrs https://github.com/joliver/EventStore http://ncqrs.org/ http://ncqrs.codeplex.com/releases/view/43035