I have a datasource with my raw data. I'm using a lookup to avoid doing a left outer join in the raw data query. The lookup and left outer join both are doing a between clause to another table. Here is what the lookup query is firing.

select * from (SELECT intTblGeoIPCountryID, intBeginningIPNo, intEndingIPNo FROM tblGeoIpCountry) as refTable where ? between [refTable].[intBeginningIPNo] and [refTable].[intEndingIPNo]

i've sorted the raw data and that is helping the speed some.. but it's still painfully slow. The tblGeoIPCountry table is not huge.. 100,000 rows. i'm thinking that there must be someway to script this out.. passing in both the raw data and having that go againt a record set of the geo data... but i'm not finding an example that that helps enough for me to muddle my way through. all those calls to to the db in the lookup.. just looking for a different way to do it. I looked at the merge join.. but can't see where i tell it to do the between.. anyway.. what are my options and does anyone have exampels

can anyone help
thanks
shannon

Not sure why you want to avoid the left outer join - that's probably the fastest way to accomplish this, given that you need to do a Between. The Lookup is really fast when you can use it for equi-joins in cached mode, but as soon as you disable caching, it's speed plummets.

Is there a reason you can't use the left outer join?

no.. there is no reason why i couldn't.. i was just looking to see if there was a way to improve on it. I had caching enabled..

thanks for the response