Using ORM tools with stored procedures has security, performance and maintenance advantages over using ORM tools with direct access to tables.
In my view, ORM tools are great, but plugging them directly to the underlying tables has security, performance and manageability overheads.
The best approach I have seen is to use stored procedures as an abstraction layer between the ORM and the database tables. This will protect the code from any schema changes and allows the DBA to tighten security on the procedure level and fine tune the procedures without impacting the underlying code. In contrast, using ORM directly with tables increases coupling between the database and code, any schema change will require change in the ORM and the code need to be compiled and redeployed.
It is possible to use Views with ORM to provide some level of abstraction, and security, but views have many limitation such as the inability to order the result in a view and views can’t take parameters are two of the main shortcoming.
- Grant Execute permission to stored procedure only, no need to grant permission on the database table level.
- Using stored procedures with correct parameters protect against sql injection. Most decent ORM tools use sp_executesql with parameters which protects against sql injection.
- Centralised access to the database.
- Query tuning is much easier with stored procedures. Fine tuning is restricted or unavailable for some table hint or index hints when using ORM.
- Complex queries with multiple joins tend to be cumbersome and it requires seasoned developers to get the ORM mapping correct. This can cause performance issues.
- Stored procedures tend to have good cache plan; some ORM tools cause cache bloating due to the way the dynamic sql is constructed. An example is when the ORM passes a different varchar(x) length depending on the length of the parameter (x) which causes cache bloating. Another issue that causes performance degradation with some ORM tools is when they treat string as Nvarchar which causes implicit conversion if the underlying column type is not nvarchar. This can cause considerable performance issues with large tables (see post).
- With stored procedures less data is passed over the wire, i.e. just the procedure name not the full query string.
- Most ORMs return a full object even if only one attribute is needed, for example to lookup a customer name by ID, it will return the customer object with all its attributes, and if not careful it might also load all its children (e.g. all the orders for the customer..etc).
- Stored procedures can be tested as a single unit without the need to compile and deploy any code.
- It might be quicker to get the code out of the door with ORM, but the ongoing maintenance cost is much higher.
- The profiler output of a statement submitted by ORM is unreadable compared to stored procedure execution.
The last point is that access to data should be designed by DBA who has knowledge of the environment and the indexes, ORM encourages top down design which does not take into account the efficiency in which data need to be accessed. The applications designed with this approach tend to work well in development but as soon as they are deployed to production with a couple of million records, they fail to perform.
And finally, most databases outlive the applications by many years, so it pays off to have a robust data abstraction layer that consists of a set of stored procedures which can be used as a gateway to access your most valuable asset which is your data.