Search a name takes long time to query | SQL Server Performance Forums

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 <i>second</i> 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.<br /><br />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,<br /><br />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=’:)‘ />]
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |