Lookup Vs oleDBCommand

  • I have a name column coming from my source. I need to get the ID for each name. The table in which the name and ID columns are stored, has over 1 mil records. Which is the best way to do this?

  • Khades (2/6/2012)


    I have a name column coming from my source. I need to get the ID for each name. The table in which the name and ID columns are stored, has over 1 mil records. Which is the best way to do this?

    Lookup, definately.

    The OLE DB command will launch a seperate query for each row, which is incredibly slow.

    Configure the Lookup with a SQL query that fetches only the name and the ID from the reference table (do not use the dropdown menu!!!).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Or configure your source comnection to use a query and bring in the name column there - that's probably what I would do.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Koen Verbeeck (2/7/2012)


    Khades (2/6/2012)


    I have a name column coming from my source. I need to get the ID for each name. The table in which the name and ID columns are stored, has over 1 mil records. Which is the best way to do this?

    Lookup, definately.

    The OLE DB command will launch a seperate query for each row, which is incredibly slow.

    Configure the Lookup with a SQL query that fetches only the name and the ID from the reference table (do not use the dropdown menu!!!).

    The ID column is an INT (and primary key). The name column is VARCHAR(50). I can narrow down the records to about 250,000. Should I be using full Cache?

    Phil Parkin (2/7/2012)


    Or configure your source comnection to use a query and bring in the name column there - that's probably what I would do.

    That's not really an option, the name is coming from an XML file, the ID is in the database.

  • Khades (2/7/2012)


    Koen Verbeeck (2/7/2012)


    Khades (2/6/2012)


    I have a name column coming from my source. I need to get the ID for each name. The table in which the name and ID columns are stored, has over 1 mil records. Which is the best way to do this?

    Lookup, definately.

    The OLE DB command will launch a seperate query for each row, which is incredibly slow.

    Configure the Lookup with a SQL query that fetches only the name and the ID from the reference table (do not use the dropdown menu!!!).

    The ID column is an INT (and primary key). The name column is VARCHAR(50). I can narrow down the records to about 250,000. Should I be using full Cache?

    Yes. (50 bytes + 4 bytes) * 250000 = 13500000 bytes = 13.500000 megabytes of memory. I think your server can handle that 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Be aware that lookup is case sensitive. You may need to use a wrapper on your columns to turn them all to UPPER (I usually use a derived column so I don't adjust original data) so that you can avoid Case Sensitivity concerns.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (2/7/2012)


    Be aware that lookup is case sensitive. You may need to use a wrapper on your columns to turn them all to UPPER (I usually use a derived column so I don't adjust original data) so that you can avoid Case Sensitivity concerns.

    And to complete Craigs remark: also watch out for trailing spaces! (the Lookup component doesn't ignore them like SQL Server does)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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