CPU Usage

I have a website that has SQL Server Db as back end. I found that the CPU usage was more than normal. I found a SQL code to show most CPU consuming query I fixed some of them and it return noticeable amount of CPU Usage.

The only SQL code that I dont know how to get it optimized to not use too much CPU redources is this one:

SELECT top 5 [ID], [Title], [Details],[IPhoto] FROM [qryWords] WHERE lang=1 and id<> 59476  and [Title] like N'%no%' order by id desc

Note: qryWord is a view and the SQL code is as following:

SELECT        word, ID, Title, Details, Visible, Lang, IPhoto
FROM            dbo.tblBabeteLekchwekan
WHERE        (Visible = 1) AND (Lang = 1)

Do you have any Idea how can I tune this query to use as low as CPU resources?
LVL 6
YadtrtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
'%no%' filter condition seems to be the reason.  I know its always possible to avoid those sorts of searches, is highly recommended. Your option to speed up this operation will be the full text indexing.
also you don't really need lang=1 in your where condition because its already on the view .
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi,

>>  id<> 59476  
This search argument is non-sargable.
http://en.wikipedia.org/wiki/Sargable

HTH
  David
0
PortletPaulfreelancerCommented:
<> is sargable, but not likely to improve performance

so the most likely performance culprit is: [Title] like N'%no%'
which is probably causing a table scan

by the way. top 5 isn't helping either because it adds the need to order, and with '%no%' probably matching many more than 5 results all records are scanned to evaluate the filter then all matches sorted by id then just 5 of those returned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.