 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / October 2004Full Text Search Performance
Hello, I have full text search enabled on two large tables, the first one has 3 million records (FT index column length is around 512-1025 char), and the other one has around 1.6 records (FT index column length is around 50-120 char) The simplest query that uses the smiplest form of FT on the first table FT index search needs 23-30 seconds to complete and from 10-15 seconds on the seconds table as it the indexed column is smaller. SQL server is deployed on 4 P servers with 8 GB of memory and attached to a very powerful SAN system. Memory usage on the server is not very high so I believe there is enough memory for the mssearch service to use. I just wonder if this is the normal throughput of the Full Text search of SQL Server. If not, I will appreciate any tips and hints that might be the reason of the system.  SignatureAli Salem That depends. SQL FTS performance is most sensitive to the number of rows you are returning. You should limit your results set as much as possible - I think you will find that the practical limit for most applications is around 100-200 rows. Limit it by using the the top_n_by_rank operator in ContainsTable or FreeTextTable, ie USE Northwind GO SELECT FT_TBL.Description, FT_TBL.CategoryName, KEY_TBL.RANK FROM Categories AS FT_TBL INNER JOIN CONTAINSTABLE (Categories, Description, 'test',200 ) AS KEY_TBL ON FT_TBL.CategoryID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC > Hello, > [quoted text clipped - 14 lines] > SQL Server. If not, I will appreciate any tips and hints that might be the > reason of the system. thanks for your reply. I cannot limit the search in this way, as there are other parameters in my query that can affect the returned result set and that are ourside the FTS. I am asking for any figures about FTS performance? What should I expect from it? Is this behavior am getting normal for the data size or, I am having something wrong > That depends. SQL FTS performance is most sensitive to the number of rows > you are returning. You should limit your results set as much as possible - I [quoted text clipped - 34 lines] > > SQL Server. If not, I will appreciate any tips and hints that might be the > > reason of the system. No, this is highly abnormal. Performance should be sub second. However I need to know what your queries look like, ie how many search arguments, how many rows are returned, and what language you are querying in. > thanks for your reply. > [quoted text clipped - 43 lines] > > > SQL Server. If not, I will appreciate any tips and hints that might be the > > > reason of the system. Thanks. - We are using enlgihs language. - Returned number of rows should be very large (thousands, in some cases it can be 100, 000). this from the Full-Text Seach, other SQL filters will be applied as well, but from mssearch this is what will be returned. Please note that the table size is around 3million records. - The smiplest query such as SELECT * FROM TABLE1 WHERE CONTAINS(COL1, 'ABCD') need around 30 seconds complete. I do appreciate any help. thank you > No, this is highly abnormal. Performance should be sub second. However I > need to know what your queries look like, ie how many search arguments, how [quoted text clipped - 61 lines] > the > > > > reason of the system. Hello, Do you have any comment to add to my problwm thank you > No, this is highly abnormal. Performance should be sub second. However I > need to know what your queries look like, ie how many search arguments, how [quoted text clipped - 61 lines] > the > > > > reason of the system. your problem is with the number of rows you are returning. There is little you can do to help with this. SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > Hello, Do you have any comment to add to my problwm > thank you [quoted text clipped - 64 lines] > > the > > > > > reason of the system. Try setting sp_fulltext_service 'resource_usage' 5 This primarily affects indexing. You might want to consider partitioning. Partitioning is where you break up the table you are searching into multiple child tables. If your query involves a data range you can have a table for each range, ie a year, or monthly tables. Then have some conditional statements which redirects the query to the pertinent table. This helps when you are doing queries like this select * from tableName where contains(*,'test') and datecol > 2004-01-01 > your problem is with the number of rows you are returning. There is little > you can do to help with this. [quoted text clipped - 85 lines] >> > the >> > > > > reason of the system. Ali Salem, Could you provide the full output of the following SQL script as it is very helpful in troubleshooting SQL FTS issue and understanding your environment and issues! use <your_database_name_here> go SELECT @@language SELECT @@version sp_configure 'default full-text language' EXEC sp_help_fulltext_catalogs EXEC sp_help_fulltext_tables EXEC sp_help_fulltext_columns EXEC sp_help <your_FT-enable_table_name_here> SELECT FULLTEXTSERVICEPROPERTY('ResourceUsage') go Additionally, where is your FT Catalog located as by default it is created under the \FTDATA directory where you have SQL Server installed as well as where your FT-enabled database files (*.mdf, *.ndf, * *.ldf) are located relative to the location of your FT Catalog folder? If your 'ResourceUsage is set to 3, you should increase it to 5 (dedicated) via sp_fulltext_service 'resource_usage' <value>, where <value> is 5. Thanks, John > Hello, > [quoted text clipped - 14 lines] > SQL Server. If not, I will appreciate any tips and hints that might be the > reason of the system. Below are the result of the script you requested me to run it: My FT files are stored on SAN storage. So IO should be performing well. Regarding the ResourceUsage, it is 3. Will not raising it to 5 harm the sql server performance! I dont want to make the full text search fast by slowing down SQL Server itself. I have splitted the result into two posts so that I can post it here. Thank you for you help ---------------------------------------------------------------------------------------------------------------------------- ---- us_english (1 row(s) affected) ---------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- -------- Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) (1 row(s) affected) name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- default full-text language 0 2147483647 1033 1033 ftcatid NAME PATH STATUS NUMBER_FULLTEXT_TABLES ------- -------------------------------------------------------------------------------------------------------------------- ------------ --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------- --------------------- ----------- ---------------------- 5 ICSubject L:\Microsoft SQL Server\MSSQL\FTDATA 0 1 6 Subject L:\Microsoft SQL Server\MSSQL\FTDATA 0 1 7 Names L:\Microsoft SQL Server\MSSQL\FTDATA 0 1 (3 row(s) affected) TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME ---------------------------------------------------------------------------------------------------------------------------- ---- ----------------------------------------------------------------------------------------------------------------------- --------- ------------------------------------------------------------------------------------------------------------------ -------------- ------------------ --------------------- -------------------------------------------------------------------- ------------------------------------------------------------ dbo IC_SUBJECTS PK_IC_SUBJECTS 1 1 ICSubject dbo MAIN PK_INOUT_MAIN 1 1 Subject dbo PERSONS PK_PERSONS 1 1 Names (3 row(s) affected) TABLE_OWNER TABLE_ID TABLE_NAME FULLTEXT_COLUMN_NAME FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE ---------------------------------------------------------------------------------------------------------------------------- ---- ----------- ----------------------------------------------------------------------------------------------------------- --------------------- ------------------------------------------------------------------------------------------------------ -------------------------- -------------- ---------------------------------------------------------------------------------- ---------------------------------------------- --------------------- ----------------- dbo 2069582411 IC_SUBJECTS ITEM_DESCRIPTION_AR 3 NULL NULL 0 dbo 274100017 MAIN SUBJECT 42 NULL NULL 0 dbo 466100701 PERSONS FIRSTNAME_AR 4 NULL NULL 0 dbo 466100701 PERSONS LASTNAME_AR 8 NULL NULL 0 dbo 466100701 PERSONS FULL_NAME 9 NULL NULL 0 dbo 466100701 PERSONS FATHERNAME_AR 12 NULL NULL 0 dbo 466100701 PERSONS GRANDFATHERNAME_AR 14 NULL NULL 0 (7 row(s) affected) Name Owner Type Created_datetime ---------------------------------------------------------------------------------------------------------------------------- ---- ----------------------------------------------------------------------------------------------------------------------- --------- ------------------------------- ------------------------------------------------------ MAIN dbo user table 2004-10-14 18:14:31.457 The Rest of the script result, one more post is required Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation ---------------------------------------------------------------------------------------------------------------------------- ---- ----------------------------------------------------------------------------------------------------------------------- --------- ----------------------------------- ----------- ----- ----- ----------------------------------- ------------------ ----------------- ----------------------------------- ---------------------------------------------------------------------- ---------------------------------------------------------- DOCID int no 4 10 0 no (n/a) (n/a) NULL DESCRIPTION varchar no 255 yes no no Arabic_CI_AS DOCDATE datetime no 8 yes (n/a) (n/a) NULL RDOCDATE datetime no 8 yes (n/a) (n/a) NULL EXT_PARTY_ID int no 4 10 0 yes (n/a) (n/a) NULL EXT_PARTY_REP_ID int no 4 10 0 yes (n/a) (n/a) NULL SUBCAT_ID decimal no 9 18 0 yes (n/a) (n/a) NULL STATUS_ID int no 4 10 0 yes (n/a) (n/a) NULL CONFID_ID int no 4 10 0 yes (n/a) (n/a) NULL REFERENCE_NO varchar no 50 yes no no Arabic_CI_AS FORWARD_TO decimal no 9 18 0 yes (n/a) (n/a) NULL FORWARD_DATE datetime no 8 yes (n/a) (n/a) NULL TO_REMIND decimal no 9 18 0 yes (n/a) (n/a) NULL REMIND_DATE datetime no 8 yes (n/a) (n/a) NULL REMARKS varchar no 1024 yes no no Arabic_CI_AS REVISION_NO varchar no 50 yes no no Arabic_CI_AS TRAN_DATE datetime no 8 yes (n/a) (n/a) NULL USER_ID varchar no 32 yes no no Arabic_CI_AS SYSTEM_ID varchar no 50 yes no no Arabic_CI_AS IP varchar no 50 yes no no Arabic_CI_AS CHECK_SUM decimal no 9 18 0 yes (n/a) (n/a) NULL SUBMIT_TYPE int no 4 10 0 yes (n/a) (n/a) NULL SUBJECT_TYPE int no 4 10 0 yes (n/a) (n/a) NULL SUBJECT nvarchar no 8000 yes (n/a) (n/a) Arabic_CI_AS PRIORITY_NO int no 4 10 0 yes (n/a) (n/a) NULL PROCESS_ID int no 4 10 0 yes (n/a) (n/a) NULL REMIND_TIME varchar no 20 yes no no Arabic_CI_AS WAITING_FOR_REPLY int no 4 10 0 yes (n/a) (n/a) NULL SITE_ID int no 4 10 0 yes (n/a) (n/a) NULL DELIVERY_METHOD_ID int no 4 10 0 yes (n/a) (n/a) NULL GROUPID int no 4 10 0 yes (n/a) (n/a) NULL LINK_STATUS int no 4 10 0 yes (n/a) (n/a) NULL INITIAL_PROCEDURE int no 4 10 0 yes (n/a) (n/a) NULL CREATOR_USER_ID varchar no 32 yes no no Arabic_CI_AS IS_SPLITTED bit no 1 yes (n/a) (n/a) NULL CHAR_FIELD2 varchar no 500 yes no no Arabic_CI_AS CHAR_FIELD5_AR nvarchar no 100 yes (n/a) (n/a) Arabic_CI_AS NUM_FIELD1 decimal no 9 18 0 yes (n/a) (n/a) NULL CHAR_FIELD66_AR nvarchar no 2000 yes (n/a) (n/a) Arabic_CI_AS NUM_FIELD6 decimal no 9 18 0 yes (n/a) (n/a) NULL FLOAT_FIELD1 int no 4 10 0 yes (n/a) (n/a) NULL FLOAT_FIELD2 int no 4 10 0 yes (n/a) (n/a) NULL NUM_FIELD2 decimal no 9 18 0 yes (n/a) (n/a) NULL NUM_FIELD3 decimal no 9 18 0 yes (n/a) (n/a) NULL NUM_FIELD4 decimal no 9 18 0 yes (n/a) (n/a) NULL NUM_FIELD5 decimal no 9 18 0 yes (n/a) (n/a) NULL DATE_FIELD1 datetime no 8 yes (n/a) (n/a) NULL DATE_FIELD2 datetime no 8 yes (n/a) (n/a) NULL CHAR_FIELD1 char no 50 yes no yes Arabic_CI_AS CHAR_FIELD3 char no 50 yes no yes Arabic_CI_AS CHAR_FIELD4 char no 50 yes no yes Arabic_CI_AS CHAR_FIELD5 char & |
Enable EMail Alerts
Start New Thread
Reply to this Message