thanks for response

i think that here is nolock needed, because this data are live, you know, this select will be every second and i work in Car company, data are still moving.

subbareddy542 - i try read this, but i post more information

Here is the select,

- so Tracking you know, there are data still change, there is just 7rows (position )

- TB_reinput are commit_no(sequences - 0001,0002-1230 etc.), which were canceled, we have table, where we write this

- so and if i want check i need to compare but there was problem because you have to compare both of them ( ORDER_DATE+COMMIT_NO), becuase commit_no are every day same. 0001-xxxx ...

if I start select without compare with TB_REINPUT is fast

SELECT STATION_ID, LAST_COMMIT_NO AS COMMIT_NO

FROM MCS_MESDB.dbo.TB_TRACKING_AGV TR (NOLOCK)

WHERE ORDER_DATE+COMMIT_NO NOT IN

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

TABLE reinput has just 759 rows and we add to this sometimes, but condition is just for specify day - last day , i have rebuild index for every saturday if the fragmentation is higher than 20 or 30 i am not sure.

here is index for TB_REINPUT

ALTER TABLE [dbo].[TB_REINPUT] ADD CONSTRAINT [PK_TB_REINPUT] PRIMARY KEY CLUSTERED

(

[ORDER_DATE] ASC,

[COMMIT_NO] ASC,

[LINE_CODE] ASC

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

GO

So i need to get faster solution if its possible.

I need get just select of 7 rows, first column is station-position and second column is the commit_no, so and if i have some commit_no+order_date inside, i dont want to select this in result . do you understand?

SSC Eights!

I tried your solution and the in execution plan were same

and i have idea, how can i say to sql, that it must scan and not seek?? any hint with specification ??

in attachment is image with execution plan , you can see with and without. and i dont know, if not be better scan than seek in small table like this.