 |
 | 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 / April 2007FT query plan
The following query: SELECT PatientGUID FROM PATIENT_SEARCH PS WHERE PS.LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128' AND CONTAINS (PS.SEARCHCOL , ' "patient*" ') Shows 2500 rows qualified by LicenseID = '465f20fc-8bd5-4802-a3f5-2a5f702be128', but 5000000 rows for "Remote Scan". Takes about 30 secs to complete which is a lot slower than even LIKE '%patient%'' I would expect it to only do FT on those 2500. Thank you, Igor Statistics coming back from the remote scan of the full-text catalog are frequently not accurate. The real problem here is that you are doing trimming where the complete results set that matches the wild card search on patient has to be returned from full-text to SQL Server and then only rows which contains the licenseid of '465f20fc-8bd5-4802-a3f5-2a5f702be128' are then returned. > The following query: > [quoted text clipped - 13 lines] > > *** Sent via Developersdex http://www.developersdex.com *** Hilary, Tha is my problem exactly. How can I force it to do the lookup on LicenseID and only then apply the FT to the remaining rows? Thank you, Igor If licenseID is discrete enough you could partition or use a full-text index on an indexed view. Otherwise you might be able to store it in the SearchCol column as well and then search on patient* and your licenseId. The problem with this approach is the more search terms you have the worse your search. The performance degradation going from one to two terms is not that significant however. > Hilary, > Tha is my problem exactly. How can I force it to do the lookup on [quoted text clipped - 4 lines] > > *** Sent via Developersdex http://www.developersdex.com *** I don't understand. LicenseID is on the same table right now as the FT column and LicenseID is indexed. How would an indexed view help the performance in this situation? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.