One of the key mistakes we make as software developers is the desire to push technology beyond the intended purpose, because it is easy, or quick to market. A perfect example of that practice may be found in some implementations of an automated ORM tool.
Let me explain that statement a little bit. Automated ORM tools make a lot of sense. There is a lot of code we have to do for an ORM that is simple boiler plate duplication. When working with a single table, it is easy to automatically generate Create, Retrieve, Update and Delete capabilities. If you delve into the relational aspects of an SQL database, you can extend that capability to filter or find parent or child instances of a record. This all works quite nicely, and saves a boatload of time.
When things begin to degrade is when you use the automated code generation to interact with multiple entities. Entity Framework is a current culprit often found in the scenario I’m going to express. When you find yourself putting together a query that would be rather complicated, even in SQL, and try to do the same through EF, the results can be quite painful, and often do not scale. They are resource hogs, and work fine as long as there is little contention. But, when the query addresses a busy table, or the number of users running your killer query increases, then the system simply comes to a halt.
How do you know if you are pushing this threshold? Well, a good place to start actually comes from some old TSQL best practices. Years ago when we started to get many tables participating in a single query we would try to find ways to simplify the logic, or break the query down into more manageable pieces. You’re lucky if you are working with a high end SQL engine when you start to get over ten tables in a query. I’ve seen queries with as many as 34 tables participating in a single EF query. Not all of the tables were returned in the results, but they were necessary for filtering or joining purposes.
My take away from this discussion is that using an ORM tool out of the box for doing your basic CRUD activities is a big boost to getting your code working. Be happy with that and move on. If you have complicated things you need to do, either figure out how to put it together using small sets of objects, or push the work back into the database, and handle the results with the extension capabilities of your ORM tool. An ORM does just provide CRUD capabilities for single tables. It can also provide connectivity, the ability to run customized code, and even the conversion of customized results into application objects.
When you use the Linq to Sql capability with too many tables, you can create super complicated queries returning a lot more data than necessary, and dragging down the performance of your SQL engine.
In short, use your ORM; don’t let it tell you what to do.
Cheers,
Ben