Performance Problem

[milinddev]

Hi All,

I had one problem there is one table which is having 3,000,000 rows.

Table is like this

empid pk,
create_date_time datetime,
dept

now my query is select TOP 1 empID, from emp with (nolock) where
dept = 'MNH' ORDER BY CREATE_DATE_TIME OPTION (FAST 1)

the query is executing very slow so any body can tell me how to improve the performance of the same.

[rmiao]

Do you have index on create_date_time datetime? If so, does system use it? YOu can check it by showing query execution plan.

[skhanal]

You should create an index in DEPT column too.

Or even better if you can cover the query with a composite index on all three columns

empid,
create_date_time,
dept

[milinddev]

I am having one index on the emp id which is the primary key how can i create the cluster index, i think i had to make a composite key for the same means pk for empid and create date time.

[rmiao]

You can just create index on create_date_time column.