Speed up NHibernate Calls to Oracle?

Are there any optimizations to consider when connecting to Oracle with NHibernate? My calls to Oracle are taking an extremely long time. If I run the NHibernate query (copied from my output window in Visual Studio) directly against Oracle, it comes right back in under a second. Here is my config file for NHibernate. I can post the mapping files if needed.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory name="DefaultSessionFactory">
        <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="dialect">NHibernate.Dialect.OracleDialect</property>
        <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
        <property name="connection.connection_string">Data Source=****;Persist Security Info=True;User ID=******;Password=*******;Unicode=True</property>
        <property name="show_sql">true</property>
        <mapping assembly="AQTool.BL"/>
    </session-factory>
</hibernate-configuration>

Update: summarizing all the discussion here:

If I remove all the many-to-one relationships and just run my unit test against the single entity with no joins, the query still runs for over 2 minutes. If I copy the sql generated by nhibernate and run it directly against oracle, the query returns in under a second. I am posting my mapping file here as well. Is there anything I am missing that might be causing this disparity? The only thing I am doing is selecting by Account Id. Although the table is large (over 5M rows), the customer account id field is indexed, and the raw query comes back very quickly.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="AQTool.BL" assembly="AQTool.BL" default-lazy="true">
    <class name="AQTool.BL.EmailAddress,AQTool.BL" table="EMAIL_CUSTOMER_ADDRESSES">
        <id name="EmailAddressId" column="EMAIL_ADDRESS_ID" type="int">
            <generator class="native" />
        </id>
        <property name="CustomerAccountId" column="CUSTOMER_ACCOUNT_ID" type="string" />
        <property name="EmailAddressText" column="EMAIL_ADDRESS_TX" type="string" />
        <property name="EmailAddressTypeId" column="EMAIL_ADDRESS_TYPE_ID" type="int" />
        <property name="EmailAddressTypeIdInternal" column="EMAIL_ADDRESS_TYPE_ID_INTERNAL" type="int" />

    </class>
</hibernate-mapping>

Answers


You may consider using this tool for "debugging NHibernate" NHibernate Query Analyzer


Need Your Help

java.util.logging.Logger to Logback using SLF4J?

java logging slf4j

Is it possible to have a typical call to java.util.logging.Logger and have it route to Logback using SLF4J? This would be nice since I wouldn't have to refactor the old jul code line by line.

How use CasperJS in a NodeJS application?

node.js phantomjs casperjs

I need run CasperJS commands in a NodeJS application. How do I do it?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.