SqlFu 1.3.1 Released, Updated Benchmarks

By Mike on 8 February 2013

Version 1.3.1 of SqlFu brings  helpers to make some tasks easier. For writing moderately complex queries I prefer Sql (I think it's easier than messing with Linq) but for very simple queries or commands I wanted to remain in C# land. Doing inserts or updates is boring with sql and while there was an Update method in SqlFu which easied the pain, it wasn't enough because it couldn't handle trivial things like:

update Table set column=column+1

So I've included a very easy to use Update Table fluent builder

db.Update<Table>()
.Set(t=>t.Column, t=> t.Column+1)
.Set(t=>t.OtherColumn, value)
.Where(t=>t.Id==someid)
.Execute()

Simple enough and it should handle 99% of usages of single table updating.

Other helpers added

  • retrieve one row using strongly typed criteria
    db.Get<Article>(a=>a.Id==12)
  • get column value
    db.GetColumnValue<Article,string>(a=>a.Title,a.Id==12)
  • counting rows from table
    db.Count<Article>(a=>a.PostedAt.Year==2013)
  • checking if a table has rows
    db.HasAnyRows<Article>(a=>a.Id==entityId);
  • delete from table
    db.DeleteFrom<Article>(a=>a.Id=entityId);
  • some table management utils
    db.Drop<Article>();
    if (!db.TableExists<Article>()) { //do create table }

Nothing revolutionary but small things to increase productivity.

Benchmarks

I thought it is a good time to  update the old benchmarks (published in May 2012) comparing SqlFu to other micro Orms. I've dropped FluentData and revised the queries as things were not that equal (some dapper queries included an order by, Massive query wasn't iterated) and I've updated all the other micro orm to their latest nuget versions.

Executing scenario: FetchSingleEntity
-----------------------------------
Massive doesn't support the action. not explicit type support
Dapper query entity - 500 iterations executed in 65,7408 ms
InsightDatabase - 500 iterations executed in 66,2373 ms
OrmLite - 500 iterations executed in 66,4864 ms
SqlFu FirstOrDefault - 500 iterations executed in 67,734 ms
SqlFu Get - 500 iterations executed in 71,1035 ms
PetaPoco entity - 500 iterations executed in 72,9989 ms
Dapper get entity - 500 iterations executed in 97,0822 ms

Executing scenario: FetchSingleDynamicEntity
-----------------------------------
Dapper query entitty dynamic - 500 iterations executed in 66,4755 ms
InsightDatabase - 500 iterations executed in 67,662 ms
SqlFu dynamic - 500 iterations executed in 69,6545 ms
PetaPoco dynamic - 500 iterations executed in 71,9594 ms
OrmLite - 500 iterations executed in 72,5374 ms
Massive - 500 iterations executed in 107,5593 ms

Executing scenario: QueryTop10
-----------------------------------
Massive doesn't support the action. not explicit type support
InsightDatabase - 500 iterations executed in 90,5127 ms
Dapper  - 500 iterations executed in 91,2931 ms
PetaPoco - 500 iterations executed in 94,7761 ms
SqlFu - 500 iterations executed in 96,9167 ms
OrmLite - 500 iterations executed in 111,007 ms

Executing scenario: QueryTop10Dynamic
-----------------------------------
OrmLite - 500 iterations executed in 92,5813 ms
Dapper  - 500 iterations executed in 100,7141 ms
InsightDatabase - 500 iterations executed in 106,0291 ms
PetaPoco dynamic - 500 iterations executed in 114,4857 ms
SqlFu - 500 iterations executed in 127,215 ms
Massive - 500 iterations executed in 244,1929 ms

Executing scenario: PagedQuery_Skip0_Take10
-----------------------------------
Dapper  doesn't support the action. No implicit pagination support
InsightDatabase doesn't support the action. No implicit pagination support
PetaPoco - 500 iterations executed in 153,303 ms
SqlFu - 500 iterations executed in 173,2594 ms
OrmLite - 500 iterations executed in 191,4013 ms
Massive - 500 iterations executed in 247,4856 ms

Executing scenario: ExecuteScalar
-----------------------------------
Dapper scalar int - 500 iterations executed in 59,7867 ms
InsightDatabase - 500 iterations executed in 61,0439 ms
SqlFu scalar int - 500 iterations executed in 61,789 ms
PetaPoco int - 500 iterations executed in 63,7753 ms
OrmLite - 500 iterations executed in 68,7653 ms
SqlFu get scalar expression based - 500 iterations executed in 71,3949 ms
Massive - 500 iterations executed in 98,7458 ms

Executing scenario: MultiPocoMapping
-----------------------------------
Massive doesn't support the action. Specified method is not supported.
OrmLite doesn't support the action. Suports only its own specific source format
Dapper  - 500 iterations executed in 67,3771 ms
InsightDatabase - 500 iterations executed in 67,3924 ms
SqlFu - 500 iterations executed in 73,1912 ms
PetaPoco - 500 iterations executed in 74,4355 ms

Executing scenario: Inserts
-----------------------------------
massive doesn't support the action. Couldn't figure how to insert pocos with auto increment id
InsightDatabase doesn't support the action. Specified method is not supported.
SqlFu - 500 iterations executed in 103,4232 ms
PetaPoco - 500 iterations executed in 116,122 ms
Dapper - 500 iterations executed in 116,6094 ms
OrmLite - 500 iterations executed in 135,619 ms

Executing scenario: Updates
-----------------------------------
InsightDatabase doesn't support the action. Specified method is not supported.
OrmLite - 500 iterations executed in 75,0417 ms
SqlFu - 500 iterations executed in 81,5845 ms
PetaPoco - 500 iterations executed in 84,1997 ms
Dapper  - 500 iterations executed in 129,9522 ms
massive - 500 iterations executed in 183,4292 ms

It seems that performance is not that much of a concern when using a micro Orm. A few of miliseconds difference when dealing with 500 queries is negligible. Pretty much any micro Orm is fast these days so what will make you prefer one over the other is their feature set (did you know SqlFu has integrated support for database migrations ?) and how comfortable you feel using them. And even if all seem to have similar core features, they do have different (not so obvious) strengths.

Introducing SqlFu 1.2.0

By Mike on 28 December 2012

1.2.0 is a milestone for SqlFu as it brings new features to the table (besides the usual bug fixing and minor improvements)

  • Database Tools
    Common table management got much easier. You can rename, drop, truncate or check table existence using C# only. Simple but very useful. 
  • Database Migrations
    You know the fun when you have to upgrade db schema, even more so when you have to support more than one database engine. Well, things will be much more easier with SqlFu. Just define migration tasks, put an attribute on them and let the Automatic Migrator keep track of everything. OF course, you still have to write the actually SQl (or maybe not - check the next feature :) ) to modify the db structure, but the whole logistics is taken care of. You just concentrate on the actual Sql stuff.
  • Fluent DDL Builder
    Or 'How to write create/alter table scripts using only C#' . Supported databases are SqlServer, Mysql, Postgres and Sqlite (only partially). You can write common Sql as well as customize the script for each supported db. All C# with code completition.
  • Stored Procedure support
    Support for input and output parameters.

