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 |
|
ap7256
Starting Member
9 Posts |
 Posted - 2008-11-04 : 08:00:38
|
| Hello,I have an issue with full text search performance. A table with full text search contains over 9 million records, full text catalog size is over 3.5GB, the catalog has over 9 million unique items indexed and over 24 million key items (I am not sure if this info is relevant but I am providing it anyway). Oh and we have SQL Server 2005 of course.So here are two queries:1. SELECT TOP 100 * FROM Table WHERE CONTAINS (*, 'market')2. SELECT TOP 100 * FROM Table WHERE CONTAINS (*, 'market') ORDER BY ID DESCThe first query retrieves first (the oldest) 100 records having 'market' and the second query retrieves last 100 records having 'market' word.The first query returns results instantly, the second query takes about 7-8 seconds to return the results.ID column is the primary key column in the table.We need to use the second query but it's so slow. could you help me to fix the issue?Thanks |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-04 : 11:44:53
|
| Have you created a full-text index? Can you show script for it? |
 |
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-04 : 15:33:25
|
quote:
Originally posted by darkdusky
Have you created a full-text index? Can you show script for it?
yes, of course the full index is created. I am not sure what you mean by showing you the script, I used SQL Management studio to create it. So I guess when I ask SQL to retrieve the last 100 records containing the word 'market' sorted by ID then it makes the following steps:1. Retrieve all records with 'market' word inside (use full index for that).2. Sort them by ID in desc order3. Take the first 100 records.So am I think the second step (sorting) may be the bottleneck, right?what could be done to increase the performance?Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 15:35:05
|
| one thing is to filter the data returned from the full text index in the contains() partif you can..._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
ap7256
Starting Member
9 Posts |
Posted - 2008-11-04 : 15:48:52
|
quote:
Originally posted by spirit1
one thing is to filter the data returned from the full text index in the contains() partif you can...
Spirit1, could you provide little bit more info please? what do you mean filter the data returned? so once again, i need to retrieve the latest 100 records containing word 'market' in their full text indexed columns. Is my query doing sloppy work for that? is there another, more advanced way to do that?thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
|
|
Full text search question