Journey to SQL Authority

[Freamon]

You've not pushed it hard enough then
I hit a problem this morning whilst adding some lookups. I wanted to compare several columns in order to the lookup table - which itself is about 30 million rows.
SSIS fell over trying to cache the data and not caching took way to long.
Ended up having to move that functionality into SQL.

You had a lookup table that is 30 million rows? Sounds like a design issue. Couldn't you use a join instead?

[mudskipper]

You had a lookup table that is 30 million rows? Sounds like a design issue. Couldn't you use a join instead?

Sounds like a UK address table.

[Spacecadet]

You had a lookup table that is 30 million rows? Sounds like a design issue. Couldn't you use a join instead?

it's not and no... although i might give the join another go tonight, just thought of another way of doing it.
Thanks

Sounds like a UK address table.

bingo

problem with address data (at
least in this project) is that the incoming format is crap and doesn't join nicely to the PAF data

[EternalOptimist]

problem with address data (at
least in this project) is that the incoming format is crap and doesn't join nicely to the PAF data

I had this in a previous life. I built a neat pattern-matching tool to find likely dupes

ah, halcyon days

[eek]

bingo

problem with address data (at
least in this project) is that the incoming format is crap and doesn't join nicely to the PAF data

You'll have fun with that. Last time I did that I ended with a multi-stage process based on the accuracy of the individual records.

10% of it turned out to be total and utter crap which was surprising as they were all customers who had purchased goods via mail order.

[EternalOptimist]

You'll have fun with that. Last time I did that I ended with a multi-stage process based on the accuracy of the individual records.

10% of it turned out to be total and utter crap which was surprising as they were all customers who had purchased goods via mail order.

fuzzy logic. thats the answer, fuzzy logic

[eek]

fuzzy logic. thats the answer, fuzzy logic

Fuzzy Logic doesn't cover the example of postcode given for Main Street, address given as High Row (which has a different postcode).

[EternalOptimist]

Fuzzy Logic doesn't cover the example of postcode given for Main Street, address given as High Row (which has a different postcode).

yes it does

[Spacecadet]

You'll have fun with that. Last time I did that I ended with a multi-stage process based on the accuracy of the individual records.

10% of it turned out to be total and utter crap which was surprising as they were all customers who had purchased goods via mail order.

That's pretty much the conclusion I'd got to as well, filter out the good, the not so good, the passable and then ignore the rest!

[Freamon]

it's not and no... although i might give the join another go tonight, just thought of another way of doing it.
Thanks

A lookup on a 30 million row table is definitely not a good idea. Lookup is designed to read the whole table into memory when the job starts up, and then access the data in memory for each row that passes through. If you read 30 million rows into memory your job will swap to disk a lot. I'd suggest using a join is definitely the way to go. In a real ETL tool you could also partition your data by the first half of the postcode to improve the join performance.

bingo

problem with address data (at
least in this project) is that the incoming format is crap and doesn't join nicely to the PAF data

Yep, I think most people have been through this pain at some point. Especially when it's addresses that have been manually keyed in with no sense-checking at the point of entry. There's specialist data cleansing tools for address data which may save you a lot of pain. Dataflux does a good job of it (amongst other things).