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

freetexttable query continued

Still working on this query:
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products

> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>