cpu peaking to 100% when running this query

Hi!

When i run this query the cpu on the sql server 2005 box peaks up to 100% usage.

how can i optimize it?

Thanks a lot!

Tammy


select fileid  from table_keywords where subject like '%attorney%'
 
or (subject like '%licens%' and (subject not like '%end user%' or subject not like '%enduser%'))
 
or (subject like '%request%' and subject like '%for admission%')
 
or (subject like '%request%' and subject like '%for production%')
 
or subject like '%subpoena%'
 
or subject like '%lawful%'
 
or subject like '%affidavit%'
 
or (subject not like '%special%' and subject not like '%ciali%')
 
or subject like '%claim chart%'
 
or subject like '%claim construction%'
 
or subject like '%common defense%'
 
or subject like '%common interest%'
 
or subject like '%community interest%'
 
or subject like '%contention%'
 
or subject like '%council%'
 
or subject like '%court%'
 
or subject like '%atty%'
 
or subject like '%counsel%'
 
or subject like '%esq%'
 
or subject like '%illegal%'
 
or subject like '%law%@%'
 
or subject like '%@%law%%'
 
or subject like '%lawsuit%'
 
or subject like '%lawyer%'
 
or subject like '%legal assistant%'
 
or subject like '%legal department%'
 
or subject like '%litigat%'
 
or subject like '%paralegal%'
 
or subject like '%pleading%'
 
or subject like '%privileg%'
 
or (subject like '%depo%' and subject not like '%deposit%')
 
or (subject like '%legal%' and subject not like '%legal disclaimer%')
 
or subject like '%declaratory judgment%'
 
or subject like '%defend%'
 
or subject like '%defense%'
 
or subject like '%discovery order%'
 
or subject like '%docket no%'
 
or subject like '%docket number%'
 
or subject like '%due diligence%'
 
or subject like '%embod%'
 
or subject like '%evidence%'
 
or subject like '%highly confidential%'
 
or subject like '%impeach%'
 
or subject like '%indemni%'
 
or subject like '%infring%'
 
or subject like '%in-house%'
 
or subject like '%intellectual property%'
 
or subject like '%interrogator%'
 
or subject like '%invention%'
 
or subject like '%joint prosecutorial interest%'
 
or subject like '%joint defense%'
 
or subject like '%joint interest%'
 
or subject like '%Law dept%'
 
or subject like '%Law department%'
 
or subject like '%lawsuit%'
 
or subject like '%lawyer%'
 
or subject like '%liabilit%'
 
or subject like '%mediation%'
 
or subject like '%testifying%'
 
or subject like '%nontestifying%'
 
or subject like '%office action%'
 
or subject like '%opposition%' 
 
or subject like '%patent%'
 
or subject like '%plaintiff%'
 
or subject like '%prior art%'
 
or subject like '%priv%'
 
or subject like '%proceeding%'
 
or subject like '%prosecution%'
 
or subject like '%settlement%'
 
or subject like '%strictly confidential%'
 
or subject like '%sued%'
 
or subject like '%suing%'
 
or subject like '%testif%'
 
or subject like '%testimony%'
 
or subject like '%trade secret%'
 
or subject like '%validity%'
 
or subject like '%verdict%'
 
or subject like '%witness%'
 
or subject like '%work product%'

Open in new window

jtammygAsked:
Who is Participating?
 
Roger BaklundConnect With a Mentor Commented:
It would be time consuming to split all the subjects, but this is a job that you do once. When it is done, you can do fast queries for specific words within the subject. When adding a new subject, you would also need to split it and put new words in the "words" table. This should be normalized, every word should exist only once in the "words" table, and a "subject_words" table would connect words with subjects.

An alternative would be to use a full-text index for the subject column.

http://www.developer.com/db/article.php/3446891
http://msdn.microsoft.com/en-us/library/ms142571(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms345119.aspx
http://www.sql-server-performance.com/tips/full_text_search_p1.aspx
0
 
Roger BaklundCommented:
Line 15 seems wrong. Won't this return allmost all rows?
0
 
jtammygAuthor Commented:
that line should read

or (subject like '%agent%' and subject not like '%special%' and subject not like '%ciali%')

even taking that line the whole query takes 100% of cpu usage.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Roger BaklundCommented:
It is a wildcard search, it must scan the entire table. How many rows are there in the table? How long does it take before you get the answer? How many rows are returned? How long does it take if you only search for a single word, for instance only the first line of this query?
0
 
jtammygAuthor Commented:
cxr,

even if i run only the first line it spikes to 100% for a few seconds and then releases it.

the table can have a variable number of rows depending on the client. in this case it has 362499 records.

what about indexing the table on the subject field? would that make any difference?

thanks!
0
 
Roger BaklundCommented:
Indexing can help, try it. It won't be lightening fast though, it still needs to scan all index entries.

To make this really fast you need to split the subject field into separate words, and store the "subject words" in a separate table, and query with ... like 'word%', i.e. without a % prefix.
0
 
jtammygAuthor Commented:
there must be another way of doing this...it would be more time consuming to have to split every word from every subject. we have terabytes of data.

any other suggestions?

thanks!

0
 
jtammygAuthor Commented:
I will look into the full-text search and see how it works with our setup.

Thanks a lot for your help!

Tammy
0
All Courses

From novice to tech pro — start learning today.