I am working on a project, which includes a searching task by entering the string as a key for search. I have made a table which stores all the words which are entered by the user. I have written a function for separating the words from a string. Up until it works perfect.

I want to search the candidate which has the entered key.

Id - > int

UserID ->int

Word-> varchar(max)

Following are some recoders

ID        UserID                  Word

........................................开发者_StackOverflow中文版.......

1          29                        ASP.Net

2          29                        Java

3          29                        Manager

4          27                        Software

5          29                        Developer

Actually, when I obtain the searching string of key, I am separating every word and preparing a query by using "in".

Query

Select distinct(UserID) 
from dbo.Dictionary 
where UserID in (select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='ASP.NET') 
and UserID in (select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='SYSTEM') 
and UserID in (select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='MANAGER') 
and UserID in (select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='JAVA') 
and UserID in (select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='ASP.NET') 
and UserID in(select UserID 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='KAMLESH') 
and UserID in(select UserID from 
                 from dbo.Dictionary 
                 where rtrim(ltrim(upper(Word)))='PROJECT')

At some level of two or three keys it works perfectly but after that adding more keys, like 6 or more, it gives the error:

Msg 8623, Level 16, State 1, Line 1

The query processor ran out of internal resources and could not produce a  
query plan. This is a rare event and only expected for extremely complex 
queries or queries that reference a very large number of tables or partitions. 
Please simplify the query. If you believe you have received this message in
error, contact Customer Support Services for more information.

I want to know how to search the data (CandidateID) if a user enters a search key as a string, without it having an effect on the speed of the site.

---

You should be able to use an IN statement for the words aswell

Select distinct(UserID) 
from dbo.Dictionary 
where UserID in(select UserID  
               from dbo.Dictionary 
               where rtrim(ltrim(upper(Word)))IN  
         ('ASP.NET','SYSTEM','MANAGER','JAVA','ASP.NET','KAMLESH','PROJECT'))

EDIT due toi request:

See this example as a start.

DECLARE @Table TABLE(
        UserID INT,
        Words VARCHAR(10)
)

INSERT INTO @Table (UserID, Words) SELECT 1, 'A'
INSERT INTO @Table (UserID, Words) SELECT 2, 'B'
INSERT INTO @Table (UserID, Words) SELECT 3, 'C'
INSERT INTO @Table (UserID, Words) SELECT 3, 'C'
INSERT INTO @Table (UserID, Words) SELECT 1, 'B'
INSERT INTO @Table (UserID, Words) SELECT 1, 'C'

DECLARE @Keys TABLE(
        KeyVal VARCHAR(10)
)

INSERT INTO @Keys SELECT 'B'
INSERT INTO @Keys SELECT 'C'

SELECT  UserID
FROM    (
            SELECT  DISTINCT 
                    UserID,
                    Words
            FROM    @Table 
        ) DistinctVals INNER JOIN
        @Keys k ON DistinctVals.Words = k.KeyVal
GROUP BY UserID
HAVING COUNT(Words) = (SELECT COUNT(KeyVal) FROM @Keys)

---

SELECT (UserID) FROM 
 (SELECT UserID,
        GROUP_CONCAT(DISTINCT RTRIM(LTRIM(UPPER(Word))) ORDER BY Word) AS w                
   FROM dbo.Dictionary
   GROUP BY UserID) AS tmptable
WHERE w= '$querystring';

The syntax may need adjustment since I'm used to MySQL, but the idea is that your $querystring contain all the terms you require separated by commas.

---

You can try this:

DECLARE @Table TABLE
( 
        UserID INT, 
        Words VARCHAR(10) 
) 


INSERT INTO @Table (UserID, Words) SELECT 1, 'A' 
INSERT INTO @Table (UserID, Words) SELECT 2, 'C' 
INSERT INTO @Table (UserID, Words) SELECT 3, 'C' 
INSERT INTO @Table (UserID, Words) SELECT 2, 'A' 
INSERT INTO @Table (UserID, Words) SELECT 3, 'B' 
INSERT INTO @Table (UserID, Words) SELECT 1, 'C' 

SELECT T.UserID
  FROM (SELECT UserID, count(distinct Words) catCount
          FROM @Table
         WHERE Words IN ('A','C')
      GROUP BY UserID) T
 WHERE T.catCount = 2 --this number = the number of words passed to the IN operator.