June 5, 2008 at 1:03 pm
Hi, I have a table of key words (that change) that can appear in an address field to indicate whether the customer has an address that is only accessible by boat (probably a cottage). I would like run a query that looks at each address in the orders table and check to see if it contains any of the words in the 'LikeAddress' table ie 'bay' in 'Hudson Bay', I have included two mock tables and the query that I have compiled so far. The problem is that this is returning zero matches in addition it runs fast in this test query but against a large table it has very poor / slow performance.
thanks in advance for any help,
Marcus.
--Table to compare water access addresses
IF OBJECT_ID('TempDB..#WaterAccess') IS NOT NULL
DROP TABLE #WaterAccess
create table #WaterAccess
(LikeAddress varchar(max))
insert into #WaterAccess (LikeAddress)
select 'Bay' union all
select 'Island' union all
select 'Isle' union all
select 'Water' union all
select 'Lake' union all
select 'Inlet'
--Orders table addresses only
IF OBJECT_ID('TempDB..#orders') IS NOT NULL
DROP TABLE #orders
create table #orders
(Address varchar(max))
insert into #orders (Address)
select '987 ThunderBay' union all
select '234 Manitoulin Island' union all
select '945 Pelee Isle' union all
select '418 Water Access Only' union all
select '428 Lake Ontario' union all
select '915 Honey Inlet' union all
select '123 Main St' union all
select '456 Yonge St' union all
select '789 Broadway Ave'
declare @i int, @maxrow int;
select @maxrow = count(LikeAddress) from #WaterAccess;
set @i = 1;
IF OBJECT_ID('TempDB..#like') IS NOT NULL
DROP TABLE #like
select LikeAddress, row_number() over(order by LikeAddress) as rn
into #like from #WaterAccess
while @i <= @maxrow
begin
select Address from #orders
where
Address like
(select LikeAddress from #like where rn = @i)
set @i = @i +1
end
June 5, 2008 at 1:17 pm
First, the while loop makes this code RBAR (a Modenism for Row By Agonizing Row). Also, it looks like it may have to do a table scan for each path through the loop, which will make it take longer.
I recommend reading about full text indexing and using containstable to solve this problem.
😎
June 5, 2008 at 4:19 pm
I am very cautious about the implementation of Full Text Indexing. I have even more doubts when implementing it for such a narrow column, where a normal index could do the job for you. Depending on your system, you may already have an index on the street address anyway.
So for a solution that isn't RBAR, doesn't require Full Text Indexing, but will probably result in a table scan...
select address from #orders o
inner join (select '%' + LikeAddress + '%' as LikeAddress from #WaterAccess) wa
on o.address like wa.likeaddress
And pardon my laziness in prettying it up. 🙂
June 9, 2008 at 9:08 am
Thanks John, this solution worked well and was actually pretty simple, thanks for providing me with a different perspective.
June 9, 2008 at 9:49 am
Thanks for following up with the forums Marcus. Always good to see that suggestions have helped!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply