aspose file tools*
The moose likes JDBC and the fly likes optimization of database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "optimization of database" Watch "optimization of database" New topic
Author

optimization of database

Anandh Ramesh
Ranch Hand

Joined: Dec 15, 2004
Posts: 61
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


cheers,<br />Anandh
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Dec 15, 2004
Posts: 61
let us say i have created the index. what next???

-Anandh
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Aug 04, 2004
Posts: 724
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.


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Anandh Ramesh
Ranch Hand

Joined: Dec 15, 2004
Posts: 61
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: optimization of database