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>
You may consider using this tool for "debugging NHibernate" NHibernate Query Analyzer
Make sure your connection pool is sufficiently large. Also, if you try debugging your code, you should be able to see where Hibernate is spending the majority of its time. It may be with processing the results, as someone commented earlier, especially if you have cascading relationships. If you do, try running those queries using subselect instead of lazy=false or fetch=join, as that will likely be significantly faster.