Why Your DBA Hates Your ORM
Podcast: Play in new window | Download (54.4MB) | Embed
Subscribe: Apple Podcasts | Spotify | Email | RSS | More
“Why does your database administrator (DBA) hate your object relational mapping (ORM)?”
Many people still perform a lot of direct database access. SQL can be painful to use around certain operations like iteration. The idea behind an ORM is that a developer can take a database object and map those columns into an object that the application is using. It does all the calls, loads up the objects, handles nulls and errors, etc.
This enables you to programmatically interact with objects rather than dealing with SQL. Typically an ORM integrates a unit of work pattern that allows you to modify multiple objects then push the changes back to the database as a unit. This returns a chunk of data known as an aggregate root. The ORM pulls the data from the database and allows the developer to edit the data as an object in the code then returns it to the database. The ORM figures out what SQL to generate to send the data back to the database. This is called a unit of work pattern. It is effectively a transaction as it either all goes to the database or none.
They tend to protect against SQL injection and other attacks to various degrees. It also protects against coders not wanting to know SQL. It is a different mindset when writing SQL and some people are better at quickly switching mindsets between object-oriented programming and SQL coding. ORMs make coding easier because the developer doesn’t have to switch between their preferred language and SQL.
“Database administrators don’t like ORMs in general. These are some things to keep in mind when talking with your DBA.”
Episode Breakdown
-
20:50 Lack of Abstraction for the DBA
“The ORM doesn’t always follow the model”
ORMs directly access tables for both reads and writes. As a result the DBA has a much harder time tuning performance and enforcing constraints. Frequently ORMs treat access to views, stored procedures, and other constructs as second class citizens.
The problem occurs when the ORM is deeply tied to the table and it needs to read but another developer is writing to the table. Optimization becomes an issue for the DBA because to optimize for one the other suffers. However if it is reading from a view the DBA can create a view for the developer needing to read and then add all the writes as a unit after business hours.
-
27:03 Chatty Calls and Long Transactions
Most ORMs hide the SQL they generate making it easy to get code that makes a large number of small calls to the database instead of one large call. They can also create code that holds a transaction open for an extended time period. Depending on the length of the transaction this can result in downstream effects on the performance of the system. Unfortunately the DBA tends to get blamed for performance issues with the database when in reality it is the code generated byt he ORM.
-
31:20 Visibility Into the Actual Code Being Used is Frequently Limited
“If the way to optimize a database call performance for a webpage starts with ‘Open up SQL Profiler’ then you have a problem.
SQL code generated by an ORM is not built for readability and therefore tends to be dense or all on one line. Performance problems with the database typically do not appear until it is in production and under heavy load. This makes tracking the source of the problem difficult and many times the DBA gets blamed for the problem.
-
33:58 Lowest Common Denominator
Object Relational Mappers are built to abstract the database from the code. Because of this they are not designed for efficiency with a single database but to be able to work with many different databases. Inefficient calls become the problem rather than slow performance of the database. Many of the basic SQL constructs are similarly accessed between databases but have different performance characteristics. Some features (table-value parameters, hierarchy ids, geospatial data types, etc.) however are massively different or not present across different databases meaning a lack of support for those features.
-
39:30 Massive Network Calls That are Non-Obvious
By abstracting the database ORMs make it less obvious when a developer is loading two entire tables into memory and joining and filtering the results there instead of doing so on the database. Some ORMs simply log a set of updates as they happen then make separate calls for each change. This works fine until something makes the same change multiple times in a row. This can lead to cascading system failure if not handled properly.
-
44:28 Database Misuse for “Features”
“The big one is search. ‘Let’s just make it where we can arbitrarily search anything.’ That sounds really good on paper until you get under load and you don’t have indexes.”
The abstraction of the database makes it easy for the developers to act as if the production transactional database is a data warehouse allowing arbitrary queries that cannot be predicted. If the ORM allows lazy loading of objects that are down-graph from the aggregate root it is very easy to load massive amounts of data just to get a record count.
-
46:21 Foreign Keys and Constraints
“If you’re using an ORM and say, ‘I don’t need foreign key constraints’ I immediately classify you as a junior dev at best”
ORMs make it easier for front end developers to ignore proper constraints between database tables as they can enforce it in the code. It works fine until someone else edits the data. They abstract to the table level making it difficult to generate SELECT N+1 problems when trying to bring back an entire object graph.
-
49:15 What It All Boils Down To
“The database is not part of your app! It is a microservice that you consume.”
When working with ORMs it is easy to forget that the database is not a part of your app but a microservice that happens to be written in SQL. Watch the performace characteristics of your code and know the ORM as well as you know SQL. Make sure you have a way to get to the actual SQL generated and know how to optimize it.
“ORMs are not a replacement for not knowing SQL”
Remember there is a service boundry, security bourndry, and a network call involved when using the ORM. Understand the process of the ORM you are using before writing the code.
IoTease: Product
WISP Radio Powered Sensors
WISP (Wireless Identification and Sensing Platform) gets it’s power from radio waves. It gathers eneregy from an RFID reader. It is then able to generate enough electricity to perform low level processes like tracking sensor information. Research into self-powered sensors has been going on for a while but recent developments have made the tech practical for IoT applications at least.
Also they can be reprogrammed wirelessly so sending out updates to a network of sensors is possible. The article even mentions putting them inside a person’s body. Future iterations of this technology could produce some interesting breakthroughs in medical technology.
Tricks of the Trade
When you have a piece of an application or set of connected services at some point there is going to be a long running process. You may have service level agreements about how they return data or how quickly they return the data. When in a larger team you can get a “tragedy of the commons” that can cause problems for the other developers on your team. Be cognizent of the boundries of what your service does and of the stones you are throwing over the wall.