Tuning non sargable query

  • This is for SQL Server 2005Standard edition.

    I have a table that contains a column called address which typically has records like

    15 Hudson st

    902 Oakwood dr

    150 Tampa Ave

    The app has a search feature that lists records for a particular record. Now the application runs a SP in the DB which executes queries like

    SELECT * from Billtable where address like '%150%Tampa%Ave%'

    Or something like

    SELECT * from Billtable where address like '%Oakwood%Dr%'

    Is there any way I can make use of indexing? The table is getting larger every month and the queries obviously are taking longer time to run. I was wondering if I can use Full text indexing to improve performance.

    Blog
    http://saveadba.blogspot.com/

  • You could split the street number / address parts into separate computed columns and create indexes on those columns.

    This needs changes in the application, though.

    FTI seems to me a bit overkill in this context.

    -- Gianluca Sartori

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply