##Database access layer benchmark
This is reference benchmark for Revenj framework data layer.
Variety of models are tested, from small simple objects, to very complex large objects.
###Models
###Testing assumptions
- loading of whole aggregate
- simple model is a single table data source
- standard model represents a parent/child relationship. Implementation is free to choose a single table or two tables
- complex model represents a parent/child/detail relationship. Implementation is free to choose a single table or three tables
- multiple test types:
- batch insert/update (single transaction)
- insert/update in a loop (multiple transactions)
- primary key lookups
- optimized search (known in advance)
- LINQ search (runtime conversion)
- report (loading of several data sources)
###Libraries
- Npgsql (2.2.4.3) - used to implement manual SQL approach; talks to Postgres using binary protocol
- Revenj (1.2.0) - implemented both as a "standard" relational approach with aggregation in a single object and as a "NoSQL" object oriented approach; Uses customized old Npgsql which talks to Postgres using text protocol
###Single table test
Small table which should reflect simple database interaction. Npgsql does not implement "true" batch inserts/updates, but rather reuses the same command.
###Parent/child test
Standard pattern which is implemented as two tables in a relational database, but can be implemented as a single table in an object-relational database. Npgsql can't really be used for "NoSQL" approach since it would require a lot of complex code.
###Parent/child/child test
Sometimes complex nested tables are required. Due to multiple roundtrips, bulk insert/update performs really poorly. Npgsql code is starting to be really complex and it's performing good only on single lookup test. Revenj is faster in all tests and bulk update is faster in "Relational" tests (since object was only partly updated).
###Reproducing results
Run GatherResults.exe by pointing it to an Postgres instance
Individual tests can be run as:
- DalBenchmark.exe (example: DALBenchmark.exe Npgsql Simple 10000)
If you are interested in changing the models, then you can:
- install Visual studio plugin: DDD for DSL
- or use dsl-clc
###Results:
Full results are available in Excel spreadsheet.
Bench was run on Windows7 with Postgres 9.3 locally installed.
###Conclusions:
- Manual coding of SQL and object materialization is not the fastest way to talk to the database
- Complex models will benefit from "NoSQL" approach to modeling
- Revenj/DSL-Platform has better performance then using SQL with data readers
- DSL-Platform compiler will create a lot of boilerplate in the database, but it will also put that boilerplate to use