Post reply

Comparing list of words to field in table.

  • June 5, 2008 at 1:03 pm

    #187625

    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

    #825054

    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

    #825170

    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

    #826151

    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

    #826175

    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