File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Win a copy of
The Mikado Method
this week in the
Agile and other Processes
Access database issue.
Joined: Jun 23, 2004
Mar 03, 2006 06:56:00
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
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.
Joined: Oct 11, 2004
Mar 03, 2006 08:29:00
You could try something like this:
--banned SELECT et.column1, et.column2, ... FROM excel_table et WHERE EXISTS (SELECT 1 FROM acces_table at WHERE et.phone_number = at.phone_number AND at.banned = true) --callable SELECT et.column1, et.column2, ... FROM excel_table et WHERE EXISTS (SELECT 1 FROM acces_table at WHERE et.phone_number = at.phone_number AND at.banned = false)
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
Joined: Jun 23, 2004
Mar 03, 2006 09:06:00
Thanks for your reply. I will try that.
I agree. Here's the link:
- it saves me about five hours per week
subject: Access database issue.
The XMLQuery syntax problem
Removing duplicate records
What is the best way to store List<List<String>> in database?
Simple field comparision
getting data from Excel
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2013