Search with Full Text Index vs "normal" search

  • jorge_gomes98

    SSC-Addicted

    Points: 436

    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*"')

    AND

    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

    WHERE

    (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%')

    AND

    (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

    WHERE

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

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

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

    AND

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

    OR obs like '%silva%'

    )

    AND inactivo = 0

     

    Thank you for your advices,

    JGomes

  • ScottPletcher

    SSC Guru

    Points: 98434

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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