The Problem

I was working on a C# ODATA WebAPI development that utilised NHibernate (version 4.1.1.4), Sybase, a Sybase ASE driver, .Net 4.7 etc. The tables being queried were from a legacy system and contain many char(1), char(10) and char(20) colums rather than varchar columns.

The performance of the API was not stunning.

Using an ODATA $filter against the API endpoints also did not achieve stunning performance – the response was the order of 10-20 Seconds. But why?

Investigation

NHibernate query logging was enabled so it was possible to see the queries being fired at the database – all looked fine. The queries were extracted from the log files and run directly against the database using TOAD – the queries ran quickly (a few hundred mS). So why was the API so slow?

The API code itself was quite simple – they controller method returned an IQueryable object and the magic of the Microsoft WebAPI infrastructure applied the $filter “WHERE CLAUSE” to the IQueryable and the results were returned as JSON to the caller.

However, many of the columns used in the WHERE clause were char(10) and char(20) columns rather than VARCHAR. In the NHIbernate XML mappings these fields were mapped to strings

 <property column="ExampleCharDBCol" type="String" name="ExampleModelPropertu" not-null="true" length="10"/>

From investiation it was surmised (NB surmised, I have no hard facts) that tomewhere in the NHibernate database communication the WHERE parameters were being transferred as some unicode encoded value which when, on the DB server, the query was “unpacked” and run the various indexes were ignored. Hence the slow querying

The Solution

I must give thans to B Boy and StackOverflow and Todd for pointing me in the right direction.

The answer is to use type=”AnsiString” in the NHibernate XML mappings:

 <property column="ExampleCharDBCol" type="AnsiString" name="ExampleModelPropertu" not-null="true" length="10"/>

Doing this made the queries run a factor 100 faster !!

Epilogue

I don’t think this solution is pertinent to only Sybase, I think it is valid for any database that NHibernate talks to.