ASP.NET Tips #11 - ORM tips for developers

More and more people are using Object to Relational Mapping (ORM) tools to jump the divide between application code that is object oriented and a database that is storing information in a relational manner. These tools are excellent and radically improve development speed. But, there are a few 'gotchas' to know about.

1. Avoid following the 'Hello World' examples provided with your ORM tool that turns it into an Object to Object Mapping. Database storage is not the same as objects for a reason. You should still have a relational storage design within a relational storage engine such as SQL Server.
2. Parameterized queries are exactly the same as stored procedures in terms of performance and memory management. Since most ORM tools can use either stored procedures or parameterized queries, be sure you’re coding to these constructs and not hard-coding values into your T-SQL queries.
3. Create, Read, Update, and Delete (CRUD) queries can all be generated from the ORM tool without the need for intervention. But, the Read queries generated are frequently very inefficient. Consider writing a stored procedure for complex Read queries.
4. Since the code generated from the ORM can frequently be ad hoc, ensure that the SQL Server instance has 'Optimize for Ad Hoc' enabled. This will store a plan stub in memory the first time a query is passed, rather than storing a full plan. This can help with memory management.
5. Be sure your code is generating a parameter size equivalent to the data type defined within table in the database. Some ORM tools size the parameter to the size of the value passed. This can lead to serious performance problems.
6. Always profile your ORM database hits with SQL Profiler during development. ORMs get away from you very quickly. Before you know it, you've run a query 2000 times in a loop, when you could have retrieved all your data with a single database hit.
7. Watch out for lazy loading in ORMs. You shouldn't lazy load any entities that could be retrieved with a single database hit.
8. Implement different database queries in different contexts. In the API and on a webpage, you'll inevitably require different entity properties, so don't load things you don't need just because it's convenient to reuse a query.
9. Using compiled queries on top of ObjectContext or DBContext can significantly improve application performance, because you save much of the time that Entity Framework would have to spend compiling your query to SQL.