I’ve used two different ORM mappers in sites with thousands of queries executing every second and both ORMs had almost no noticeable effect on performance.

Take a look at this post about the Subsonic ORM:
http://blog.wekeroad.com/blog/subsonic-scaling/

“I’ve just pumped a million orders (and items – 2 million records total) into my SQLExpress Northwind database and it took just about 22 minutes (which is about 1500 records a second):”

Here’s a link to a performance test of NHibernate:
http://www.iamnotmyself.com/2008/07/02/NHibernateTestingThePerformanceUrbanLegend.aspx

1000 inserts via Stored Procedure was only 175ms faster than NHibernate. That means that NHibernate added .17 ms to the overhead of inserting a record.

So when you say “several hundred-fold improvement in performance, on average.” for adding items into the database the problem likely due to a specific problem with your code.

Overall, popular ORM packages have a negligible effect on performance and its unfair to say all ORM performance is inadequate based on these numbers.

I’d encourage you to write at least one or two database applications before you start using an ORM. While it can be a bit more tedious writing the queries by hand and debugging them, it is a worthwhile learning experience. Once the learning is done and you’re just repeating the same busywork you’ve already done a number of times before, then the ORM makes a lot more sense.

I’ve become sceptical of ORMs and abstraction layers. Great in theory but I’ve not yet seen an implementation that doesn’t suck either for maintenance coders or for performance, or both.

I used to be big on following principles when coding. After several years of contracting, working on existing code bases, I’m now in favour of practicality. I rate everything now based on the two criteria I mentioned above:

1) Is it easy to maintain?
2) Is the performance adequate?

Almost all the code I’ve come across fails one or both of these two criteria.

The code-base I’m working on at the moment is a case in point. I need to change the way the data is stored in the database. First step, reverse engineering the existing code to find out how it saves the data. 10 – 12 nested methods between clicking a button on the UI and something happening in the database (not to mention side calls to read properties or get specific data). And slow as a dog. All the business logic in the middleware layer. Great idea but it involves 3 database writes and 4 reads to add an item to an order. Maximum of 99 items can be added at a time. I make that up to 693 database accesses to perform a single operation (adding a bunch of items to an order). It took two days to rewrite the code to perform a single database update for all items. Still using a data access layer but moving the business logic to a stored procedure in the database. No good from a purist’s point of view but all the business logic is in one place (the stored proc) and we got a several hundred-fold improvement in performance, on average.

Other than that, you’re mostly right. I didn’t look at Firebird because it was simply off my radar, but I did take a look at SqlLite. I didn’t actually use it, but from what I read on their site, it just didn’t seem suitable:

“Situations Where Another RDBMS May Work Better

Client/Server Applications

A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

High Concurrency

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database.”

Source: http://www.sqlite.org/whentouse.html

That said, after all the feedback I’ve received from this post, I’m definitely going to take a closer look at nHibernate. I also like SQL Server Compact, but I haven’t looked close enough at it to see if it’s suitable.

Not using an ORM mapper, or getting to learn one, is going to waste your time and prevent you from focusing on leaning C#. Without an ORM mapper your going to have to write every single query that accesses your database by hand and then debug those queries when you inevitably make a typo.

The real “everything is a hammer” problem is probably your choice to use Access as a backend DB “because you know it”. This is what the saying is used for. Because you know Access every persistence problem your have your going to try and fix with Access.

Try branching out into something like Firebird or SqlLite instead of what you know if you really can’t get connected to your company’s sql servers.

Instead of putting the common database lookup routines in the base class, treat them as a separate “Database abstraction layer”. This way, you won’t have to modify any of the DAO classes if you ever change out your database. You just write a new Database abstraction layer with the same API as the old one and you’re done.

http://en.wikipedia.org/wiki/Database_abstraction_layer

Yes, this splits your application classes into 3 layers that sit on top of the database, but the extra flexibility and modularity are worth it.

This looks like an interesting site, but I think I’ll be alright keeping my repository on my laptop for now since it’s the only place I do any development work (for now, at least).

As the project gets bigger, I’ll probably be posting more and more code looking for feedback. Perhaps when that time comes I’ll look at hosting the repository so my readership (both of you!) can easily access the whole project.