前面提到過這個網址:性能註意事項(實體框架) https://msdn.microsoft.com/zh-cn/library/cc853327.aspx註意版本: .NET Framework (current version) (還有一個其他版本, .NET Framework 4). 此文提到 ...
前面提到過這個網址:性能註意事項(實體框架) https://msdn.microsoft.com/zh-cn/library/cc853327.aspx
註意版本: .NET Framework (current version) (還有一個其他版本, .NET Framework 4).
此文提到 查詢執行的各個階段[格式:操作(相對成本)]:
載入元數據(中等),打開資料庫連接(中等),生成視圖(高),準備查詢(中等),執行查詢(低),載入和驗證類型(低),跟蹤(低),是對象具體化(中等)
還是截個圖吧.
其中,我特別在意'生成視圖'這個階段,高成本(其它階段要麼頻率低要麼成本低).
生成視圖,註釋如下:在實體框架可以針對概念模型執行查詢或將更改保存到數據源之前,它必鬚生成一組本地查詢視圖才能訪問資料庫。 由於生成這些視圖會產生很高的成本,因此,您可以在設計時預生成視圖並將它們添加到項目。 有關詳細信息,請參閱How to: Pre-Generate Views to Improve Query Performance。
於是,我就去看How to: Pre-Generate Views to Improve Query Performance(以下簡稱H文)。(如何:預生成視圖以提高查詢性能)
動手,建立解決方案名EFTest,控制台名EFIce.
簡要說下H文的步驟(跟H文有差異):
a.打開PhoneBookModel.edmx,在模型瀏覽器里,找到PhoneBookModel,右鍵,屬性,有一項屬性名為'元數據項目處理',預設值是'嵌入輸出程式集中',更改為'複製到輸出目錄';
b.生成PhoneBookModel.ssdl,PhoneBookModel.csdl,PhoneBookModel.msl,PhoneBookModel.Views.cs四個文件並加入到項目里,其中.ssdl,.csdl,.msl三個文件的屬性生成操作設置為'嵌入的資源',而PhoneBookModel.Views.cs文件會直接編譯到.dll里.
此處生成文件的方法:命令提示符執行
C:\Users\Administrator>EdmGen /mode:FullGeneration /connectionstring:"server=.;user id=sa;password=******;persist security info=True;database=PhoneBook" /provider:"System.Data.SqlClient" /outssdl:PhoneBookModel.ssdl /outcsdl:PhoneBookModel.csdl /outmsl:PhoneBookModel.msl /outobjectlayer:PhoneBookObject.cs /outviews:PhoneBook.Views.cs /namespace:"EFIce" /entitycontainer:"PhoneBookEntitiesContainer
產生的文件的位置C:\Users\Administrator目錄下.
PhoneBookModel.edmx的圖解:
文件結果:
//------------------------------------------------------------------------------ // <auto-generated> // 此代碼由工具生成。 // 運行時版本:4.0.30319.36282 // // 對此文件的更改可能會導致不正確的行為,並且如果 // 重新生成代碼,這些更改將會丟失。 // </auto-generated> //------------------------------------------------------------------------------ [assembly: System.Data.Mapping.EntityViewGenerationAttribute(typeof(Edm_EntityMappingGeneratedViews.ViewsForBaseEntitySets9E94E67D0053D8FDE07EEEB8F52E0E47D0A29AB22FB0BCB5EB6BE8BC009D5748))] namespace Edm_EntityMappingGeneratedViews { /// <Summary> /// 此類型包含在設計時生成的 EntitySets 和 AssociationSets 的視圖。 /// </Summary> public sealed class ViewsForBaseEntitySets9E94E67D0053D8FDE07EEEB8F52E0E47D0A29AB22FB0BCB5EB6BE8BC009D5748 : System.Data.Mapping.EntityViewContainer { /// <Summary> /// 構造函數存儲各區的視圖,以及根據元數據和映射結束和視圖生成的哈希值。 /// </Summary> public ViewsForBaseEntitySets9E94E67D0053D8FDE07EEEB8F52E0E47D0A29AB22FB0BCB5EB6BE8BC009D5748() { this.EdmEntityContainerName = "PhoneBookEntitiesContainer"; this.StoreEntityContainerName = "EFIceStoreContainer"; this.HashOverMappingClosure = "4c6b0bf3e3884e8b4a6c4c7c60d99b787ec990e9b24297519ab203951cadc07d"; this.HashOverAllExtentViews = "d048405239ad31569ab8a2a9f67381c39d4130d40b625903b35e1e8d2d1266f5"; this.ViewCount = 8; } /// <Summary> /// 此方法返回給定索引的視圖。 /// </Summary> protected override System.Collections.Generic.KeyValuePair<string, string> GetViewAt(int index) { if ((index == 0)) { return GetView0(); } if ((index == 1)) { return GetView1(); } if ((index == 2)) { return GetView2(); } if ((index == 3)) { return GetView3(); } if ((index == 4)) { return GetView4(); } if ((index == 5)) { return GetView5(); } if ((index == 6)) { return GetView6(); } if ((index == 7)) { return GetView7(); } throw new System.IndexOutOfRangeException(); } /// <Summary> /// 返回 EFIceStoreContainer.AccountInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView0() { return new System.Collections.Generic.KeyValuePair<string, string>("EFIceStoreContainer.AccountInfo", @" SELECT VALUE -- Constructing AccountInfo [EFIce.Store.AccountInfo](T1.AccountInfo_ID, T1.AccountInfo_Account, T1.AccountInfo_AccountName, T1.AccountInfo_Pwd) FROM ( SELECT T.ID AS AccountInfo_ID, T.Account AS AccountInfo_Account, T.AccountName AS AccountInfo_AccountName, T.Pwd AS AccountInfo_Pwd, True AS _from0 FROM PhoneBookEntitiesContainer.AccountInfo AS T ) AS T1"); } /// <Summary> /// 返回 PhoneBookEntitiesContainer.AccountInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView1() { return new System.Collections.Generic.KeyValuePair<string, string>("PhoneBookEntitiesContainer.AccountInfo", @" SELECT VALUE -- Constructing AccountInfo [EFIce.AccountInfo](T1.AccountInfo_ID, T1.AccountInfo_Account, T1.AccountInfo_AccountName, T1.AccountInfo_Pwd) FROM ( SELECT T.ID AS AccountInfo_ID, T.Account AS AccountInfo_Account, T.AccountName AS AccountInfo_AccountName, T.Pwd AS AccountInfo_Pwd, True AS _from0 FROM EFIceStoreContainer.AccountInfo AS T ) AS T1"); } /// <Summary> /// 返回 EFIceStoreContainer.ContactInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView2() { return new System.Collections.Generic.KeyValuePair<string, string>("EFIceStoreContainer.ContactInfo", @" SELECT VALUE -- Constructing ContactInfo [EFIce.Store.ContactInfo](T1.ContactInfo_ID, T1.ContactInfo_ContactId, T1.ContactInfo_IsDelete, T1.ContactInfo_Account, T1.ContactInfo_ContactName, T1.ContactInfo_CommonMobile, T1.ContactInfo_HeadPortrait, T1.ContactInfo_AttFile, T1.ContactInfo_GroupId) FROM ( SELECT T.ID AS ContactInfo_ID, T.ContactId AS ContactInfo_ContactId, T.IsDelete AS ContactInfo_IsDelete, T.Account AS ContactInfo_Account, T.ContactName AS ContactInfo_ContactName, T.CommonMobile AS ContactInfo_CommonMobile, T.HeadPortrait AS ContactInfo_HeadPortrait, T.AttFile AS ContactInfo_AttFile, T.GroupId AS ContactInfo_GroupId, True AS _from0 FROM PhoneBookEntitiesContainer.ContactInfo AS T ) AS T1"); } /// <Summary> /// 返回 EFIceStoreContainer.GroupInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView3() { return new System.Collections.Generic.KeyValuePair<string, string>("EFIceStoreContainer.GroupInfo", @" SELECT VALUE -- Constructing GroupInfo [EFIce.Store.GroupInfo](T1.GroupInfo_GroupId, T1.GroupInfo_GroupName) FROM ( SELECT T.GroupId AS GroupInfo_GroupId, T.GroupName AS GroupInfo_GroupName, True AS _from0 FROM PhoneBookEntitiesContainer.GroupInfo AS T ) AS T1"); } /// <Summary> /// 返回 PhoneBookEntitiesContainer.ContactInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView4() { return new System.Collections.Generic.KeyValuePair<string, string>("PhoneBookEntitiesContainer.ContactInfo", @" SELECT VALUE -- Constructing ContactInfo [EFIce.ContactInfo](T1.ContactInfo_ID, T1.ContactInfo_ContactId, T1.ContactInfo_IsDelete, T1.ContactInfo_Account, T1.ContactInfo_ContactName, T1.ContactInfo_CommonMobile, T1.ContactInfo_HeadPortrait, T1.ContactInfo_AttFile, T1.ContactInfo_GroupId) FROM ( SELECT T.ID AS ContactInfo_ID, T.ContactId AS ContactInfo_ContactId, T.IsDelete AS ContactInfo_IsDelete, T.Account AS ContactInfo_Account, T.ContactName AS ContactInfo_ContactName, T.CommonMobile AS ContactInfo_CommonMobile, T.HeadPortrait AS ContactInfo_HeadPortrait, T.AttFile AS ContactInfo_AttFile, T.GroupId AS ContactInfo_GroupId, True AS _from0 FROM EFIceStoreContainer.ContactInfo AS T ) AS T1"); } /// <Summary> /// 返回 PhoneBookEntitiesContainer.GroupInfo 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView5() { return new System.Collections.Generic.KeyValuePair<string, string>("PhoneBookEntitiesContainer.GroupInfo", @" SELECT VALUE -- Constructing GroupInfo [EFIce.GroupInfo](T1.GroupInfo_GroupId, T1.GroupInfo_GroupName) FROM ( SELECT T.GroupId AS GroupInfo_GroupId, T.GroupName AS GroupInfo_GroupName, True AS _from0 FROM EFIceStoreContainer.GroupInfo AS T ) AS T1"); } /// <Summary> /// 返回 EFIceStoreContainer.ContactInGroup 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView6() { return new System.Collections.Generic.KeyValuePair<string, string>("EFIceStoreContainer.ContactInGroup", @" SELECT VALUE -- Constructing ContactInGroup [EFIce.Store.ContactInGroup](T1.ContactInGroup_Id, T1.ContactInGroup_Account, T1.ContactInGroup_GroupNumber, T1.ContactInGroup_ContactId, T1.ContactInGroup_IsDelete, T1.ContactInGroup_UpdateTime) FROM ( SELECT T.Id AS ContactInGroup_Id, T.Account AS ContactInGroup_Account, T.GroupNumber AS ContactInGroup_GroupNumber, T.ContactId AS ContactInGroup_ContactId, T.IsDelete AS ContactInGroup_IsDelete, T.UpdateTime AS ContactInGroup_UpdateTime, True AS _from0 FROM PhoneBookEntitiesContainer.ContactInGroup AS T ) AS T1"); } /// <Summary> /// 返回 PhoneBookEntitiesContainer.ContactInGroup 的視圖 /// </Summary> private System.Collections.Generic.KeyValuePair<string, string> GetView7() { return new System.Collections.Generic.KeyValuePair<string, string>("PhoneBookEntitiesContainer.ContactInGroup", @" SELECT VALUE -- Constructing ContactInGroup [EFIce.ContactInGroup](T1.ContactInGroup_Id, T1.ContactInGroup_Account, T1.ContactInGroup_GroupNumber, T1.ContactInGroup_ContactId, T1.ContactInGroup_IsDelete, T1.ContactInGroup_UpdateTime) FROM ( SELECT T.Id AS ContactInGroup_Id, T.Account AS ContactInGroup_Account, T.GroupNumber AS ContactInGroup_GroupNumber, T.ContactId AS ContactInGroup_ContactId, T.IsDelete AS ContactInGroup_IsDelete, T.UpdateTime AS ContactInGroup_UpdateTime, True AS _from0 FROM EFIceStoreContainer.ContactInGroup AS T ) AS T1"); } } }PhoneBook.Views.cs
<?xml version="1.0" encoding="utf-8"?> <Schema Namespace="EFIce" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm"> <EntityContainer Name="PhoneBookEntitiesContainer" annotation:LazyLoadingEnabled="true"> <EntitySet Name="AccountInfo" EntityType="EFIce.AccountInfo" /> <EntitySet Name="ContactInfo" EntityType="EFIce.ContactInfo" /> <EntitySet Name="ContactInGroup" EntityType="EFIce.ContactInGroup" /> <EntitySet Name="GroupInfo" EntityType="EFIce.GroupInfo" /> <AssociationSet Name="FK_ContactInfo_GroupInfo" Association="EFIce.FK_ContactInfo_GroupInfo"> <End Role="GroupInfo" EntitySet="GroupInfo" /> <End Role="ContactInfo" EntitySet="ContactInfo" /> </AssociationSet> </EntityContainer> <EntityType Name="AccountInfo"> <Key> <PropertyRef Name="ID" /> </Key> <Property Name="ID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" /> <Property Name="Account" Type="String" MaxLength="64" Unicode="true" FixedLength="false" /> <Property Name="AccountName" Type="String" MaxLength="50" Unicode="true" FixedLength="false" /> <Property Name="Pwd" Type="String" MaxLength="50" Unicode="true" FixedLength="false" /> </EntityType> <EntityType Name="ContactInfo"> <Key> <PropertyRef Name="ID" /> </Key> <Property Name="ID" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" /> <Property Name="ContactId" Type="String" Nullable="false" MaxLength="128" Unicode="true" FixedLength="false" /> <Property Name="IsDelete" Type="Int32" Nullable="false" /> <Property Name="Account" Type="String" Nullable="false" MaxLength="64" Unicode="true" FixedLength="false" /> <Property Name="ContactName" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" /> <Property Name="CommonMobile" Type="String" MaxLength="50" Unicode="true" FixedLength="false" /> <Property Name="HeadPortrait" Type="String" MaxLength="256" Unicode="true" FixedLength="false" /> <Property Name="AttFile" Type="String" MaxLength="256" Unicode="true" FixedLength="false" /> <Property Name="GroupId" Type="Int32" /> <NavigationProperty Name="GroupInfo" Relationship="EFIce.FK_ContactInfo_GroupInfo" FromRole="ContactInfo" ToRole="GroupInfo" /> </EntityType> <EntityType Name="ContactInGroup"> <Key> <PropertyRef Name="Id" /> </Key> <Property Name="Id" Type="String" Nullable="false" MaxLength="128" Unicode="false" FixedLength="false" /> <Property Name="Account" Type="String" Nullable="false" MaxLength="64" Unicode="true" FixedLength="false" /> <Property Name="GroupNumber" Type="String" Nullable="false" MaxLength="16" Unicode="true" FixedLength="false" /> <Property Name="ContactId" Type="String" Nullable="false" MaxLength="128" Unicode="false" FixedLength="false" /> <Property Name="IsDelete" Type="Int32" /> <Property Name="UpdateTime" Type="DateTime" /> </EntityType> <EntityType Name="GroupInfo"> <Key> <PropertyRef Name="GroupId" /> </Key> <Property Name="GroupId" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" /> <Property Name="GroupName" Type="String" Nullable="false" MaxLength="300" Unicode="true" FixedLength="false" /> <NavigationProperty Name="ContactInfo" Relationship="EFIce.FK_ContactInfo_GroupInfo" FromRole="GroupInfo" ToRole="ContactInfo" /> </EntityType> <Association Name="FK_ContactInfo_GroupInfo"> <End Role="GroupInfo" Type="EFIce.GroupInfo" Multiplicity="0..1" /> <End Role="ContactInfo" Type="EFIce.ContactInfo" Multiplicity="*" /> <ReferentialConstraint> <Principal Role="GroupInfo"> <PropertyRef Name="GroupId" /> </Principal> <Dependent Role="ContactInfo"> <PropertyRef Name="GroupId" /> </Dependent> </ReferentialConstraint> </Association> </Schema>PhoneBookModel.csdl
<?xml version="1.0" encoding="utf-8"?> <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs"> <EntityContainerMapping StorageEntityContainer="EFIceStoreContainer" CdmEntityContainer="PhoneBookEntitiesContainer"> <EntitySetMapping Name="AccountInfo"> <EntityTypeMapping TypeName="EFIce.AccountInfo"> <MappingFragment StoreEntitySet="AccountInfo"> <ScalarProperty Name="ID" ColumnName="ID" /> <ScalarProperty Name="Account" ColumnName="Account" /> <ScalarProperty Name="AccountName" ColumnName="AccountName" /> <ScalarProperty Name="Pwd" ColumnName="Pwd" /> </MappingFragment> </EntityTypeMapping> </EntitySetMapping> <EntitySetMapping Name="ContactInfo"> <EntityTypeMapping TypeName="EFIce.ContactInfo"> <MappingFragment StoreEntitySet="ContactInfo"> <ScalarProperty Name="ID" ColumnName="ID" /> <ScalarProperty Name="ContactId" ColumnName="ContactId" /> <ScalarProperty Name="IsDelete" ColumnName="IsDelete" /> <ScalarProperty Name="Account" ColumnName="Account" /> <ScalarProperty Name="ContactName" ColumnName="ContactName" /> <ScalarProperty Name="CommonMobile" ColumnName="CommonMobile" /> <ScalarProperty Name="HeadPortrait" ColumnName="HeadPortrait" /> <ScalarProperty Name="AttFile" ColumnName="AttFile" /> <ScalarProperty Name="GroupId" ColumnName="GroupId" /> </MappingFragment> </EntityTypeMapping> </EntitySetMapping> <EntitySetMapping Name="ContactInGroup"> <EntityTypeMapping TypeName="EFIce.ContactInGroup"> <MappingFragment StoreEntitySet="ContactInGroup"> <ScalarProperty Name="Id" ColumnName="Id" /> <ScalarProperty Name="Account" ColumnName="Account" /> <ScalarProperty Name="GroupNumber" ColumnName="GroupNumber" /> <ScalarProperty Name="ContactId" ColumnName="ContactId" /> <ScalarProperty Name="IsDelete" ColumnName="IsDelete" /> <ScalarProperty Name="UpdateTime" ColumnName="UpdateTime" /> </MappingFragment> </EntityTypeMapping> </EntitySetMapping> <EntitySetMapping Name="GroupInfo"> <EntityTypeMapping TypeName="EFIce.GroupInfo"> <MappingFragment StoreEntitySet="GroupInfo"> <ScalarProperty Name="GroupId" ColumnName="GroupId" /> <ScalarProperty Name="GroupName" ColumnName="GroupName" /> </MappingFragment> </EntityTypeMapping> </EntitySetMapping> </EntityContainerMapping> </Mapping>PhoneBookModel.msl
<?xml version="1.0" encoding="utf-8"?> <Schema Namespace="EFIce.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl"> <EntityContainer Name="EFIceStoreContainer"> <EntitySet Name="AccountInfo" EntityType="EFIce.Store.AccountInfo" store:Type="Tables" Schema="dbo" /> <EntitySet Name="ContactInfo" EntityType="EFIce.Store.ContactInfo" store:Type="Tables" Schema="dbo" /> <EntitySet Name="ContactInGroup" EntityType="EFIce.Store.ContactInGroup" store:Type="Tables" Schema="dbo" /> <