Hi,

I'm executing a query against a Sybase database server and execution times are very slow in some cases.

Here are the execution times for the NHibernate query methods that I have tested:
- HQL with SetParameter() : 7 seconds (KO)
- native SQL : 23 ms (OK)
- "native" HQL (no SetParameter()) : 23 ms (OK)
- ICriteria : 7s (KO)

Details:
- NHibernate version is 3.2.0.
- The execution times are the same for both attribute and XML mapping.
- The query is a simple SELECT query, with no join, and only equality comparisons.


Does anybody have any clue ?


Thanks in advance,
Elyes.

Hi again,

The Sybase DB server is Sybase 12, and we were hitting it with a Sybase 15 driver. Sorry for omitting this important info.

In the Sybase15 dialect, DbType.String is mapped to "nvarchar(255)".
Our properties were mapped to "DbType.String".

We used the IQuery.SetParameter() method and at runtime, that resolved the driver type to AseType.Univarchar (a Sybase15 data type), which in turn had to be converted from the actual Sybase12 type "varchar".


So as a summary:

• CLR : System.String
• NHibernate : DbType.String
• Sybase15 Dialect : nvarchar(255)
• Sybase15 Driver : univarchar (for some (weird?) reason)
• Sybase12 DB server : varchar

Why that takes 7 seconds for a single row is only known of the driver's developers.


Changing the column mapping to DbType.AnsiString solved the problem.


Thanks to the viewers ^^
Elyes.