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.
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.
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.
Joined: Dec 15, 2004
let us say i have created the index. what next???
Joined: Aug 15, 2004
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.
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.
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.