Optimal indexes for these queries

[abdkad2007]

HI All,

I have the following queries are still running slow eventhough I added some indexes it didn't get the indexes and still giving me table scan and table delete.

Have the following 2 tables each have more then 20 million rows.


CREATE TABLE [dbo].[STATUS](
[NAME] [nvarchar](10) NOT NULL,
[TIME] [datetime] NOT NULL,
[POPULATION] [int] NULL,
[OLDEST_TIME] [datetime] NULL,
[COUNTRY] [nchar](2) NOT NULL,
[WAIT_TIME [decimal](8, 2) NULL,
PRIMARY KEY CLUSTERED
(
[TIME] ASC,
[COUNTRY] ASC,
[NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


first query gets the max time in all the table

Select max(time)as lastrundate
from status WITH(NOLOCK)

Second this is my issue her takes long time to run

DELETE FROM status WHERE time <=(
SELECT MAX( time )
FROM status WITH(NOLOCK)
WHERE time IN (
SELECT TOP time
FROM status WITH(NOLOCK)
WHERE time < '2009-06-16 12:31:59.877' ))


second table is my issue includes more then 40 millions rows without a primary key

CREATE TABLE [dbo].[FILES](
[NAME] [nvarchar](10) NOT NULL,
[TIMESTAMP] [datetime] NOT NULL,
[FILE_NAME] [nvarchar](200) NOT NULL,
[FILE_TIMESTAMP] [datetime] NOT NULL,
[COUNTRY [nchar](2) NULL,
[FILE_SIZE_BYTES] [int] NULL,
[FILES_COUNT] [int] NULL,
[WAIT_TIME] [decimal](8, 2) NULL
) ON [PRIMARY]

as in the above table is very slow running query

DELETE FROM files WHERE timestamp <=(
SELECT MAX( timestamp )
FROM files WITH(NOLOCK)
WHERE timestamp IN (
SELECT TOP 1000 timestamp
FROM files WITH(NOLOCK)
WHERE timestamp < '2009-06-15 05:06:11.200' ))



Thanks in advance for any suggestion and idea to have the optimal indexes on these tables;)