February 6, 2012 at 5:35 pm
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?
February 7, 2012 at 12:38 am
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
February 7, 2012 at 1:30 am
Or configure your source comnection to use a query and bring in the name column there - that's probably what I would do.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 7, 2012 at 10:05 am
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.
February 7, 2012 at 1:48 pm
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
February 7, 2012 at 2:36 pm
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.
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
February 7, 2012 at 2:43 pm
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