Database Reference
In-Depth Information
Choosing the right framework is one of the most important decisions during the system design stage.
Unfortunately, developers often make this decision by themselves without any database professional involvement.
This is a very bad practice, which can lead to very serious performance issues if poor decisions are made.
You should always research how the framework works with the database backend. You need to read the
documentation and online resources as well as run tests that capture and analyze SQL statements generated by the
frameworks. The set of important questions to ask are outlined below.
What database platform features are
not
supported by the framework?
As already mentioned, cross-platform support
limits the feature set by using the lowest-common denominator principle. In some cases, missing platform-specific
features could be a deal breaker. For example, neither of the major frameworks supports table-valued parameters,
which could be a big limitation on systems that need to process and update large batches of data. The same is true for
some SQL Server data types. Not all frameworks support
sql_variant
,
date2
,
time2
,
datetime2
, and
datetimeoffset
.
Does the framework allow you to run custom queries and stored procedures?
Even though auto-generated code can
be good enough in most cases, you should be able to execute queries and stored procedures from within the framework.
This will allow you to address critical performance issues if the quality of auto-generated code is not sufficient.
How does the framework work with database transactions?
At a bare minimum, you should be able to explicitly
control transaction scope and isolation levels.
How does the framework work with parameters?
Inefficient parameterization leads to unnecessary
recompilations, increases the size of the plan cache, and negatively affects SQL Server performance. Frameworks,
which do not parameterize queries, should never be used. Fortunately, that is rarely the case nowadays.
You should research, however, how the framework chooses parameter data types and length, especially in the
case of strings. You should be able to control what data type to choose:
varchars
or
nvarchars
. Data length is another
important factor. Unfortunately, there are plenty of frameworks that generate it based on the actual value rather than
the column size. For example,
City=N'New York'
would generate
nvarchar(8)
and
City=N'Paris'
would generate
nvarchar(5)
parameters. Queries with those parameters will be recompiled and have plans cached separately from
each other. Obviously, it is better to choose a framework that can generate parameter length based on the actual size
of the data column or allow you to specify it.
Does the framework allow you to load a subset of the attributes in
the
Entity Object?
As we discussed in Chapter 1,
selecting unnecessary columns adds extra load to SQL Server and makes query optimization more complicated,
especially when data is stored in
ROW_OVERFLOW
or
LOB
pages. It is beneficial to use frameworks that support the partial
load of entity objects to avoid such problems. As a workaround, you can create separate entity objects for different
use-cases. For example,
Customer
class can include all customer attributes and
CustomerInfo
class can provide small
set of attributes to display in grid control.
The above list is by no means complete; however, it can work as a good starting point. Let's look at Microsoft
Entity Framework 6 and perform this analysis.
Analyzing Microsoft Entity Framework 6
As a first step, let's create the set of tables as shown in Listing 16-3.
Listing 16-3.
Test tables
create table dbo.Customers
(
CustomerId int not null identity(1,1),
FirstName nvarchar(255) null,
LastName nvarchar(255) null,
Email varchar(254) null,
LastPurchaseDate datetime2(0) null,
CreditLimit int null,
Photo varbinary(max) null,
Ver timestamp not null,
