Batched Searching With a JOIN

ruby-talk
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