Behind the slow speed of using Guid as the primary key

Recently, I encountered a performance problem that is so frustrating. There is a table in the production environment with 0.7 million data records. The result shows that it takes 3.5 seconds to execute a statement, whether it is a query that matches the primary key or an update! If you take the SQL statement intercepted in NH Prof to PL/SQL Developer for execution, it takes dozens of milliseconds. At first, I thought it was a problem with NH, and later I found that there was actually another secret.
Let's take a look at the environment first. 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. ORM uses nhib1_2.1.0 and 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 ");
...
}
}

Be sure to 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: fluentnhib.pdf. Conventions. IIdConvention
{
Public void Apply (fluentnhib.pdf. 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. CT (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: emp ");})
. BuildSessionFactory ();

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

<Hibernate-mapping xmlns = "urn: nhibernate-mapping-2.2" default-access = "property" auto-import = "true" default-cascade = "none" default-lazy = "true">
<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>
</Hibernate-mapping>

[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-srcsrcNHibernateTypeTypeFactory.cs 197th rows.
[3] See NHibernate-2.1.0.GA-srcsrcNHibernateDialectOracle8iDialect.cs 92nd rows.
[4] See NHibernate-2.1.0.GA-srcsrcNHibernateDialectOracle8iDialect.cs 88th rows.