Build a web search application using the full-text search function of Microsoft SQL Server 2000
Andrew B. Cencini
Microsoft Corporation
December 2002
Suitable for: Microsoft® SQLTM Server 2000
Summary: Learn how to make full use of SQL Server 2000 full-text search function. This article contains some tips and techniques for achieving maximum throughput rates and optimal performance.
table of Contents
Introduction to the full text search function Configuration full text search function full text query rankings and other performance skills Skumber Appendix A: Implement full-text search function Best choice Appendix B: Use the best choice, result paging, and effective full-text query logic sample application appendix C: Resources
Introduction
With the full-text search feature of Microsoft® SQLTM Server 2000, you can perform fast and flexible queries to index generated on non-structured text data. Common full-text search tools are the search engine of the website. In order to help readers understand the best way of use of full-text search functions, this article describes a large number of abstract concepts; and provides a number of tips and techniques for optimizing full-text indexes and queries to achieve maximum throughput and optimal performance.
Introduction to the full-text search function
The full-text search function is introduced in SQL Server 7.0. This technology is also used in Microsoft Search (MSSearch) technology, Microsoft Exchange and Microsoft SharePointTM Portal Server.
SQL Server 7.0 full-text search features provide basic text search capabilities, and use earlier versions of MSSearch; and SQL Server 2000 full-text search implementation contains a set of reliable indexes and query functions, and found on SQL Server 7.0 A few enhancements have been added above. These enhancements include: Fully supporting cluster operations through Microsoft Cluster Services, filtering and indexing documents stored in Image columns, providing improved language support, and improvements in performance, scalability, and reliability.
MSSearch generation, maintenance, and query file system (not SQL Server) full-text index. The logic and physical storage units used by MSSearch for full-text index are directories. The full-text catalog contains one or more full-text indexes in each database - a full-text index can be created for each table in SQL Server, and the index can contain one or more columns in the table. Each table can only belong to a directory, and each table can only create an index. We will briefly introduce the best solutions for organizing full-text catalogs and indexes - but first, let us simply understand the working principle of the full-text search.
Configuring full-text search function
To create a full-text index for text data stored in SQL Server, you should first complete the following steps. The first step is to enable databases containing text data to generate indexes in full text (if you have not performed this).
note:
Perform the following statement will discard and recreate all full-text directories that belong to the database to enable full text search. Unless you want to recreate a full-text directory, make sure that you have not created any full-text catalog in a specific database to enable.
If you are a member of the sysadmin role or DB_OWNER of this database, you can continue and issue the following statement:
Use northwind
EXEC SP_FULLTEXT_DATABASE 'Enable'
Next, you need to create a full-text directory to store a full text index. As mentioned earlier, the data in this directory is stored in the file system (instead of SQL Server), so it should be carefully selected when considering the storage location of the full-text directory. Unless otherwise specified, the full-text directory stores in the subdirectory of the ftdata directory (located in the Microsoft SQL Server / MSSQL storage location). The following is a method for creating a full-text directory in a non-default location: EXEC SP_FULLTEXT_CATALOG 'CAT_DESC', 'CREATE', 'F: / FT'
In this example, the full-text catalog will create a subdirectory for "f: / ft". If you view this section of the file system, you will see it has your own directory. The naming rules of the full-text directory used by MSSearch are:
SQL DBID CATALOGID
The directory ID starts from 00005 and increments 1 every new directory.
If possible, it is best to create a full-text directory on its physical drive. If the process of generating a full-text index requires a lot of I / O operation (specifically, it is read from the SQL Server, and then writes the index to the file system), it should avoid making the I / O subsystem into a bottleneck.
So, how big is the full-text catalog? Typically, the system overhead of the full-text directory is approximately 30% higher than the data stored in SQL Server; however, this rule depends on the distribution of unique words (or primary keys) in the data, and by you It is deemed to be the distribution of words of interfering words. Interfering words (or stop words) refers to words to exclude in full text indexes and queries (because they are not your search for search words, and there is high frequency, so it will only make indexes very big, and there will be no actual effect). Later, we will introduce precautions for interfering words, and how to optimize interference words to improve query performance.
If you have not done this, create a unique single column non-empty index on the table to generate a full-text index. This unique index is used to map each line in the table to a unique compressible primary key used within MSSearch. Next, you need to let MSSearch know that you have to create a full-text index for the table. The following statement is issued to the table to the selected full-text directory (in this case, it is "CAT_DESC" we created in front):
EXEC SP_FULLTEXT_TABLE 'CATEGORIES', 'CREATE', 'CAT_DESC',
'PK_CATEGORIES'
The next step is to add a column to this full-text index. You can select a language for each column. If the type of the column is image, a column must be specified to indicate the type of document stored in each row of the image column.
In column language selection, there are some important but unformatted precautions. These precautions are related to the text of the text and the MSSearch's indexing mode. The written text is provided by a component called a word separator (used as a word boundary marker). In English, word separators are typically spaced or some form of punctuation; and in other languages (such as German), words or characters can be combined; therefore, the selected column language should indicate to be stored in this column. The language in the line. If it is uncertain, the best method is usually used to use a neutral word separator (only using spaces and punctuation to perform tag function). Another benefit of selecting a column language is "Tracked Trip". The search root trace source in full text refers to the process of searching for all variations in a word in a particular language. Another consideration of the selection language is related to the representation of the data. For non-Image column data, there is no need to perform special filtering operations; the text usually needs to pass the word separator components as it is. The word separator is mainly used to process written text. Therefore, if there is any type of tag (eg, HTML) in the text, the language accuracy will not be high during the index and search. In this case, you have two options - the preferred method is to store only text data in the Image column and specify its document type to filter it. If this method is not selected, you can consider using a neutral word separator, and possible, add a marker data (e.g., "BR" in HTML) in the interference word list). No language-based search sources are not performed in the columns specified in the neutral language, but some environments may require you to select this method.
After you know the column option, add one or two columns to the full-text index by issuing the following statement:
EXEC SP_FULLTEXT_COLUMN 'CATGORES', 'Description', 'Add'
You may notice that no language is specified here - in which case the default full-text language will be used. You can set the default full text language by system stored procedure "sp_configure" for the server.
After adding all columns to a full-text index, the fill operation can be performed. There is a lot of filling methods, which is not described in detail here. In this example, just fill the table, and wait for it to execute:
EXEC SP_FULLTEXT_TABLE 'CATGORIES', 'Start_Full'
You may want to use the FullTextCatalogProperty or ObjectProperty function to monitor the population. To get a directory fill status, you can do:
SELECT FULLTEXTCATALOGPROPERTY ('cat_desc', 'populatestatus')
Normally, if the full fill is being processed, the result is "1". For more information on how to use FullTextCatalogProperty and ObjectProperty, see SQL Server Books Online.
Full-text query
Query full-text index is slightly different from standard relationship queries in SQL Server. Since the index is stored and managed outside the SQL Server, most of the full-text query processing is complete by MSSearch (therefore, those part are relational, part of the full-textful query will be handled separately), so sometimes it will harm performance.
From essentially, when performing a full text query, the query is passed to MSSearch, which traverses its internal data structure (index) and returns the primary key and the qualifying value to SQL Server. If you do a contAins or FreeText query, you can never see the primary key or qualifying value, but if you perform a ContaInstable or FreeEtextTable query, you will get these values, and then these values are usually combined with the base table. The process with the primary table consolidated primary key requires a high system overhead - later, we will introduce you to some clever ways to minimize or completely avoid this merge. If you have been thinking about how to return to the full text query, you can speculate that the ContainS / FreeText query is only executed with the ContainStable / FreeTextTable query and merge with the base table. With this understanding, you should avoid using these types of queries unless you don't do this. In the web search application, use ContainStable and FreeTextTable than using the same type of functions without TABLE.
To now, you already know that full-text query is a special way to access data from the MSSearch index stored outside SQL Server, and we will encounter trouble if it is blindly merged with the base table blindly. Another important thing that should be understood is the essence difference between the Contains style query and the FreEtext style query.
Contains query is used to perform a full match query on all words of the query. Whether you look for a single word or look up all words starting with "ORANGE", the system only returns the result of all search terms. Therefore, the Contains query speed is very fast because they usually return very little results and do not need to perform excessive additional processing. The shortcomings of Contains queries include the problem of troubleshooting interference. Experienced developers and database administrators who have used the full-text search, when trying to match the word or phrase that only contains a single interference word, has encountered "Your query only interfering", "is a surprising error. To avoid receiving this error, one of the methods is to filter out the interfering words before performing a full text query. It is impossible to return the result to the ContaS query containing the interference word. Because this kind of query returns a result of fully matched with the entire query string. Since the interference word is not a full-text index, the Contains query containing interference words will not return any rows.
FreeText query eliminates all warning descriptions that occurs during the Contains query. When a FreEtext query is issued, the root query is actually issued. Therefore, when you search for "root beer", "root" and "beer" include all its forms (search root traceability and language; the language used is determined by the full-cultural language specified by the generated index, and in the column of all queries Must be the same), and the system will return all of the rows that match one of these words.
The negative impact of FreEtext queries is that they are usually consumed more CPU than the Contains query - because the reuse of the source and returns more results, it is necessary to include more complex ranking calculations. However, Fretext-based queries are very flexible, and the speed is very fast, which is the best choice for usual use in web-based search applications.
Ranking and optimization
I often encounter users using the full-text search, they ask me what the numbering number is meaningful, and how to convert the ranking number into a value that can be understood by a user. For this question, the answer can be long, here I will make a brief answer. Simply, these ranking numbers are not as important as the order returned. That is, when you sort the results according to the rankings, you always return the highest level of association. The qualifying value itself often changes - the full-text search uses the probability ranking algorithm, that is, the relevance of each document returned is directly affected by any or all of the other documents in the full-text index. Some people think that a technique that helps to increase certain row ranks is repeatedly used search keywords in these rows. Although to some extent, this method may improve the chance of returning these lines, but in other cases, it may be counterproductive - and there is also a risk of decreasing the word query performance. A preferred solution is to achieve the "Best Selection" system for search applications (see the following example), which ensures that some documents are returned first. Use the keyword multiple times to expand the full-text index of these specific keywords, and MSSearch is wasting time when looking for correct rows and computing rankings. If the full-text index data is large, you can try this method, you may find that some full-text queries are time consuming. If you can achieve more meticulous (or more accurate) systems, you will find that it has significantly improved query performance.
Another issue of multiple repetition data is related to common techniques for combined relationship queries and full-text queries. Many users who use full-text search have been troubled by this issue. Whenever they try to apply some filter to the result returned to the full-text query, they will encounter such problems. As mentioned earlier, the full text query returns a primary key and a ranking for each matching line - to collect any details about these rows, you must merge with its base table. Since any number of results may be returned from unrestricted full-text queries, the merger may require a large amount of system overhead. One effective way to avoid combining is to add data to be filtered only in a full-text index (if possible). In other words, if the user wants to search for the keyword "ichiro" from the text of all articles on the newspaper, and only want to return the article in the newspaper column, the query statement is usually as follows:
-- [method 1:]
- The highest overhead: all select, then merge and filter
Select articles_tbl.author, articles_tbl.body, articles_tbl.dateline,
Ft_tbl. [Rank]
From FretextTable (Articles, Body, 'Ichiro') AS FT_TBL
Inner Join Articles as Articles_TBL
ON ft_tbl. [Key] = articles_tbl.ArticleID
Where articles_tbl.category = 'Sports'
- [Method 2:]
- You can use it, but will cause unexpected results, or return inaccurate results:
- Perform full-text filtering and only extract primary keys and ranking
- (Processing on the web server)
SELECT [key], [rank]
From ContainStable (Articles, *, 'Formsof (ICHIRO')
And "sports" ')
These two queries either unnecessarily occupy a large amount of system overhead, or there is a possibility that returns an error result (in the second query, "Sports" is likely to appear in all types of articles). These two technologies have other variants, but this is two very simple models. If it is feasible, I usually suggest that you can classify the data. That is, each possible value of the Category column has a column (or table), and the searchable keyword related to this article is only stored in this column. This method is used instead of using a "body" column and a "category" column, you can remove the Category column, and use the store to search for the "Body_
- Both becomes your own column (or form) and needs hit
- The full-text index is less. This is obvious to make some explanations ...
SELECT [key], [rank]
From FreetextTable (Articles, Body_Sports, 'Ichiro')
For systems that contain a lot of data, and these data can be adapted to this architecture (perhaps the main architecture), its performance is significantly improved. But when you apply multiple filters or do not apply a filter, there is a significant limit. Of course, there are other methods to solve these problems. Through the above example, you will learn about the method of abstracting some search criteria to the architecture - actually "spoof" optimization program (more exactly "becoming an" optimization program), because in the full text in SQL Server itself There is currently no local optimization.
Other performance skills
People often ask my question when chatting, how can I make pages display full-text query results. In other words, if I want to issue a "root beer" query, display 40 results on a web page, and only want to return 40 results on the page (for example, if I am on the third page, I hope only only Returns the results of 81 to 120).
For page display results, I have seen a variety of methods, but there is no way to do 100%. The method I recommend can minimize the number of times the full-text query execution (in fact, each result set to be displayed only) and use the web server as a simple cache. From a higher level, you only need to retrieve a complete primary key and qualified row collection in the full-text query (if needed, you can use the best choice in the architecture and extract the common filter), and store it in In the memory of the web server (depending on your application and load, imagine that the <32-byte typical primary key size is added to the <4-byte ranking size [equal to <36 bytes], then multiplied by usually returned The result set <1000 lines, finally equal to <35K. Assuming a activity cache set in any given time returns <1000 active query results set, you will find that this activity buffer set on the web server less than 35MB - This is also acceptable).
For page display results, the process only traverses the array stored in the memory of the web server and emits SELECT to SQL Server to only display the rows and columns that you need to display. This is returned to the full text query only returns the concept of primary keys and qualifications - SELECT (even many query statements) is much faster than the full-text query. Using SELECT instead of combining multiple rows with the base table, combining multiple other policies, you can keep more CPU cycles on the SQL Server computer, and more efficient, more cost-effective to use the web field.
Another way to replace the web server-side cache is to cache result sets in SQL Server itself and define a variety of methods for browsing these results. Although this article focuses on the Web Server (ASP)-level application design, SQL Server's programmable features provide a powerful framework for generating high-performance web search applications. summary
The full-text search feature of Microsoft SQL Server 2000 provides reliable, fast and flexible ways for non-structured text data stored in the index and query database. If you want to apply this fast, accurate search function to a variety of applications, it is important to make full use of its speed and accuracy to achieve full-text search solutions. By distributing the load and organizes the data in some clever way, you can save money to purchase other hardware and software to get rid of the troubles caused by unnecessary slow queries. When developing excellent search applications, many factors and precautions are usually taken into account, I hope that the information and examples provided herein will help you learn to use SQL Server 2000 to generate excellent web search applications.
Appendix A: Best choice for realizing full-text search functions
A feasible method for improving full-text query performance and effectiveness is to implement the "best choice" system. This system is a very simple way to ensure that some rows that match specific query expressions are first returned. Best choice is not complex pre-programming logic (for example, SharePoint Portal Server contains such logic), so it is usually a preferred method.
The best choice is selected in this example and the only primary key and some keywords are stored in a separate table. FreeTextTable Query (Very Small) Best Selection Table is executed, and any result returned from the query is returned with the FreEtextTable query result of the base table. Under these search conditions, the first return will be all "best choice" lines, followed by MSSearch to the highest level of association (returned in the order of decreasing).
Here is a very simple sample script for creating the best choice system.
Use mydb
Create Table DocumentTable (ftkey Int Not Null, Document NText)
CREATE UNIQUE INDEX DTFTKEY_IDX ON DocumentTable (ftkey)
/ *
Insert a document here
(To generate all documents of full-text index)
* /
- Create a full-text directory and index for all document tables
EXEC SP_FULLTEXT_CATALOG 'Documents_cat', 'Create', 'F: / FTCATS'
EXEC SP_FULLTEXT_TABLE 'DOCUMENTTABLE', 'CREATE', 'Documents_cat',
'DTFTKEY_IDX'
EXEC SP_FULLTEXT_COLUMN 'DOCUMENTTABLE', 'Document', 'Add'
EXEC SP_FULLTEXT_TABLE 'DOCUMENTTABLE', 'Start_Change_Tracking'
EXEC SP_FULLTEXT_TABLE 'DOCUMENTTABLE', 'Start_Background_UpdateIndex'
/ *
Create the best choice and index now
(Add the document that should always return first)
* /
Create Table Bestbets (Ftkey Int Not Null, Keywords Ntext)
CREATE UNIQUE INDEX BBFTKEY_IDX ON BESTBETS (FTKEY) / *
Insert the best choice here
* /
- Create a full-text directory and index for the best options
EXEC SP_FULLTEXT_CATALOG 'BESTBETS_CAT', 'CREATE', 'F: / FTCATS'
EXEC SP_FULLTEXT_TABLE 'BESTBETS', 'Create', 'Bestbets_cat', 'BBFTKEY_IDX'
EXEC SP_FULLTEXT_COLUMN 'BESTBETS', 'Keywords', 'Add'
EXEC SP_FULLTEXT_TABLE 'BESTBETS', 'Start_Change_Tracking'
EXEC SP_FULLTEXT_TABLE 'BESTBETS', 'Start_Background_UpdateIndex'
First create a universal "All Document" tables for storing documents that you want a full-text index. Typically, other columns are included in the document table, but in this article, only two columns - primary key indexes and document itself. The full-text directory and index are created for document tables.
The "Best Selection" table is then created to store special documents that are first returned in all full-text queries. This table only needs to have a full-minded key column and a document itself (optimizing some documents as a query target, including adding other keywords in the document itself). The full-text directory and index are created for the best choice table.
The best choice table and document table can share documents (the best selection document is also stored in the regular document table, they share the same main key value), or mutual exclusion (the best choice document is only stored in the best choice) . To facilitate retrieval, making the best choice to mutually exclusive - so do not need to remove shared operations from the best choice and the returned normal search result row collection. On the other hand, the use of this method is used to maintain a document may be difficult to implement, because in this method, you must add logic to the query to delete the shared document between the returned rows.
If the above table is given, two stored procedures can be created to search for the best selection tables and document tables. You can cache and display the desired results using the web server level logic or other stored procedures (see a complete, valid example below for cache, display, and paging with the best choice).
First, create a stored procedure for retrieving the best choice line (if any):
Create Procedure BBSearch @searchterm Varchar (1024) AS
SELECT [Key], [Rank] from freetexttable (bestbets, keywords, @searchterm) Order by [rank] DESC
Make sure you have cleaned your access to the search string to avoid the use of T-SQL on the server, and ensure that the string is enacted in a single quotation. In this case, using FreetextTable is better than using ContainStable because FreetextTable will use the root traceable source and find the best choice to match any search term.
Next, the second stored procedure retrieves documents matching the regular search criteria (if any):
Create Procedure ftsearch @searchterm varchar (1024) AS
SELECT [Key], [Rank] from freetextTable (DocumentTable, Keywords, @searchterm) ORDER BY [RANK] DESC In addition, make sure that it is cleaned into the search string and encompasses the string with single quotes.
When performing these stored procedures, you should pass the same search term during two stored procedures. First, perform the best choice search, and then perform a common full-text search. The next section describes how to use the best options with other full-text search techniques when building a Web search application.
Appendix B: Sample application using the best choice, result paging and effective full-text query logic
In this example, we implemented a web search application that almost used all the optimization schemes described in this article. We use simple search engines to the online retailer catalog and assume that all users expect results in a short response time in the case of high traffic. This example uses the best selection table and stored procedure in the previous section.
This app is just a simple example of some advanced policies available to implement the best full-text search performance. This example uses ASP, or uses ISAPI, ASP.NET, or other platforms to implement similar solutions with their own advantages and disadvantages. Session objects do not necessarily apply all applications, and if not used, it may bring a certain degree of danger. In this example, we use session objects to implement fast and efficient caching mechanisms - of course there are many other methods to achieve this function varying degrees.
Below is a common code for the ASP page:
<% @Language = "VBScript"%>
<% Response.buffer = true%>
