Hi All,

I have one table with 200 milion records migrated. I have created nonclustered index(IX_ENTMCFDtl_ProcessLogID) on ProcessLogID. I am using NHibernate Criteria to retrive the record by processlogid.
E.g 1: select top 15 from ENTMCFDtl where processlogid = 1038
There are total 2200 records base on processlogid = 1038 but I only need to retrive 15 per paging.
above statement return took very long time(nearly 2 mins).

E.g 2 : select top 15 * from ENTMCFDtl with (index=3) where processlogid = 1038
this statement responsed very fast (withing a second).

I don't know how to add with (index=3) in NHibernate.
My question is that where should i force to use IX_ENTMCFDtl_ProcessLogID index. Here is my HBM relation table structure. Please HELP.

<many-to-one name="ProcessLog" index="IX_ENTMCFDtl_ProcessLogID " column="ProcessLogID" class="ProcessLog">
<meta attribute="read-only">false</meta>
<meta attribute="nullable">true</meta>
<meta attribute="value-type">false</meta>
</many-to-one>


CREATE TABLE [dbo].[ENTMCFDtl](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ProcessLogID] [bigint] NULL,
[LMerchantCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AcquirerCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CurrencyFXGroupCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeZoneCode] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

CONSTRAINT [PK_EntMCFDtl] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ENTMCFDtl] WITH CHECK ADD CONSTRAINT [FK_EntMCFDtl_ProcessLog] FOREIGN KEY([ProcessLogID])
REFERENCES [dbo].[ProcessLog] ([ID])
GO