You can read about these features in detail on the SqlFu Wiki

Battle of Micro-ORMs: Benchmarks and Observations

By Mike on 21 May 2012

Update: See this post for newer benchmarks

Since I did a bit of benchmarking for my SqlFu micro-orm, I thought how about I'd do a 'proper'  job and benchmark more micro-orms and more usage scenarios. I've included all the micro-orms I found via Nuget or I knew about, but I didn't include any of the heavy ORMs as they're aimed at a different target .


The participants:
- SqlFu
- Dapper.net with extensions
- PetaPoco
- Massive
- FluentData
- InsightDatabase
- ServiceStack.OrmLite

A few observations

Because every orm has its quirks, I had to remove enum handling and avoid returning null when the result is a nullable value.


PetaPoco
 -  doesn't like converting from tinyint to Enum when executing scalar.
 -  doesn't support mapping from string to Enum
 -  doesn't suport mapping dbnull to nullable when executing scalar
 -  handles up to 5 pocos and requires to put the column names in the same order as the properties of the result poco. A bit awkward but it works.


Dapper
- it doesn't support byte to enum when mapping result
- the multi mapping kinda works or I don't know how to write the sql in order for dapper to properly map it. Anyway, this is an issue for anyone using it: hit or miss depending on the sql.


Massive
- it's strange to work with, perhaps because I am not used to the Active Record pattern. I consider it a bit unintuitive to use compared to the others.
- it can't automatically map scalars to a type, you have to do the conversion yourself.
- couldn't figure how to do a proper insert


