FT 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
*** Sent via Developersdex http://www.codecomments.com ***
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.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:uaslhAqhHHA.4064@.TK2MSFTNGP02.phx.gbl...
> 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
>
> *** Sent via Developersdex http://www.codecomments.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
*** Sent via Developersdex http://www.codecomments.com ***
|||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.
"mEmENT0m0RI" <nospam@.devdex.com> wrote in message
news:ObdYRu2hHHA.3960@.TK2MSFTNGP02.phx.gbl...
> 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
>
> *** Sent via Developersdex http://www.codecomments.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?
*** Sent via Developersdex http://www.codecomments.com ***
sql