Hi All,

I've searched and read through as many docs as I can find, but I can't find a clear answer on how to approach this issue.

I have a User entity that maps to a Registration Source entity (for tracking advertising partners). We experienced a huge traffic surge a few weeks ago and ran into major performance issues. Yesterday, I ran a load test with a DBA in a controlled environment and found a poorly performing query.

The problem I have is, the queryplan generated the sp_executesql does not cause SQL Server to complile a proper queryplan and it ignores its index(s) and therefore does a horrible table scan. If I run the query manually by taking away the sp_executesql, it uses its clustered indexes and performs magnificantly.

How should I try to tackle this? Is this something I should try to battle from the db side? Not sure how much luck I will have with this w/out writing a stored proc. Can I get NHIbernate to hint at what index it should use? should I try to run a sql query manually from nhibernate?

thanks in advance for your insight, details below:

Hibernate version: 1.2.1.GA

Mappings:
User:
<hibernate-mapping auto-import="true" default-lazy="false" assembly="EatBetterAmericaLib" namespace="GeneralMills.GMIApplications.GMIInternetSites.EatBetterAmerica" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.2">
<class name="User" table="TUSER" schema="dbo" dynamic-insert="true" dynamic-update="true">
<id name="Id" access="property" column="user_nbr" type="Int32" unsaved-value="0">
<generator class="native">
</generator>
</id>
<property name="EulaAccepted" access="property" type="DateTime">
<column name="eula_accepted_ts"/>
</property>
<property name="BirthDate" access="property" type="DateTime">
<column name="birth_dt"/>
</property>
<property name="Created" access="property" type="DateTime" generated="insert">
<column name="created_ts"/>
</property>
<property name="LastModified" access="property" type="DateTime" generated="insert">
<column name="last_modified_ts"/>
</property>
<property name="segment_profile_nbr" access="property" type="Int32">
<column name="segment_profile_nbr"/>
</property>
<property name="PostalCode" access="property" type="String">
<column name="zip_cd"/>
</property>
<property name="LastLogon" access="property" type="DateTime">
<column name="last_logon_ts"/>
</property>
<property name="DailyVisitCount" access="property" type="Int32">
<column name="daily_visit_qty"/>
</property>
<property name="City" access="property" type="String">
<column name="city_nm"/>
</property>
<property name="State" access="property" type="String">
<column name="state_cd"/>
</property>
<property name="Salutation" access="property" type="String">
<column name="salutation_cd"/>
</property>
<property name="_encryptedPassword" access="field" type="String">
<column name="password_txt"/>
</property>
<property name="Address1" access="property" type="String">
<column name="street_addr"/>
</property>
<property name="Address2" access="property" type="String">
<column name="street2_addr"/>
</property>
<property name="FirstName" access="property" type="String">
<column name="first_nm"/>
</property>
<property name="LastName" access="property" type="String">
<column name="last_nm"/>
</property>
<property name="ScreenName" access="property" type="String">
<column name="screen_nm"/>
</property>
<property name="Email" access="property" type="String">
<column name="email_addr"/>
</property>
<property name="Administrator" access="property" type="Boolean">
<column name="administrator_fg"/>
</property>
<property name="SurveyComplete" access="property" type="Boolean">
<column name="survey_complete_fg"/>
</property>
<many-to-one name="UserRegistrationSource" access="property" class="RegistrationSource" column="registration_source_nbr" />
<bag name="ArticleList" access="property" table="TUSER_ARTICLE" lazy="true">
<key column="user_nbr" />
<many-to-many class="Article">
<column name="article_nbr" not-null="true"/>
</many-to-many>
</bag>
<bag name="ExpertAnswerList" access="property" table="TUSER_EXPERT_ANSWER" lazy="true">
<key column="user_nbr" />
<many-to-many class="ExpertAnswer">
<column name="expert_answer_nbr" not-null="true"/>
</many-to-many>
</bag>
<bag name="RecipeList" access="property" table="TUSER_RECIPE" lazy="true">
<key column="user_nbr" />
<many-to-many class="Recipe">
<column name="recipe_nbr" not-null="true"/>
</many-to-many>
</bag>
<bag name="RecipeSummaryItemList" access="property" table="TUSER_RECIPE" lazy="true">
<key column="user_nbr" />
<many-to-many class="RecipeSummaryItem">
<column name="recipe_nbr" not-null="true"/>
</many-to-many>
</bag>
<bag name="PersonalGoalList" access="property" table="TUSER_PERSONAL_GOAL" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="user_nbr" />
<one-to-many class="PersonalGoal"/>
</bag>
<idbag name="ProfileAnswerList" access="property" table="TUSER_PROFILE_ANSWER" lazy="true" >
<collection-id column="user_profile_answer_nbr" type="Int32">
<generator class="identity"/>
</collection-id>
<key column="user_nbr" />
<many-to-many class="ProfileAnswer">
<column name="profile_answer_nbr" not-null="true"/>
</many-to-many>
</idbag>
<bag name="RecipeReviewList" access="property" table="TREVIEW" inverse="true" lazy="true">
<key column="user_nbr" foreign-key="user_cd" />
<one-to-many class="RecipeReview"/>
</bag>
<bag name="ChildList" access="property" table="TUSER_CHILD" lazy="true" inverse="true" cascade="all-delete-orphan">
<key column="user_nbr" />
<one-to-many class="UserChild"/>
</bag>
</class>
</hibernate-mapping>

Registration Source:
<hibernate-mapping auto-import="true" default-lazy="false" assembly="EatBetterAmericaLib" namespace="GeneralMills.GMIApplications.GMIInternetSites.EatBetterAmerica" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:nhibernate-mapping-2.2">
<class name="RegistrationSource" table="TREGISTRATION_SOURCE" schema="dbo">
<id name="Id" access="property" column="registration_source_nbr" type="Int32" unsaved-value="0">
<generator class="increment">
</generator>
</id>
<property name="Name" access="property" type="String">
<column name="source_nm"/>
</property>
<property name="Type" access="property" type="String">
<column name="registration_source_type_cd"/>
</property>
</class>
</hibernate-mapping>
[b]


[b]SQL Server 2000:

The generated SQL
exec sp_executesql N'SELECT this_.user_nbr as user1_46_1_, this_.eula_accepted_ts as eula2_46_1_, this_.birth_dt as birth3_46_1_, this_.created_ts as created4_46_1_, this_.last_modified_ts as last5_46_1_, this_.segment_profile_nbr as segment6_46_1_, this_.zip_cd as zip7_46_1_, this_.last_logon_ts as last8_46_1_, this_.daily_visit_qty as daily9_46_1_, this_.city_nm as city10_46_1_, this_.state_cd as state11_46_1_, this_.salutation_cd as salutation12_46_1_, this_.password_txt as password13_46_1_, this_.street_addr as street14_46_1_, this_.street2_addr as street15_46_1_, this_.first_nm as first16_46_1_, this_.last_nm as last17_46_1_, this_.screen_nm as screen18_46_1_, this_.email_addr as email19_46_1_, this_.administrator_fg as adminis20_46_1_, this_.survey_complete_fg as survey21_46_1_, this_.registration_source_nbr as registr22_46_1_, registrati2_.registration_source_nbr as registra1_45_0_, registrati2_.source_nm as source2_45_0_, registrati2_.registration_source_type_cd as registra3_45_0_ FROM dbo.TUSER this_ left outer join dbo.TREGISTRATION_SOURCE registrati2_ on this_.registration_source_nbr=registrati2_.registration_source_nbr WHERE this_.email_addr = @p0', N'@p0 nvarchar(21)', @p0 = N'[email protected]'