optimization of database

hi

i have an access database that has a table with around 220000 records. the table has a date field. i want to delete all records more than 6 months old from the table. the execution of this query takes an awful lot of time. can anybody suggest a better method to do the same which will reduce the time taken? i am using a simple 'delete' statement for this.

-Anandh

you can create index on that column. if not.
or
assuming that your table primary key would be auto-number. you can generate your own primary key via code based on date format. and change the datatype to number rather than auto-number. this way you can apply the condition on your primary key. it will definitely improve your performance.
i.e.

YYYYMMDDHHMISSm

where,

m is your milliseconds
SS is your seconds
MI is your minutes
HH is your hours
DD is your day
MM is your month
YYYY is your year

Tip: we discourage including a date field in our search conditions.

cheers

let us say i have created the index. what next???

-Anandh

ok if doesn't affect much then try the other option i have mentioned. or switch to ORACLE or DB2. or wait for others suggestions.

thanks

Moving to database server is the best you could do

.
Microsoft has its own server MSSQL, there is also free version called Microsoft Desktop Engine,I guess.

i have an access database that has a table with around 220000 records

Wooft! Didn't read that bit. Yes, change databases - I'm amazed this doesn't fall over all the time/take forever to startup. My one-and-only experience with a large table in Access (through a thoughly third-rate piece of bug track software) ended up with us seeing inserts take half an hour. And there were only ever a maximum of six users connecting to the DB.

hi

i am not authorised to change the databse. i have been assigned the task of taking a backup. that's it. i'll tell you another issue here. i don't have the rights to save new queries or tables in the database. i have to execute the query of creating the index every time. and it takes a lot of time to execute. please suggest some other method to optimize it.