May 21, 2009 at 2:32 am
Hi Guys
I have 2 tables User and UserProfile
UserId in UserProfile is an Foreign key Of User table
Data in UserTable
UserIdFirstnameLastNameEmail
1Ningaraj Nayakninga@yahoo.com
2mandirKumarman@xlindia.com
3DeepakKumarDeepu@gmail.com
4VijaySinghVijay@gmail.com
5MadhuSudhanMadhu@excelindia.com
6Venkiprathapvenki@yahoomail.com
Data in UserProfile
UserIdManagerName OfficeName HallTicketNumber EducationalLevel
1KrishnaPrakashSarasHAL0001BE(Computer Science)
2DeepakPegasusHAL0002BSIT
3VasuGuardinHAL0003BE(Mechanical)
4KrishnaPrakash SarasHAL0004MSIT
5SubbuESKLHAL0005BE(CS)
6ChandhruPegasusHAL0006MCA
When i give the search keyword as '%as%'
I need to get the list of Users whose Details matches the Search KeyWord
User Ningaraj also should come bcoz his managerName consists the search keyword '%as%'
I tried this one
SELECT UserId FROM User U INNER JOIN UserProfile UP ON U.UserId=UP.UserId
WHERE (U.Firstname LIKE '%as%' OR U.LastName LIKE '%as%' OR U.Email LIKE '%as%' OR
UP.ManagerName LIKE '%as%' OR UP.OfficeName LIKE '%as%' OR UP.HallTicketNumber LIKE '%as%' OR
UP.EducationalLevel LIKE '%as%')
but this Query is taking more duration ...
Is there any Logic not using OR operator
Please do the need ful thing
Thanx
Regards
Ningaraju
May 21, 2009 at 6:40 am
Your query can not use any index because you are using a wild card in the beginning of the string that you use to base your search on (and also because you are checking all columns), so if the tables are big, it will take some time to finish the search. Also why are you searching all the columns? If I want to find an employee that has the letters "as" in his first name, it doesn't mean that I'm looking for an employee with the letters "as" in his last name or him manager's name. One last thing - why do you have 2 tables? It seems to me that you should have this data in one table.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 23, 2009 at 12:40 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply