SQL Server Performance Forum – Threads Archive

Search a name takes long time to query

i have a table which contains a text field and basically i have to check if a text or phrase exist in that text field. select count(*) from MYtable where TxtField like ‘%MYPHRASE%’ there are 700,000 records in that table and whenever i query it takes 9 seconds to give me the recordcount. what i am doing wrong

Your query can’t use an index on that column. So SQL Server has to scan the whole table to fulfill the request. With 700k rows 9 seconds doesn’t sound too bad to me. What do you expect? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

thanks FrankKalis for the reply.
when i search that text field i make a view and in that view i tried converting that text field to a nvarchar(2000) and still it gives the same performance ie 9 Seconds. I cant believe that to search a string it takes that much time with just 700k records
What happens when i have 7 million records?never search at all? Do you think that there are no ways mate?

i deleted half of the records and had just 350k records and still it gave
the same performance ie 9 seconds to search.
So your point of no of records not come into question???

Mismatch of filtering on an NVARCHAR field with a non-Unicode string will also deteriorate the response time. In any case, not sure whether 9 seconds is all that bad, really. And look up "full-text indexes" in BOL.

So, we’re talking about a TEXT column, no VARCHAR. In that case I would also consider Full-Text search. What performance do you expect? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

no all the characters are normal characters and not special german or norwegian characters

Yes, but you said you had changed the column to nvarchar(2000). SQL will filter that column for ‘%MYPHRASE%’ without a problem, but it does take more time because it has to implicitly convert that string to an nvarchar string, and this is known to slow down queries. Use this: SELECT COUNT(*) FROM MyTable
WHERE my_nvarchar_column LIKE N’%MyPhrase%’ … or this: DECLARE @Criteria NVARCHAR(100)
SET @Criteria = ‘%MyPhrase%’ SELECT COUNT(*) FROM MyTable
WHERE my_nvarchar_column LIKE @Criteria

N’%MyPhrase%’ That is a new thing which i have learnt from you. I never knew such a feature exists in SQL.
It didnt do any performance improvement i am afraid.
But thanks for your help mate.

My second suggestion might perform a little better. But still, 9 seconds is not too bad. Like we pointed out before, you might benefit from having a full-text index. And by the way, unless you’re an Australian, it isn’t considered polite to call people "mate".[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Thank you Adriaan,
I am Afraid I have Many Australian Friends working with me and get used to calling Mate.
Sorry for that.
Regards

Hi Vimal, Thanks for clearing up the Ozzie connection! It sounded a little weird coming from someone in India – or maybe it’s just me.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

]]>