Database Reference
In-Depth Information
You should always remember to specify non-unicode attributes in the model to avoid generating
nvarchar
parameters when referencing
varchar
columns. For updateable entities, you should specify a primary key and the
length of the properties/data columns, and you should also make sure that deletion operations do not introduce
additional overhead by pre-loading the objects.
Unfortunately, the generation of easy-to-understand SQL code has never been a top priority for the framework,
which has been designed to simplify and speed up client application development. Generated SQL statements are
massive and very hard to read. This makes query analysis and optimization difficult and time consuming.
It is also worth mentioning that SQL generators are the hidden part of frameworks and can change over time. It is
important to re-evaluate the quality of SQL code when upgrading to a new version of the framework.
Summary
The architecture of a modern complex system should utilize a layered approach, separating subsystems from each
other. This separation improves the supportability of the code, simplifies refactoring, and allows multiple developers
to work on the project in parallel.
The data access layer contains the code that works with the database backend. Various approaches can be
used for data access layer implementation. Each approach has both advantages and disadvantages that need to be
evaluated during the system architecture stage.
The boundaries of the data access layer can cross client code and utilize database views and stored procedures
when appropriate. It is advantageous to have dedicated database professionals on the team who can take care of
designing and developing data access code. This will help to avoid costly mistakes and code refactoring at a very late
development stage or after deployment to production.
ORM Frameworks and Code Generators have become more and more popular. While they simplify and speed up
client application development, they can introduce performance issues in systems due to the suboptimal SQL code
that they generate. You should perform an analysis of framework functional and generated SQL code when choosing a
framework for a system.
SQL Server client libraries utilize connection pooling to keep SQL Server connections open. Client applications
reuse connections from the pool, which improves system performance. It is important to release connections back to
the pool as fast as possible by closing or disposing of them in the client applications.
Reusing connections from the pool does not reset the transaction isolation level set by the previous session. You
should reset the transaction isolation level before returning a connection to the pool, or after you open a connection.
