Hi, I have a view that I use with NHibernate. Everything works fine, except it takes forever until it times out to return the count of the claims + the claims. When I execute the same generated SQL statement in SQL Management Studio, it takes less than a second to a second. Can someone explain to me why it's taking a long time until it timesout + the CPU gets maxed out?
Code:
SELECT TOP (100) PERCENT dbo.Claims.ClaimsKey, dbo.Claims.Sequence, dbo.Claims.ClaimsDate AS Date, dbo.Claims.CIN, dbo.ClaimsAmplifying.SubmitterID,
dbo.ClaimsAmplifying.ClientsClaimID AS ClaimID, dbo.ClaimsAmplifying.PatientFirstName AS FirstName, dbo.ClaimsAmplifying.PatientLastName AS LastName,
dbo.ClaimsAmplifying.SubscriberID, dbo.ClaimsAmplifying.DateOfServiceStart AS DOSStart, dbo.ClaimsAmplifying.DateOfServiceEnd AS DOSEnd,
dbo.ClaimsAmplifying.ClaimAmount AS Amount, dbo.ClaimsAmplifying.BillingProviderNPI AS BillingNPI,
dbo.ClaimsAmplifying.BillingProviderSecondaryID AS BillingSecondaryID, dbo.ClaimsAmplifying.BillingProviderSecondaryIDType AS BillingType,
dbo.ClaimsAmplifying.RenderingProviderNPI AS RenderingNPI, dbo.ClaimsAmplifying.RenderingProviderSecondaryID AS RenderingSecondaryID,
dbo.ClaimsAmplifying.RenderingProviderSecondaryIDType AS RenderingType, dbo.ClaimsAmplifying.ClaimsPayerName AS PayerName,
dbo.ClaimsAmplifying.ClaimsPayerIdentifier AS PayerID, dbo.ClaimsAmplifying.ClaimsPayerAddress1 AS Address1,
dbo.ClaimsAmplifying.ClaimsPayerAddress2 AS Address2,
dbo.ClaimsAmplifying.ClaimsPayerCity + ', ' + dbo.ClaimsAmplifying.ClaimsPayerState + ' ' + dbo.ClaimsAmplifying.ClaimsPayerZipCode AS CityStateZip,
dbo.ViewExternalPayersOnePayerID.ConnectionIdentifier AS ConnectionID, dbo.ViewExternalPayersOnePayerID.RoutingPayerID,
dbo.ClaimsAmplifying.ReceiverTraceID, dbo.Claims.BatchedClaimsKey AS BatchID, dbo.Claims.ClaimsStatus AS Status,
dbo.Claims.LastClaimsHistoryCode AS LastCode, dbo.ClaimsAmplifying.ClaimType
FROM dbo.Claims INNER JOIN
dbo.ClaimsAmplifying ON dbo.Claims.ClaimsKey = dbo.ClaimsAmplifying.ClaimsKey LEFT OUTER JOIN
dbo.ViewExternalPayersOnePayerID ON dbo.ClaimsAmplifying.ClaimsPayerIdentifier = dbo.ViewExternalPayersOnePayerID.PayerID
This is the Nhibernate Config:
Code:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="Inhouse">
<property name="dialect"> NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Server=srvr;initial catalog=Inhouse</property>
<property name="proxyfactory.factory_class">NHibernate.ByteCode.Castle.ProxyFactoryFactory, NHibernate.ByteCode.Castle</property>
<mapping assembly="BusinessLayer" />
</session-factory>
</hibernate-configuration>
ViewClass Mapping:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="BusinessLayer.Claims" assembly="BusinessLayer">
<class name="ViewClaims" table="View_ClaimsNew">
<id name="ID" column="ClaimsKey" type="Int32">
<generator class="native"/>
</id>
<property name="Date" column="Date" type="DateTime"/>
<property name="CIN" column="CIN" type="String"/>
<property name="SubmitterID" column="SubmitterID" type="String"/>
<property name="ClaimID" column="ClaimID" type="String"/>
<property name="LastName" column="LastName" type="String"/>
<property name="FirstName" column="FirstName" type="String"/>
<property name="SubscriberID" column="SubscriberID" type="String"/>
<property name="DOSStart" column="DOSStart" type="Date"/>
<property name="DOSEnd" column="DOSEnd" type="Date"/>
<property name="Amount" column="Amount" type="Decimal"/>
<property name="BillingNPI" column="BillingNPI" type="String"/>
<property name="BillingSecondaryID" column="BillingSecondaryID" type="String"/>
<property name="RenderingNPI" column="RenderingNPI" type="String"/>
<property name="RenderingSecondaryID" column="RenderingSecondaryID" type="String"/>
<property name="PayerName" column="PayerName" type="String"/>
<property name="PayerID" column="PayerID" type="String"/>
<property name="Address1" column="Address1" type="String"/>
<property name="Address2" column="Address2" type="String"/>
<property name="CityStateZip" column="CityStateZip" type="String"/>
<property name="ConnectionID" column="ConnectionID" type="String"/>
<property name="RoutingPayerID" column="RoutingPayerID" type="String"/>
<property name="ReceiverTraceID" column="ReceiverTraceID" type="String"/>
<property name="BatchID" column="BatchID" type="Int32"/>
<property name="Status" column="Status" type="String"/>
<property name="LastCode" column="LastCode" type="Int16"/>
<property name="ClaimType" column="ClaimType" type="String"/>
</class>
</hibernate-mapping>
This is the execution in SQL Profiler:
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
Code:
exec sp_executesql N'SELECT count(*) as y0_ FROM View_ClaimsNew this_ WHERE (this_.Date between @p0 and @p1 and (this_.PayerID like @p2 or this_.RoutingPayerID like @p3));
SELECT top 100 this_.ClaimsKey as ClaimsKey151_0_, this_.Date as Date151_0_, this_.CIN as CIN151_0_, this_.SubmitterID as Submitte4_151_0_, this_.ClaimID as ClaimID151_0_, this_.LastName as LastName151_0_, this_.FirstName as FirstName151_0_, this_.SubscriberID as Subscrib8_151_0_, this_.DOSStart as DOSStart151_0_, this_.DOSEnd as DOSEnd151_0_, this_.Amount as Amount151_0_, this_.BillingNPI as BillingNPI151_0_, this_.BillingSecondaryID as Billing13_151_0_, this_.RenderingNPI as Renderi14_151_0_, this_.RenderingSecondaryID as Renderi15_151_0_, this_.PayerName as PayerName151_0_, this_.PayerID as PayerID151_0_, this_.Address1 as Address18_151_0_, this_.Address2 as Address19_151_0_, this_.CityStateZip as CitySta20_151_0_, this_.ConnectionID as Connect21_151_0_, this_.RoutingPayerID as Routing22_151_0_, this_.ReceiverTraceID as Receive23_151_0_, this_.BatchID as BatchID151_0_, this_.Status as Status151_0_, this_.LastCode as LastCode151_0_, this_.ClaimType as ClaimType151_0_ FROM View_ClaimsNew this_ WHERE (this_.Date between @p4 and @p5 and (this_.PayerID like @p6 or this_.RoutingPayerID like @p7)) ORDER BY this_.ClaimsKey asc;
',N'@p0 datetime,@p1 datetime,@p2 nvarchar(6),@p3 nvarchar(6),@p4 datetime,@p5 datetime,@p6 nvarchar(6),@p7 nvarchar(6)',@p0='2009-10-01 00:00:00',@p1='2009-10-31 23:59:59',@p2=N'SMCO0%',@p3=N'SMCO0%',@p4='2009-10-01 00:00:00',@p5='2009-10-31 23:59:59',@p6=N'SMCO0%',@p7=N'SMCO0%'
Executing the same SQL Statement above in the management studio gives results in 1 second. Executing it through my application, takes about 40 seconds with the CPU maxed out to 100%. I checked all the indexes, they all seem to be fine. If I remove the routingPayerID, the query still longer but it actually returns something. If I don't search by anything, the query returns the results in 1s or 2s. Why is it under management studio works fine and using my app doesn't ?
Login
FAQ
Search