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.