Batched Searching With a JOIN

Ruby
1

I have two tables (IdmLdapAccounts and IdmAdAccounts) each with
20000-30000 records in them. What I’m trying to do is join these tables
so that I can find out what records from the LDAP table do NOT exist in
the AD table.

uid is indexed in the LDAP table and samaccountname is indexed in the AD
table.

I tried using some LEFT OUTER JOINs…but couldn’t figure out how to
get that to work with the Model.find_each that does batched queries.

So this is what I do now…it takes about 25 seconds to run…and
that’s using SQLPlus in my dev environment. I would think it would be
faster when I move to a real MySQL db.

accounts = []
IdmLdapAccount.find_each do |ldap_account|
  if !IdmAdAccount.exists?(:samaccountname => ldap_account.uid)
    accounts << ldap_account
  end
end
ap accounts.size
exit

This produces the result I want, but I don’t know if it’s the best way
to do this. This queries the AD table once for every row in the LDAP
table (about 23000 single queries)…it’s pretty quick…but seems
inefficient. Wouldn’t a true JOIN work better?

Any thoughts?

Thanks,
Matt