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 Java 8 in Action this week in the Java 8 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
 
Similar Threads
database fail out of log space
Delete operation as small chunks on records?
How to delete more then one record in single query using iBATIS
displaying database records rowwise in the table
Removing duplicate records