Look up VS Sql Query LEFT Join

  • Which is bettter in perfomance ,

    Look up or adding a left join to Sql query in Oledb source itself .

    i have 10 Look up to be used in a single package each around 50000 rows

  • I would say LEFT JOIN, but it depends on the volumes and indexes of the tables you are joining.

    With lookups, you issue one query for each row, with the join you leave to the db engine the task of choosing the best plan to "lookup" the rows. It knows better how to handle data, it is designed for that.

    -- Gianluca Sartori

  • If you can use a LEFT JOIN then I'd suggest that at is a set-based operation, while the Lookup Component is RBAR. Granted SSIS is designed to be fast with RBAR processing, but I'd bet the LEFT JOIN will greatly improve performance of the package.

  • Also be aware that SSIS lookup is by default case-sensitive , there are work-arounds available, So you may not get the same results as a Left-Join

  • I agree that generally the LEFT JOIN is the better performing option. You'll want to consider using the SSIS lookup in cases where you want to do some alternative processing to your unmatched rows, such as sending them to an error queue or output file.

    hth,

    Tim

  • steveb. (9/25/2009)


    Also be aware that SSIS lookup is by default case-sensitive , there are work-arounds available, So you may not get the same results as a Left-Join

    Great point about case-sensitivity. I was burned by that in the past.

  • I agree. Getting the data from the OLE DB source component here may be better. Typically, the source data is comming from a seperate system then where you are doing the lookups so you would need to use the Lookup Transformations to look up values in the destination system based on values from the source system.

    In the case where you can get all of the data you need in one shot, go for it! It will, generally speaking, perform better, the package will be easier to maintain and less complex, and you'll not have to watch out for the gotcha's that come along with the ETL process.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply