aspose file tools*
The moose likes JDBC and the fly likes Simple field comparision Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Simple field comparision" Watch "Simple field comparision" New topic
Author

Simple field comparision

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
I don't know if this is exactly the right place for this, I have two tables in access database, each has the same "phone" (text) field. Table 1 is the customer list and table 2 is the restricted numbers list. Restricted Table has like 1.25 million records. Customer Table has like 20 thousand records.

I need to filter the customer records based on the restricted numbers adn generate a collection of allowed customers.

so I am doing a

select * from Customers where phone not in (select phone from RestrictedList)

and it is taking ages.

What is a better approach for efficiency in this case? I guess if I do anything outside the database (meaning copy the records to datastructures and do my own comparison) it would further increase the time. Any thoughts on this?


regards,
Brian
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
I think the solution is to make sure you have an index on 'phone' in your RestrictedList, and pass the value down into the inner query so it will short-circuit right away. Something like:

select * from Customers c where c.phone not in (select r.phone from RestrictedList r where r.phone = c.phone)


Reid - SCJP2 (April 2002)
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Query tuning can sometimes seem like voodoo, and what works for one DB might not help in another.

Having correct indexes, certainly on phone in RestrictedList, and probably also on Customers, is going to be essential.

Different queries to try:
[yours]
select * from Customers where phone not in (select phone from RestrictedList)

[Reid's]
select * from Customers c where c.phone not in (select r.phone from RestrictedList r where r.phone = c.phone)

[others]
( http://www.devx.com/dbzone/Article/9570/0/page/3 )
select * from Customers c where not exists (select r.phone from RestrictedList r where r.phone = c.phone)

(not sure of the outer join syntax for Access, this is at least close)
select c.*
from Customers c
left join RestrictedList r on (c.phone = r.phone)
where r.phone is null

(if and only if RestrictedPhone has the same columns as Customers)
select * from Customers c
minus
select * from RestrictedPhone r

(this might be slower, not faster, but you won't know until you test
I also don't know if Access allows sub-selects this way)
select c.* from Customers c,
(select phone from Customers minus select phone from RestrictedPhone) x
where c.phone = x.phone
Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
Reid and Derby,

Thanks a lot for your response. I will try your suggestions. I am using a access database, so 'minus' operator is not an option.

regards,
Brian
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Simple field comparision