A Wandering Mind

Introduction

In this first of several practical lab sessions in this series, we’re going to evaluate the performance of using the Entity Framework with and without mapped stored procedures, specifically across:

1. Size of the plan cache (in kilobytes)
2. Number of cached plans
3. Use of underlying indexes / execution plan

Today we’re going to examine the relative performance of SELECT operations against the AdventureWorks database.

For the purposes of this lab, we are going to first select a particular sales order from the Sales.SalesOrder table (using the primary key SalesOrderId column as a WHERE clause), then retrieve the associated details from the Sales.SalesOrderDetail table.

In the LINQ language, this retrieval is accomplished using this code:

IQueryable<SalesOrderDetail> qSOD = from sod in awEnt.SalesOrderDetails
                                                 where sod.SalesOrderID == SalesOrderID
                                                 select sod;

Once the object is retrieved, we can simply print out the details:

            foreach (SalesOrderHeader mySOH in qSOH)
            {
                Console.WriteLine("Details for SalesOrderID " + mySOH.SalesOrderID);
                foreach (SalesOrderDetail mySOD in mySOH.SalesOrderDetails)
                {
                    Console.WriteLine(" Total Cost: " + mySOD.LineTotal);
                }
            }

This operation will be repeated for a number of numerically sequential SalesOrderIDs (i.e. from 42111-42121). This will help to establish plan cache reuse, since the queries should be identical except for differing parameters.

Observations

“Lazy” Loading

When the initial statement (the “from x in x”) is stepped over, no SQL is executed. Only when the first object is retrieved do we see a statement call. From what I can tell, this is expected behavior.

Use of sp_executesql and parameterized statement

The statement execution is using a parameterized, well-formed query:

exec sp_executesql N'SELECT
[Extent1].[SalesOrderID] AS [SalesOrderID],
[Extent1].[RevisionNumber] AS [RevisionNumber],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[DueDate] AS [DueDate],
[Extent1].[ShipDate] AS [ShipDate],
[Extent1].[Status] AS [Status],
[Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
[Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Extent1].[AccountNumber] AS [AccountNumber],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[ContactID] AS [ContactID],
[Extent1].[SalesPersonID] AS [SalesPersonID],
[Extent1].[TerritoryID] AS [TerritoryID],
[Extent1].[BillToAddressID] AS [BillToAddressID],
[Extent1].[ShipToAddressID] AS [ShipToAddressID],
[Extent1].[ShipMethodID] AS [ShipMethodID],
[Extent1].[CreditCardID] AS [CreditCardID],
[Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Extent1].[CurrencyRateID] AS [CurrencyRateID],
[Extent1].[SubTotal] AS [SubTotal],
[Extent1].[TaxAmt] AS [TaxAmt],
[Extent1].[Freight] AS [Freight],
[Extent1].[TotalDue] AS [TotalDue],
[Extent1].[Comment] AS [Comment],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [Sales].[SalesOrderHeader] AS [Extent1]
WHERE [Extent1].[SalesOrderID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=43659

This is also true when the related “SalesOrderDetail” object is retrieved:

exec sp_executesql N'SELECT
[Extent1].[SalesOrderID] AS [SalesOrderID],
[Extent1].[SalesOrderDetailID] AS [SalesOrderDetailID],
[Extent1].[CarrierTrackingNumber] AS [CarrierTrackingNumber],
[Extent1].[OrderQty] AS [OrderQty],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[SpecialOfferID] AS [SpecialOfferID],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitPriceDiscount] AS [UnitPriceDiscount],
[Extent1].[LineTotal] AS [LineTotal],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [Sales].[SalesOrderDetail] AS [Extent1]
WHERE [Extent1].[SalesOrderID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=43659

Query Plan Reuse

The following SQL was used to interrogate the plan cache:

select	qp.query_plan, cp.*
from	sys.dm_exec_cached_plans cp cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
		cross apply sys.dm_exec_plan_attributes(cp.plan_handle) pa
where	pa.attribute = 'dbid' and pa.value = DB_ID('AdventureWorks')

When examining the query plan cache after execution, there are three plans in cache:

Two of the plans are for retrieving from the SalesOrderDetail, and appear to be identical (even a diff program could not determine a difference in the statement), and the third is for retrieving from the SalesOrderHeader table. In both cases the plans show good reuse (ten and eleven uses for the second and third plans respectively, though the first plan showed only a single use), and are using underlying indexes appropriately.

Stored Procedure Comparison

Now, let’s compare this with when we use the function import feature of EF to enable using a stored procedure to retrieve both the SalesOrderHeader and SalesOrderDetail records.

For the purposes of the exercise I created two simple stored procedures that retrieve all columns in the SalesOrderHeader and SalesOrderDetail tables based upon an inputted value for the SalesOrderID column. They are called p_getSalesOrderHeader and p_getSalesOrderDetails (yeah I know, bad naming consistency) respectively.

The C# code behind the scenes is slightly different, in that we are calling the “imported” functions:

System.Data.Objects.ObjectResult<SalesOrderHeader> qSOH2 = awEnt.p_getSalesOrderHeader(SalesOrderID);

            foreach (SalesOrderHeader mySOH in qSOH2)
            {
                Console.WriteLine("Details for SalesOrderID " + mySOH.SalesOrderID);
                foreach (SalesOrderDetail mySOD in awEnt.getSalesOrderDetail(mySOH.SalesOrderID))
                {
                    Console.WriteLine(" Total Cost: " + mySOD.LineTotal);
                }
            }
        }

The results are very similar to the example not using stored procedures:

As you can see, there are 4 query plans in cache (two with one use, two with ten uses; again, I can’t find a difference to explain this, but at least it’s consistent), all of equal size to those generated using the parameterized statements.

Conclusions

Based on this initial set of data, I would say that there is no appreciable difference, plan cache wise, between using EF without and with stored procedures, for equivalent SELECT style operations. Again, this is only from the perspective of avoiding plan cache bloat, and ignores areas such as security best practices and enabling easy review of code.

Next time we’ll look at using EF with INSERT operations.

Be Sociable, Share!

•