自己来控制EntityFramework4.1 Code-First,强大的EF多种加载方式

众所周知,EF对关系表的联表查询有2种方式:延迟加载、贪婪加载,为了弄清这2种方式具体是如何操作数据库的,我开启了SQL Server Profiler,只开启了存储过程和TSQL的监视,并且指定了当前操作的库。

   //3联表,Order Product Category
1. var ordercontext = db.OrderContext.Include(o => o.Product); //外键表贪婪一个,延迟一个Category 
2. var ordercontext = db.OrderContext.Include(o => o.Product).Include(o=>o.Product.Category) //外键表全贪婪
3. var ordercontext = db.OrderContext;  //外键表全延时

对应生成的SQL并执行的语句为:查询输出100条记录

1.半延迟半贪婪  3次数据库查询
执行时间:0.0181664 
解析时间:0.0629575
总时间:0.0811491
SELECT 
[Extent1].[ID] AS [ID],
[Extent1].[Product_ID] AS [Product_ID],
[Extent1].[Name] AS [Name],
[Extent1].[Address] AS [Address],
[Extent1].[CreateTime] AS [CreateTime],
[Extent2].[ID] AS [ID1],
[Extent2].[Name] AS [Name1],
[Extent2].[CategoryID] AS [CategoryID],
[Extent2].[Price] AS [Price]
FROM  [dbo].[Order] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2


2. 全贪婪  1次数据库查询
执行时间:0.0173032 
解析时间:0.0533344
总时间:0.0706627
SELECT 
[Extent1].[ID] AS [ID],
[Extent1].[Product_ID] AS [Product_ID],
[Extent1].[Name] AS [Name],
[Extent1].[Address] AS [Address],
[Extent1].[CreateTime] AS [CreateTime],
[Extent2].[ID] AS [ID1],
[Extent2].[Name] AS [Name1],
[Extent2].[CategoryID] AS [CategoryID],
[Extent2].[Price] AS [Price],
[Extent4].[CategoryID] AS [CategoryID1],
[Extent4].[CategoryName] AS [CategoryName],
[Extent4].[IsDel] AS [IsDel]
FROM    [dbo].[Order] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Product] AS [Extent3] ON [Extent1].[Product_ID] = [Extent3].[ID]
LEFT OUTER JOIN [dbo].[Category] AS [Extent4] ON [Extent3].[CategoryID] = [Extent4].[CategoryID]

3.全延迟加载, 延迟2个表 ,5次数据库查询
执行时间:0.0128477 
解析时间:0.0796226
总时间:0.0925025
SELECT 
[Extent1].[ID] AS [ID],
[Extent1].[Product_ID] AS [Product_ID],
[Extent1].[Name] AS [Name],
[Extent1].[Address] AS [Address],
[Extent1].[CreateTime] AS [CreateTime]
FROM [dbo].[Order] AS [Extent1]

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

三中加载方式和对应的实际操作SQL命令我贴了出来,每种方式的数据库查询次数都不同,并且延迟加载是以存储过程方式执行的语句; 细心的人可能发现我输出了执行时间,这个我是在MVC过滤器中做的定时器,虽然我已经刷新N次,取了中间值,但这个执行时间还是比较离奇,权当我机器环境不稳定了,但有点可理解的是,比如底3种,按说他应该最慢的,但并没有如此,因为第一条sql语句没有使用联表节约了时间,而其后面的4条延时加载又以存储过程方式执行提高了性能。

需要注意的是,经调试和监视SQL SERVER,发现EF对延迟加载统一使用存储过程方式执行,而贪婪式则为JOIN联表操作,此文并不作几种方式的性能评测结论,因为联表中的外键表记录只有2条,所以聪明的EF是读取所需的外键值针对性的去读库,而不是100条记录联2个表就要额外读200次外键表。自然,当数据库联表外键值较多时,或者每条主表记录的外键值都不同时,的确要读和记录相同数的数据库次数。

因地而异,并不能指定怎样去用何种方式加载,但输出关系表内容,并调用关系表字段值时,仍然建议使用贪婪式加载。

posted @ 2011-07-07 16:43  Richwong  Views(4018)  Comments(2Edit  收藏  举报