anthony.green Here is TB_REINPUT, so i think that not will be problem in second table . If you check the image with sqlplan, there is 77% with seek in TB_REINPUT.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TB_REINPUT](

[ORDER_DATE] [char](8) NOT NULL,

[COMMIT_NO] [char](4) NOT NULL,

[LINE_CODE] [char](5) NOT NULL,

[CREATE_BY] [nvarchar](10) NULL,

[CREATE_DTTM] [datetime] NULL,

CONSTRAINT [PK_TB_REINPUT] PRIMARY KEY CLUSTERED

(

[ORDER_DATE] ASC,

[COMMIT_NO] ASC,

[LINE_CODE] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

here is almost same query, but with join another table for check not LAST_COMMIT but just COMMIT and this is faster than query before in sql plan. like estimated query and % are 53 vs 43 for this second query,, but in c# program the second little froze program, and first no/

SELECT STATION_ID,

(

SELECT TOP 1 COMMIT_NO

FROM MCS_MESDB.dbo.TB_WORK_ORDER_AGVEN WO

WHERE TR.LAST_ORDER_DATE + TR.LAST_COMMIT_NO < WO.ORDER_DATE + WO.COMMIT_NO

AND DATA_TYPE <> 'SD'

AND WO.ORDER_DATE + WO.COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM MCS_MESDB.dbo.TB_REINPUT WHERE ORDER_DATE >= TR.LAST_ORDER_DATE AND LINE_CODE= TR.LINE_CODE)

ORDER BY ORDER_DATE ASC, COMMIT_NO ASC

) COMMIT_NO

FROM MCS_MESDB.dbo.TB_TRACKING_AGV TR (NOLOCK)

and statistic are from today and fragmentation is 14% in TB_REINPUT its ok ,

so it was idea, if not will be better scan all table than seek > do you know this hint ? how can i write or force the query use the index with hint scan ?

I hope that isnt confuse :]]