Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
donar
Starting Member
22 Posts |
 Posted - 2004-12-07 : 10:23:57
|
| I am now having a big trouble of designing a public web search.I am having 7 tables in the database having a common key ISN. In DOCUMENT table, isn and other fields are one to one relation, like document_no, document_type, title etc. in other tables, one ISN could have many relations with other fields, like author.Now I need to design a web search , it could be in any fields in these 7 tables.The only thing I can do is to join all these 7 tables together by ISN, and then search, but if you use "or" as the search conditon, it will take a long time, the performance is not acceptable.So I am thinking create a view, concatenate all the fields except ISN together, and then search in the one field. It also take long.Or try full text query, i have not tried it yet. Any one can give me some suggestions? I know SQL server is not good at this kind of things, it is sth. like library system, but I am now having to do it. So, please help! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 10:27:14
|
| Why join them at all? Why not search each table individually, gather the matching ISN's into a temp table or table variable, then join that table to the full results you need to provide? |
 |
|
|
donar
Starting Member
22 Posts |
Posted - 2004-12-07 : 10:49:43
|
| Because there are dozens of fields there, i do know which field , which table I should search. I have to search all the tables and search all the fields using a.field1 like '%sss%' or a.field2 like '%aaa%' or a.field3 like '%aaa%'..... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 11:36:44
|
| I'm saying, if ISN is in each table, you don't need to join all of them together to do one big search. Doing several searches of one table each will probably take less time, because the mechanics of the join would not be necessary. You'd only need to join these tables together when you present the final results, not when you're searching.Can you post the code you're currently using? |
|
|
|
donar
Starting Member
22 Posts |
Posted - 2004-12-07 : 12:12:44
|
| Sure!create view summary_1 ASselect a.isn, a.title + d.author + cast(a.doc_type as char(2))+ a.doc_number+a.pub_date+a.page_info+cast( a.number_of_maps as char(5))+ b.value as "summary"from dbo.document ajoin dbo.document_detail bon a.isn=b.isnjoin dbo.author don a.isn=d.isnselect distinct isn, summary from summary_1 where summary like '%drill%'The view now only joins 3 tables. In DOCUMENT table:{ ISN int 4 Indexer varchar 50 doc_number varchar 25 doc_type int 4 doc_subtype char 1 title_type int 4 title varchar 600 page_info varchar 15 number_of_maps int 4 status int 4 pub_date datetime 8 release_date smalldatetime 4 ISN_xref int 4 DOC_xref varchar 350 accession_number varchar 6 local_file_number varchar 125 RREF varchar 255 1 Notes varchar 750 1 operator varchar 50 1 create_dttm smalldatetime 4 modify_dttm smalldatetime 4 }In Author: (isn int, author_rank int, author_name varchar(50)), isn and rank together is uniquein Document_detail, (isn int,sequence int, value_type int, value varchar(50)), isn&sequence&value_type is uniqueThere are 4 more tables like author and document_detailThe search could be in DOCUMENT table's doc_number, doc_type, page_info, title, pub_dateor doucment_detail's value, or author's auhtor_name, u need to search all these tables, and all these fields and then say I can not find anything or u get the hit.Thanks!!! |
|
|
|
|
|
|
|
|
use an indexed view or full-text query?