Developers love object relational mapping. I get it. It speeds up development time, enabling them to focus more on solving business problems and less on learning the database schemas. But in life there are no free lunches. Tools that enable "code first, design second behavior" can have problems scaling. This post will touch on some of the main issues we run into when supporting an environment using ORMs.

<b font-weight: bold;>1: They bring back too many rows. The screenshot below is a contrived but common code generated by ORMs. There’s no WHERE clause which means all the rows will be returned to the client and then most likely filtered by .NET. The process of filtering should be left to database.

ORM1

<b font-weight: bold;>2: Too many columns. The query above is the equivalent of SELECT *. Applications generally require only a subset of existing columns returned for any given operation, returning all is overkill. From an optimization standpoint it’s much easier to index a query returning two columns verse a query returning 10.

<b font-weight: bold;>3: Excessive roundtrips Entity Framework uses a function called lazy loading by default. Related data is not loaded until specifically requested. Let's say for example we have an object named CUSTOMERS loaded and we want to see data associated with an ORDER property. For each customer a query will be executed to retrieve the property. If we executed this for customers in New York or California there might thousands of iterations. The more latency between the client and the server the greater the performance hit. As cloud technologies become more prevalent reducing application latency will become more of a priority.

4: Inefficient WHERE clauses.
The code below is an example of a query generated by Entity framework. A few things pop right off the bat:
  • The use of LTRIM(RTRIM()) functions on the column make index seeks impossible
  • Multiple OR operators make estimating the number of rows challenging for the optimizer which may result in a poor query plan selection
  • The use of sub-queries is pointless
  • Once again we are returning more columns then needed
SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[EventID] AS [EventID], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3], 
    [Limit1].[C4] AS [C4], 
    [Limit1].[CustomersEventStatusDescr] AS [CustomersEventStatusDescr], 
    [Limit1].[C5] AS [C5], 
    [Limit1].[ContractNumber] AS [ContractNumber], 
    [Limit1].[DepartmentNumber] AS [DepartmentNumber], 
    [Limit1].[DepartmentDescr] AS [DepartmentDescr], 
    [Limit1].[CustomerID] AS [CustomerID], 
    [Limit1].[CustomerName] AS [CustomerName], 
    [Limit1].[ClassNumber] AS [ClassNumber], 
    [Limit1].[ClassName] AS [ClassName], 
    [Limit1].[GroupNumber] AS [GroupNumber], 
    [Limit1].[Group] AS [Group], 
    [Limit1].[CustomersEventID] AS [CustomersEventID], 
    [Limit1].[IS_Special] AS [IS_Special]
    FROM ( 
	    SELECT TOP (1) 
        [Extent1].[CustomersEventStatusDescr] AS [CustomersEventStatusDescr], 
        [Extent1].[EventID] AS [EventID], 
        [Extent1].[CustomersEventID] AS [CustomersEventID], 
        [Extent1].[ClassNumber] AS [ClassNumber], 
        [Extent1].[ClassName] AS [ClassName], 
        [Extent1].[CustomerID] AS [CustomerID], 
        [Extent1].[CustomerName] AS [CustomerName], 
        [Extent1].[ContractNumber] AS [ContractNumber], 
        [Extent1].[DepartmentNumber] AS [DepartmentNumber], 
        [Extent1].[DepartmentDescr] AS [DepartmentDescr], 
        [Extent1].[GroupNumber] AS [GroupNumber], 
        [Extent1].[Group] AS [Group], 
        [Extent1].[IS_Special] AS [IS_Special], 
        1 AS [C1], 
        LTRIM(RTRIM([Extent1].[EventDescription])) AS [C2], 
        LTRIM(RTRIM([Extent1].[EventType])) AS [C3], 
        LTRIM(RTRIM([Extent1].[EventStatus])) AS [C4], 
         CAST( [Extent1].[EventStartDate] AS datetime2) AS [C5]
        FROM (
	SELECT 
    [Customers].[EventStatus] AS [EventStatus], 
    [Customers].[CustomersEventStatusCode] AS [CustomersEventStatusCode], 
    [Customers].[CustomersEventStatusDescr] AS [CustomersEventStatusDescr], 
    [Customers].[EventID] AS [EventID], 
    [Customers].[CustomersEventID] AS [CustomersEventID], 
    [Customers].[EventDescription] AS [EventDescription], 
    [Customers].[EventType] AS [EventType], 
    [Customers].[ClassNumber] AS [ClassNumber], 
    [Customers].[ClassName] AS [ClassName], 
    [Customers].[CustomerID] AS [CustomerID], 
    [Customers].[PrimaryCustomer_CustomersAccountNumber] AS [PrimaryCustomer_CustomersAccountNumber], 
    [Customers].[CustomerName] AS [CustomerName], 
    [Customers].[ContractNumber] AS [ContractNumber], 
    [Customers].[ContractName] AS [ContractName], 
    [Customers].[DepartmentNumber] AS [DepartmentNumber], 
    [Customers].[DepartmentDescr] AS [DepartmentDescr], 
    [Customers].[SpecialContractID] AS [SpecialContractID], 
    [Customers].[SpecialContractFlag] AS [SpecialContractFlag], 
    [Customers].[ParentContractID] AS [ParentContractID], 
    [Customers].[MarketSectorID] AS [MarketSectorID], 
    [Customers].[MarketSector] AS [MarketSector], 
    [Customers].[OwnerNameID] AS [OwnerNameID], 
    [Customers].[OwningCustomer_CustomersAccountNumber] AS [OwningCustomer_CustomersAccountNumber], 
    [Customers].[OwnerName] AS [OwnerName], 
    [Customers].[ContractMgrID] AS [ContractMgrID], 
    [Customers].[ContractMgrName] AS [ContractMgrName], 
    [Customers].[GroupNumber] AS [GroupNumber], 
    [Customers].[Group] AS [Group], 
    [Customers].[EMEmployeeID] AS [EMEmployeeID], 
    [Customers].[EMLName] AS [EMLName], 
    [Customers].[EMFName] AS [EMFName], 
    [Customers].[EMLogin] AS [EMLogin], 
    [Customers].[EMDept] AS [EMDept], 
    [Customers].[EMLoc] AS [EMLoc], 
    [Customers].[EMEmailAddress] AS [EMEmailAddress], 
    [Customers].[EventStartDate] AS [EventStartDate], 
    [Customers].[SpecialDescription] AS [SpecialDescription], 
    [Customers].[IS_Special] AS [IS_Special], 
    [Customers].[CreateManage] AS [CreateManage]
    FROM [dbo].[Customers] AS [Customers]) AS [Extent1]
     WHERE ((LTRIM(RTRIM([Extent1].[EventID]))) = (LTRIM(RTRIM(@p__linq__0))))
	  OR ((LTRIM(RTRIM([Extent1].[EventID])) IS NULL) AND (LTRIM(RTRIM(@p__linq__0)) IS NULL)) 
	  OR ([Extent1].[CustomersEventID] = (LTRIM(RTRIM(@p__linq__1)))) 
	  OR (([Extent1].[CustomersEventID] IS NULL) AND (LTRIM(RTRIM(@p__linq__1)) IS NULL))
    )  AS [Limit1]

It's important to be aware of the trade-offs associated with code creation tools. Doing so will help prevent headaches down the road. A couple concepts to keep in mind when using ORMs:

  • Bring back only what you need
  • Let the database do the filtering
  • Understand the behavior of the lazy loading function, if possible test eager loading
  • For complex data operations consider writing stored procedures
  • For queries using multiple combinations of search parameters consider building the statement dynamically