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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-21 : 08:22:22
|
| Hemang writes "I want to tune the sql query, Please find it below.SELECT TOP 10 [NPOId],[EIN], [NPOName], [City], [State], [ZipCode],[OrganizationCode], [CategoryDescription], [ClassificationCode], [SubSectionCode], [SubCategoryDescription] FROM tbl_NPODetails INNER JOIN CONTAINSTABLE (tbl_NPODetails, *,'("amer" Or "American")') AS KEY_TBL ON KEY_TBL.[KEY] = tbl_NPODetails.NPOId where city = 'New York'Herein I have Indexed City column.I have Full Text Indexed Name Column.I want to optimize this query in such a way so as to have the inner join with the subset of the recordet obtained on querying with city." |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-21 : 09:00:24
|
| One thing you could do is take out the TOP 10 (especially since you have no order by clause) and use the top_n_by_rank qualifier for CONTAINSTABLE. Limiting the rows returned by FTS is the easiest way to improve Full Text Search PerformanceSELECT [NPOId],[EIN], [NPOName], [City], [State], [ZipCode],[OrganizationCode], [CategoryDescription], [ClassificationCode], [SubSectionCode], [SubCategoryDescription] FROM tbl_NPODetails INNER JOIN CONTAINSTABLE (tbl_NPODetails, *,'("amer" Or "American")',10) AS KEY_TBL ON KEY_TBL.[KEY] = tbl_NPODetails.NPOId where city = 'New York' Can you post the SHOWPLAN TEXT output of your query.How selective is the city column in tbl_NPODetails ?HTHJasper Smith |
 |
|
|
|
|
|
|
|
SQL Query (Related to Full Text Index)