FluentData
- quite intuitive, I like using it
- doesn't automatically ignore properties not found in query result
- doesn't support dbnull to nullable when executing scalar
- doesn't support  byte to enum
- doesn't handle Inserts properly - SqlServer complains about the generated sql.


ServiceStack OrmLite
- it does support a form of multi mapping but it is specific to OrmLite.
- doesn't support Enum to byte (it's safe to assume it requires enums to be persisted as varchar)


InsightDatabase
- built mainly for stored procedures usage, annoying if you're using just sql
- interesting features (like async queries) which are outside of this benchmark

SqlFu
- Handles Enums properly and it doesn't choke on nullables. It's also the most handsome of them all.


Scenarios

Each use case is run 500 times, after 10 times of warm up. When the micro-orm doesn't support a feature out of the box (i.e you'd have to do it manually), I put it as Not supported action. Only suported actions are timed. I've run all the benchmarks a couple of times in order to get relevant results.

While there is no clear winner, especially since you have to consider all the features a package offers, you can clearly see a trend on who's in the top and who's lagging behind.

Executing scenario: FetchSingleEntity
-----------------------------------
Massive doesn't support the action. not explicit type support
Dapper entity - 500 iterations executed in 76,5396 ms
SqlFu Get - 500 iterations executed in 77,6155 ms
PetaPoco entity - 500 iterations executed in 78,8191 ms
SqlFu FirstOrDefault - 500 iterations executed in 80,1971 ms
OrmLite - 500 iterations executed in 80,2547 ms
InsightDatabase - 500 iterations executed in 160,2398 ms
FluentData - 500 iterations executed in 206,3038 ms

Executing scenario: FetchSingleDynamicEntity
-----------------------------------
SqlFu dynamic - 500 iterations executed in 74,6951 ms
Dapper dynamic - 500 iterations executed in 80,3824 ms
PetaPoco dynamic - 500 iterations executed in 83,6375 ms
Massive - 500 iterations executed in 130,8806 ms
OrmLite - 500 iterations executed in 146,8522 ms
InsightDatabase - 500 iterations executed in 176,2635 ms
FluentData - 500 iterations executed in 280,377 ms

Executing scenario: QueryTop10
-----------------------------------
Massive doesn't support the action. not explicit type support
SqlFu - 500 iterations executed in 90,7809 ms
PetaPoco - 500 iterations executed in 106,1067 ms
Dapper  - 500 iterations executed in 115,7401 ms
OrmLite - 500 iterations executed in 128,891 ms
InsightDatabase - 500 iterations executed in 203,417 ms
FluentData - 500 iterations executed in 445,0844 ms


Executing scenario: QueryTop10Dynamic
-----------------------------------
Dapper  - 500 iterations executed in 99,1737 ms
OrmLite - 500 iterations executed in 108,785 ms
Massive - 500 iterations executed in 116,3599 ms
SqlFu - 500 iterations executed in 116,4271 ms
PetaPoco dynamic - 500 iterations executed in 125,1672 ms
InsightDatabase - 500 iterations executed in 192,129 ms
FluentData - 500 iterations executed in 198,8402 ms

Executing scenario: PagedQuery_Skip0_Take10
-----------------------------------
Dapper  doesn't support the action. No implicit pagination support
FluentData doesn't support the action. No implicit pagination support
OrmLite doesn't support the action. No implicit pagination support
InsightDatabase doesn't support the action. No implicit pagination support
Massive - 500 iterations executed in 115,4747 ms
SqlFu - 500 iterations executed in 199,1776 ms
PetaPoco - 500 iterations executed in 292,4833 ms

