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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Access database issue." Watch "Access database issue." New topic
Author

Access database issue.

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
I don't know if this is the exact place for this q, but I am trying anyway.
I have a access database table of say a million and a half records. I have an excel file of say 25000 records.

the excel file has customer list with their phone numbers, the access table has do not call list of numbers.

I have to filter customer list for these do not call list and spit out two excel files, one clean file and the other with the filtered numbers.

Since the database what is the fastest method for this?

I am trying, in order to make the whole thing SQL, I am first copying the excel file as a table and doing kind of

select blah from blah where blah not in (select blah from ...)

Still it is slow.. Access really sucks. Am wondering if there are any better algorithms.


regards,
Brian
Balazs Borbely
Ranch Hand

Joined: Oct 11, 2004
Posts: 33
You could try something like this:


With the folowing preconditions.
- index on et.phone_number, at.phone_number, at.banned
- 'SELECT et.column1, et.column2, ... ' should have the minimum number of column required by application


PS. as a general rule EXISTS has a better performance than IN in most of the cases
[ March 03, 2006: Message edited by: Balazs Borbely ]

'Make everything as simple as possible, but not simpler.' --Albert Einstein
Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
Bolazs,

Thanks for your reply. I will try that.

regards,
Brian
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Access database issue.
 
Similar Threads
getting data from Excel
Simple field comparision
What is the best way to store List<List<String>> in database?
The XMLQuery syntax problem
Removing duplicate records