4

I'm trying to make a detailed search with asp and SQL Server Full-text.

When a keyword submitted, I need to search in multiple tables. For example,

Table - Members

  • member_id
  • contact_name

Table - Education

  • member_id
  • school_name

My query;

select mem.member_id, mem.contact_name, edu.member_id, edu.school_name from Members mem FULL OUTER JOIN Education edu on edu.member_id=mem.member_id where CONTAINS (mem.contact_name, '""*"&keyword&"*""') or CONTAINS (edu.school_name, '""*"&keyword&"*""') order by mem.member_id desc;

This query works but it takes really long time to execute.

Image that the keyword is Phill; If mem.contact_name matches then list it, or if edu.school_name matches, list the ones whose education match the keyword.

I hope I could explain well :) Sorry for my english though.

Burak F. Kilicaslan
  • 535
  • 2
  • 8
  • 20
  • By the way, I can't use Union because fields of members and education are not the same. I need to search also in contact_address on Members table. – Burak F. Kilicaslan Dec 21 '09 at 08:35
  • You might find the answers to this question useful: http://stackoverflow.com/questions/2063561/sql-server-full-text-search-create-one-computed-column . – Vince Bowdren Jan 21 '10 at 13:00

3 Answers3

2

Perhaps try an indexed view containing the merged dataset- you can add the fulltext index there instead of the individual tables, and it's further extensible to as many tables as you need down the line. Only trick, of course, is the space...

nitzmahone
  • 13,720
  • 2
  • 36
  • 39
1

This is what I would do for my multi table full text search.

Not exact but it will give basic idea. the key thing is to give table vise contain with OR condition.

DECLARE  @SearchTerm NVARCHAR(250)
    
SET @SearchTerm = '"Texas*"'

SELECT * FROM table1 
JOIN table2 on table1.Id = table2.FKID
WHERE (
        (@SearchTerm = '""') OR 
        CONTAINS((table1.column1, table1.column2, table1.column3), @SearchTerm) OR 
        CONTAINS((table2.column1, table2.column2), @SearchTerm)
      )
Bharat
  • 5,869
  • 4
  • 38
  • 58
0

Couple of points I don't understand that will be affecting your speed.

  1. Do you really need a full outer join? That's killing you. It looks like these tables are one to one. In that case can't you make it an inner join?

  2. Can't you pass a column list to contains like so: 

    SELECT mem.member_id,
     mem.contact_name,
     edu.member_id,
     edu.school_name
FROM members mem
    INNER JOIN education edu ON edu.member_id = mem.member_id
WHERE Contains((mem.contact_name,edu.school_name),'*keyword*')
ORDER BY mem.member_id DESC

Further info about contains.

jimconstable
  • 2,338
  • 18
  • 28
  • 6
    Sql Server is incapable of prefix matching, so only 'keyword*' is useful. Also: `Msg 7646, Level 16, State 1, Line 7 Fulltext predicate references columns from two different tables or indexed views 'mem' and 'edu' which is not allowed.` – Amit Naidu Sep 14 '13 at 16:12