Batched Searching With a JOIN

Discussion in 'Ruby' started by Matt Mencel, Apr 8, 2010.

  1. Matt Mencel

    Matt Mencel Guest

    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
    Matt Mencel, Apr 8, 2010
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Todd Anderson

    join('')

    Todd Anderson, Sep 11, 2003, in forum: Perl
    Replies:
    1
    Views:
    629
    Kris Wempa
    Sep 12, 2003
  2. laurent
    Replies:
    7
    Views:
    1,919
    Joe Smith
    May 27, 2004
  3. Alan Silver
    Replies:
    0
    Views:
    871
    Alan Silver
    Jun 5, 2006
  4. googleboy
    Replies:
    1
    Views:
    908
    Benji York
    Oct 1, 2005
  5. stumblng.tumblr
    Replies:
    1
    Views:
    191
    stumblng.tumblr
    Feb 4, 2008
Loading...

Share This Page