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 |
|
sdwebguy
Starting Member
2 Posts |
 Posted - 2008-01-30 : 17:06:47
|
| I need advice for any recommendations to speed up a full text search.The query in its worst performing state:select count(*) as itemCount from dbo.files as finner join containstable(files,filecontent,'stop or go') s on s.[key] = f.fileIDSome definition:the files table has the following columns: fileID (int), filecontent (varchar(max))FileID is the primary key and has a unique index. There are 4.1 million records in the table.The above search takes anywhere from 10 to 15 seconds to perform. I know the search criteria is horrible, but then again users put in bad criteria all the time, so we are trying to speed up the bad ones. Any help would be appreciated.Tim |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-01-31 : 05:26:11
|
| Depending on how critical it is for the CONTAINSTABLE , you could switch to using CONTAINS .Jack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
sdwebguy
Starting Member
2 Posts |
Posted - 2008-01-31 : 10:26:02
|
| Yeah, we tried that -- the execution paths were identical for both queries so I think internally it might be doing the same thing? We dont need the rank, so we can use CONTAINS in this case; but it didnt help increase the response time. Still takes over 10 seconds.Tim |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-31 : 10:42:18
|
| the containstable returns the whole resultset and then joins to it. this can take a while.what i found it works pretty ok is to set the limit to the containstable:declare @rc intselect * into #tempfrom dbo.files select @rc = @@rowcountselect count(*) as itemCount from #temp t1join containstable(files,filecontent,'stop or go', @rc) s on s.[key] = f.fileID_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
|
|
|
Recommendations for speeding up query