March 26, 2020 at 5:32 pm
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?)
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
March 26, 2020 at 8:50 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply