• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

optimization of database

 
Anandh Ramesh
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Anandh Ramesh
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
let us say i have created the index. what next???

-Anandh
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Anandh Ramesh
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic