Why is the execution speed too slow when the Oracle database Guid is used as the primary key?

Oracle DatabaseWhat is the reason for the slow execution of GUID as the primary key? Next we will start to introduce it. First, introduce the environment. The database uses Oracle10g. All character fields are varchar2 [1]. All primary keys use Guid, which is of the varchar2 (36) type in the database. Corresponding, the Object Id attribute is of the string type. The ORM usesNhib1_2.1.0And FluentNHibernate1.1.

After some troubleshooting, we found that the root cause of the problem was that when NH passed the SQL statement to Oracle, all struct parameters were of the nvarchar2 type, while the corresponding fields in the database were of the varchar2 type, this will cause Oracle to fail to use the index, and finally cause full table scanning. Therefore, if the data volume is a little large, it will not slow down.

The first solution is to change the type of all the reserved fields in the database from varchar2 to nvarchar2. We do not want to do this for various reasons.

The second solution is to let NH pass varchar2 as the parameter type to Oracle.

In fact, NH maps the. net string to DbType. String [2] by default, and maps DbType. String to nvarchar2 [3]. Map DbType. AnsiString to varchar2 [4].

Therefore, the query is simple. You only need to specify the HQL parameter type as AnsiString.

 
 

  
  
var query = Session.CreateQuery(@"select t from Region as t  
  
  
 
  
  
where t.Id = :Id")  
  
  
 
  
  
.SetAnsiString("Id", id);  
  
  
 
  
  
var query = Session.CreateQuery(@"select t from Region as t  
  
  
 
  
  
where t.Id in (:Ids)")  
  
  
 
  
  
.SetParameterList("Ids", ids.ToList(), NHibernateUtil.AnsiString); 
 
 

But how do I set the parameter types of the Update and Delete statements? Here is a small secret to specify the attribute type in the ing file as "AnsiString.

 
 

  
  
public class RegionMap : TreeNodeMap<Region> 
  
  
 
  
  
{  
  
  
 
  
  
public RegionMap()  
  
  
 
  
  
{  
  
  
 
  
  
Table("INFRA_REGION");  
  
  
 
  
  
Id(t => t.Id, "REGION_ID").CustomType("AnsiString");  
  
  
 
  
  
...  
  
  
 
  
  
}  
  
  
 
  
  
} 
 
 

Note: You must use CustomType () instead of CustomSqlType ().

Of course, it would be annoying to change every configuration file. It seems that the project uses Fluent nhib.pdf and you only need to add an IdConvention.

 
 

  
  
public class IdConvention : FluentNHibernate.Conventions.IIdConvention  
  
  
 
  
  
{  
  
  
 
  
  
public void Apply(FluentNHibernate.Conventions.Instances.IIdentityInstance instance)  
  
  
 
  
  
{  
  
  
 
  
  
instance.CustomType("AnsiString");  
  
  
 
  
  
}  
  
  
 
  
  
} 
 
 

To be thorough, you can add a convention of the string type property.

 
 

  
  
public class StringPropertyConvention : IPropertyConvention, IPropertyConventionAcceptance  
  
  
 
  
  
{  
  
  
 
  
  
public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)  
  
  
 
  
  
criteria.Expect(x => x.Property.PropertyType == typeof(string));  
  
  
 
  
  
public void Apply(IPropertyInstance instance)  
  
  
 
  
  
{  
  
  
 
  
  
instance.CustomType("AnsiString");  
  
  
 
  
  
}  
  
  
 
  
  
} 
 
 

Add the two Convention to the Configuration:

 
 

  
  
Session ["SessionFactory"] = Fluently. Configure ()
  
  
 
  
  
. Database (OracleClientConfiguration. Oracle10
  
  
 
  
  
. Dialect <Oracle10gDialect> ()
  
  
 
  
  
. ConnectionString ("User ID = iBlast; Password = not allowed; Data Source = Moki ")
  
  
 
  
  
. QuerySubstitutions ("true 1, false 0, yes 'y', no 'n '")
  
  
 
  
  
. UseOuterJoin ()
  
  
 
  
  
. ProxyFactoryFactory <ProxyFactoryFactory> ()
  
  
 
  
  
. AdoNetBatchSize (1000)
  
  
 
  
  
. Driver <OracleClientDriver> ())
  
  
 
  
  
. Mappings (m => {m. HbmMappings. AddFromAssembly (Assembly. Load ("Infrastructure. Repositories "));
  
  
 
  
  
M. FluentMappings. AddFromAssembly (Assembly. Load ("Infrastructure. Repositories "))
  
  
 
  
  
. Conventions. Add <EnumConvention> ()
  
  
 
  
  
. Conventions. Add <HasManyConvention> ()
  
  
 
  
  
. Conventions. Add <HasManyToManyConvention> ()
  
  
 
  
  
. Conventions. Add <StringPropertyConvention> ()
  
  
 
  
  
. Conventions. Add <IdConvention> ()
  
  
 
  
  
. ExportTo (@ "F: \ temp \");})
  
  
 
  
  
. BuildSessionFactory ();
 
 

Note the last and second rows. exportTo (@ "F: \ temp \") outputs the ing file to "F: \ temp \" to test whether the generated ing file is correct \", the ing file should look like this:

 
 

  
  
 
  
 
  
  
<class xmlns="urn:nhibernate-mapping-2.2" dynamic-insert="true" dynamic-update="true" mutable="true" where="IsDelete=0" name="Dawn.HIS.Infrastructure.Core.Data.Region, Infrastructure.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" table="INFRA_REGION"> 
  
  
 
  
  
<id name="Id" type="AnsiString"> 
  
  
 
  
  
  <column name="REGION_ID" /> 
  
  
 
  
  
  <generator class="assigned" /> 
  
  
 
  
  
</id> 
  
  
 
  
  
<version name="Version" type="System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> 
  
  
 
  
  
  <column name="Version" /> 
  
  
 
  
  
</version> 
  
  
 
  
  
<property name="CreateTime" type="System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"> 
  
  
 
  
  
  <column name="CREATETIME" /> 
  
  
 
  
  
</property> 
  
  
 
  
  
<property name="Name" type="AnsiString"> 
  
  
 
  
  
  <column name="NAME" /> 
  
  
 
  
  
</property> 
  
  
 
  
  
...  
  
  
 
  
  
</class> 
  
  
 
  
  
 
 

Note:

[1] varchar2 instead of nvarchar2 is used to avoid performance problems in nvarchar2 sorting, in addition to considering varchar2 to save space.

[2] See NHibernate-2.1.0.GA-src \ src \ nhib.pdf \ Type \ TypeFactory. cs line 197th.

[3] See NHibernate-2.1.0.GA-src \ src \ nhibect \ Dialect \ Oracle8iDialect. cs line 92nd.

[4] See NHibernate-2.1.0.GA-src \ src \ nhibect \ Dialect \ Oracle8iDialect. cs line 88th.

This article will introduce you here. If you want to learn more about Oracle databases, you can read this article: http://database.51cto.com/oracle /.