Executing scenario: ExecuteScalar
-----------------------------------
OrmLite - 500 iterations executed in 59,7584 ms
InsightDatabase - 500 iterations executed in 64,2303 ms
PetaPoco int - 500 iterations executed in 66,3844 ms
SqlFu scalar int - 500 iterations executed in 77,7015 ms
Dapper scalar int - 500 iterations executed in 83,0212 ms
Massive - 500 iterations executed in 130,4026 ms
FluentData - 500 iterations executed in 157,6623 ms

Executing scenario: MultiPocoMapping
-----------------------------------
Massive doesn't support the action. Specified method is not supported.
OrmLite doesn't support the action. Suports only its own specific source format
InsightDatabase doesn't support the action. No implicit multi mapping support
SqlFu - 500 iterations executed in 75,7716 ms
Dapper  - 500 iterations executed in 88,3029 ms
PetaPoco - 500 iterations executed in 99,3068 ms
FluentData - 500 iterations executed in 236,9429 ms

Executing scenario: Inserts
-----------------------------------
Massive doesn't support the action. Couldn't figure how to insert pocos with auto increment id
FluentData doesn't support the action. Specified method is not supported.
InsightDatabase doesn't support the action. Specified method is not supported.
SqlFu - 500 iterations executed in 112,067 ms
PetaPoco - 500 iterations executed in 126,0432 ms
OrmLite - 500 iterations executed in 128,8205 ms
Dapper - 500 iterations executed in 264,1543 ms

Executing scenario: Updates
-----------------------------------
InsightDatabase doesn't support the action. Specified method is not supported.
SqlFu - 500 iterations executed in 75,1604 ms
PetaPoco - 500 iterations executed in 80,522 ms
Dapper  - 500 iterations executed in 102,6581 ms
OrmLite - 500 iterations executed in 195,2351 ms
massive - 500 iterations executed in 202,5537 ms
FluentData - 500 iterations executed in 249,3975 ms


You can find the benchmark project on GitHub (part of the SqlFu repository).

SqlFu My Versatile Micro-Orm

By Mike on 19 May 2012

I can already hear you: "What, another micro-orm, do we really need yet ANOTHER ONE?". Yes and believe me I've tried! I've searched for a micro-orm that will have those 2 features I really wanted. OK, at least one of them: multi poco mapping by convention. I wanted that for my view models. It's really tedious to write glue code or to mutate my models so they play nice. And I found it! FluentData was doing  multi poco mapping similar to what EF does: just name the columns [Property]_[Property] and the automapper will take care of it.

Such bliss! Until I've benchmarked it... Then everything shattered. FluentData is SLOW (well compared to PetaPoco - my micro-orm of choice) and features some weird (IMO of course) design choices. Plus, I couldn't find a way how to do paged queries, but the slowness part was the deal killer. On the other hand it did allow you to use your very own mapper when you wanted - I liked that , but it was also the only way to be fast.


What to do, what to do? I've thought well, since PetaPoco does suport a way to map multiples poco, I can add that functionality to it. And I did it, as a proof of concept but it was slow (reflection based). And my other pet peeve: I couldn't have both multi poco mapping (MPM) AND pagination in the same query. And the MPM required to put the column names in a certain order and it would support only up to 5 pocos (same as Dapper.net I think).


 Long story short, I've decided to develop my own micro-orm -SqlFu - which will be fast, it will support MPM automatically, no other convention or setup required but the columns names to contain the properties separated by '_' ,  pagination no matter if it's a simple object mapping or a MPM. Basically, I wanted a library that will do automatically the basic things and to be versatile when needed. It really puzzles me why there aren't many options like it already. I mean, in any web scenario you will have pagination and composed view models, why should I write that boring paging syntax (no fun on SqlServer) or poco mapping?! Yes, Automapper can be a solution but it is yet an abstraction layer: get poco from db ,then map it to another poco which will contain pretty much the same properties albeit in a an hierarchy. I think this multipoco mapping feature should be default in any self-respecting micro-orm. This and pagination.


 One week later, I've got SqlFu ready and I must say I'm really proud of it. It does all the stuff at top speed (as fast as peta poco and dapper). Only it has more features and it's very easy to plug in your own mapping code for those edge cases. You can find the source and the docs, along with some benchmarks on GitHub.