SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can this be optimized/done better?

    Bit of background:

    I have a product database, and now an external product database with extra information/etc... I need to match THEIR product database to MY product database.

    The external database has roughly 1,200,000 while my internal one contains 500,000.

    The way I do it now, is do it in batches of 20,000 from 0 to the highest product # which is 1 billion. I'm thinking this may not be the best way because there are many batches which will not contain 20,000 records.

    But in this example, $low and $high would be 20,000 apart.

    Code SQL:
    SELECT external.productid, external.mfgpartno, mfr_xref.internal_mfr_id
    FROM external_product AS external
    INNER JOIN mfr_xref ON mfr_xref.external_mfr_id = external.manufacturerid
    LEFT JOIN prod_xref ON prod_xref.external_product_id = external.productid
    WHERE
    	external.productid BETWEEN $low AND $high
    	AND prod_xref.external_product_id IS NULL

    Any ideas for optimizing maybe not only the query, but the process too?
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm not sure i understand the question

    "I'm thinking this may not be the best way"

    the best way for what? what are you trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I need to match THEIR products to MY products... that simply fetches out the ones that don't match, then I will see if I have a match based on the mfr part # (separate).

    Uses the cross reference table to select the rows that are NOT matched...
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    cross reference table??? not matched????

    sorry, even less clear now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there is a cross reference table containing THEIR product id and OUR product id.

    Grabbing the ones from THEIR product table that are not matched (THEIR product id does not exist in the cross reference table)
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    uh? so you have three tables...

    your_products
    | your_prodict_id | your stuff

    their_products
    | their_product_id | their stuff |

    cross_reference
    | your_product_id | their product_id |

    I don't know how to write the query but I think you should want to select those products in their table, which do not match their product_id in your cross_reference table.

    pseudo code..
    Code:
    select their_product_id
    from their_table
    WHERE their_product_id NOT IN 
    ( SELECT their_product_id 
      FROM cross_reference
    )
    hth

    bazz

  7. #7
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bazz, that logic is basically what im doing... if they aren't in the cross reference, they do not match... so I am left joining and only including where the match is NULL (no match)...
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  8. #8
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the NOT IN () is not as efficient as it could be. You could do an inner join ON id = NULL, that's the same as doing the NOT IN and I think it's faster

    SELECT
    t1.id
    FROM
    t1 LEFT OUTER JOIN t2 ON (t1.id = t2.id AND t2.id = NULL)

    this should return all of the records that don't match
    Last edited by Intaglio5; Dec 27, 2008 at 13:32.
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ON (t1.id = t2.id AND t2.id = NULL)
    no, sorry, that won't work

    if t2.ID is NULL, then it won't be equal to t1.id, ever

    that query will return 0 rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    erm, you're right, I meant to write LEFT OUTER JOIN, not INNER JOIN. Brain sneeze.
    SQL optimization, Data management, Back-end development
    http://brainfreezeanalytics.com
    info@brainfreezeanalytics.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •