Database Reference
In-Depth Information
Code Generators and ORM Frameworks
Code Generators and ORM (Object-Relational Mapping) Frameworks have never been particularly popular in the
community of database professionals. Those frameworks were always targeted at developers, helping them to speed
up the development process. ORM Frameworks also offered the ability to develop database-agnostic systems (on
paper, at least), where you can transparently switch an application to a different database backend.
DataBaSe-aGNOStIC SYSteMS: IS It reaLLY WOrth the eFFOrt?
the decision of making a system database-agnostic is very dangerous. the ability to support different database
backends looks great in marketing materials, and it can open up business opportunities for independent Software
Vendors. Developers often argue that such benefits come for free with an orM framework. that is not true,
however. in fact, these benefits come at a high cost.
the ability to support multiple database backends requires you to work based on the lowest common
denominator and use only features and data types that are supported in all database platforms and in the
framework itself. You will not be able to take advantage of server-specific features, which can simplify and/or
improve performance of some of the tasks. You will also need to test every supported database backend, which
can greatly increase development times and cost.
Moreover, different database servers require different approaches during system optimization and performance
tuning. Development and product Support teams should have engineers with deep knowledge of the all supported
backends to address those challenges.
obviously, you can detect what database backend is in use and add backend-specific code to support some of the
use-cases. however, this approach requires extra effort, and it increases development and support costs.
Finally, it is not uncommon to see examples of database-agnostic systems when most, if not all, installations
utilize just a single backend. Customers tend to deploy a system to database servers, which are cheaper to
acquire and maintain.
In the past, ORM frameworks did not generate high-quality SQL code. They did not handle query
parameterization correctly nor generate efficient queries. However, they have become more mature and have begun
to address some of the issues over the years.
The generated SQL code is still not perfect, and it greatly depends on the version of framework, the quality of
entity model, and the developers' experience and skills. Nevertheless, frameworks have become more and more
popular nowadays as hardware costs continue to decline and the time to market and development costs are the most
important factors in projects.
Frameworks allow application developers to work within familiar object-oriented models without diving deep
into the database implementation and T-SQL code. Moreover, they significantly reduce code-refactoring time and
make it possible to catch a large number of errors at the compilation stage.
Code generators and ORM frameworks change performance tuning and optimization workflow. It is very
difficult, if even possible, to predict how SQL statements are generated, which often masks performance issues until
the application is deployed to production.
There is very little that can be done to avoid it. Obviously, thorough testing can help to pinpoint inefficiencies;
however, agile development teams rarely reserve enough time for that. In a large number of cases, the only available
option is monitoring systems after they are deployed to production, refactoring problematic queries on the fly.
■
Note
We will discuss how to detect inefficient queries in Chapter 27, “System troubleshooting.”
