Search with Full Text Index vs "normal" search

  • Hello,

    i need your help because i am feeling bad with Full text index.

    I need to do a search for clients, so i have implemented a full text index on my table "b_utentes".

    My code is fine, is working very well. But, someone ask me to do a code without use of full text...ok, i do it.

    What i don't understand is the behavior  of code, if i include Execution Plan i see that Full Text Index is not the best!

    Why?!  (My table have just 500 rows, peraphs is because of that?)


    1. Search with full text index (Execution plan Query cost relative to the batch 40% ):

    SELECT * from b_utentes

    WHERE (

    CONTAINS((no_str, ncont, nbenef, nbenef2, tlmvl, tlmvl_seq, telefone_seq, nrss, codpost, no_ext, bino), '"500530769*"')


    CONTAINS((nome, local, morada, telefone, obs, codpost, nrcartao), '"*silva*"')


    and inactivo = 0

    2. Search with normal (v1) (Execution plan Query cost relative to the batch 32% )

    SELECT * from b_utentes


    (no_str like '500530769%' OR ncont like '500530769%'

    OR nbenef like '500530769%' OR nbenef2 like '500530769%' OR tlmvl like '500530769%'

    OR tlmvl_seq like '500530769%' OR telefone_seq like '500530769%' OR nrss like '500530769%'

    OR codpost like '500530769%' OR no_ext like '500530769%' OR bino like '500530769%')


    (nome like '%silva%' OR local like '%silva%' OR morada like '%silva%' OR telefone like '%silva%'

    OR obs like '%silva%' OR codpost like '%silva%' OR nrcartao like '%silva%'


    AND inactivo = 0

    3. Search with normal (v2) (Execution plan Query cost relative to the batch 29% )

    SELECT * from b_utentes


    (no_str +' / '+ ncont +' / '+ nbenef +' / '+ nbenef2+' / '+ tlmvl +

    ' / '+ tlmvl_seq +' / '+ telefone_seq +' / '+nrss+' / '+codpost+

    ' / '+ no_ext+' / '+ bino) like '%500530769%'


    ((nome+' / '+local+' / '+morada+' / '+telefone+' / '+codpost+' / '+nrcartao) like '%silva%'

    OR obs like '%silva%'


    AND inactivo = 0


    Thank you for your advices,


  • You need to be aware that "Execution plan Query cost relative to the batch" is meaningless.  SQL is very often quite wrong on this number.  Just ignore it!

    You need to look at the estimated plan, or, far better, look at the actual execution plans, including io stats, to see which is really better.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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