Sql query working very slow.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi Expert,

There 2 tables emailinfo(id,email) and keywordinfo(id,keyword).
emailinfo contains 80,000 rows and keywordinfo contains 2000 rows.

I want emails from emailinfo table which does not contains keywords from keywordinfo table.

And my query is like following.

SQL

SELECT  EMAIL.email FROM emailinfo EMAIL    WHERE   (  not exists( Select keyword from keywordinfo where EMAIL.email  like '%'+ keyword +'%' ))

but it takes 3 minutes to execute .

please help me to slove this problem.

Thanks in advance.

Posted 11-May-12 2:01am

udusat13

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-05-11T02:47:00

Solution 2

I want emails from emailinfo table which does not contains keywords from keywordinfo table.

SQL

SELECT [email] FROM emailinfo WHERE NOT [email] IN(SELECT [keyword] AS [email] FROM keywordinfo WHERE [keyword] like '%'+ keyword +'%' )

Posted 11-May-12 2:47am

Maciej Los

Comments

udusat13 14-May-12 0:40am

Thanks for replying me,
but your above query retrives all the emails from emailinfo table.
i have added yahoo keyword in keywordinfo table,but still it retrieveing email that contains yahoo keyword.

Please help me.

phil.o · Answer 2 · 2012-05-11T02:48:00

Solution 3

SQL

WITH keywords AS
(
  SELECT
    keyword
  FROM
    keywordinfo
)

SELECT
  emailinfo.email
FROM
  emailinfo
WHERE
  emailinfo.email NOT LIKE '%' + keywords.keyword + '%'

Posted 11-May-12 2:48am

phil.o

Updated 13-May-12 20:32pm

v2

Comments

Maciej Los 11-May-12 8:50am

Please, do not multiply answers. Please, move it into previous answer.

phil.o 11-May-12 8:53am

It is a radically different solution as the 1st one, so no, sorry, I won't move it.

udusat13 14-May-12 0:41am

Thanks for solution.

but it gives error like

The multi-part identifier "emailinfo.email" could not be bound.
The multi-part identifier "keywords.keyword" could not be bound.

phil.o 14-May-12 2:33am

Hi, I had forgotten the FROM part of the query.
Please try again with it included.

udusat13 14-May-12 2:40am

thanks but i am confused,
because i am new to sql server

Bernhard Hiller · Answer 3 · 2012-05-11T02:51:00

That query will always take a long time. You need a different design for your solution: When a new email arrives, check it for the keywords, and add new entries to a linking table containing emailid and keywordid. The time required for doing that extra step at email arrival does not matter. When you search later on, no full text search is required anymore.

phil.o · Answer 4 · 2012-05-11T02:05:00

Solution 1

Make sure there is an index on your email column.

Posted 11-May-12 2:05am

phil.o

Comments

udusat13 11-May-12 8:14am

Thanks for replying me.
I have already indexed email column from emailinfo table and keyword column from keyword info table.

Please check the query is right or not?
Thanks again.

phil.o 11-May-12 8:46am

Your query does not seem really correct (syntacticaly speaking) to me : in your nested query (SELECT keyword FROM keywordinfo WHERE emailinfo...), you query the keywordinfo table but take a condition on the emailinfo table.
I'll try to post a solution using a CTE as soon as I can.