CONTAINS and LIKE

Hillary, would you mind doing a small explanation on this post?
Thanks a lot!
It will do a clustered index scan. this is what it looks like.
StmtText
SELECT * from authors
WHERE
CONTAINS( *, 'keyworkd')
OR
au_lname LIKE '%keyworkd%'
(1 row(s) affected)
StmtText
----
|--Filter(WHERElike([authors].[au_lname], '%keyworkd%', NULL) OR
[Expr1004]))
|--Nested Loops(Left Semi Join, WHERElike([authors].[au_lname],
'%keyworkd%', NULL))OUTER REFERENCES[authors].[au_id]), DEFINE[Expr1004]
= [PROBE VALUE]))
|--Clustered Index
Scan(OBJECT[pubs].[dbo].[authors].[UPKCL_auidind]))
|--Filter(WHERE[authors].[au_id]=[FULLTEXT:authors].[KEY]))
|--Table Spool
|--Remote Scan(OBJECTCONTAINS))
(6 row(s) affected)
I didn't think you were being serious before.
Basically it does a remote scan of the full text catalog (|--Remote
Scan(OBJECTCONTAINS))), it then spools this data locally and does a join
against the authors table - scanning the au_id column using the auidind
index (this is a clustered index scan)
Scan(OBJECT[pubs].[dbo].[authors].[UPKCL_auidind]))
|--Filter(WHERE[authors].[au_id]=[FULLTEXT:authors].[KEY]))
|--Table Spool
It then does a nested loop against the authors table using the join (Left
Semi Join, WHERElike([authors].[au_lname], '%keyworkd%', NULL))
and filters the results.
Put this query in query analyzer and hit ctrl - L
and you can see it for yourself.
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
"Matias Woloski" <woloski@.NOSPAMsion.com> wrote in message
news:OJYMB6lVFHA.1328@.tk2msftngp13.phx.gbl...
> Hillary, would you mind doing a small explanation on this post?
> Thanks a lot!
> It will do a clustered index scan. this is what it looks like.
>
> StmtText
> ----
--
> --
> SELECT * from authors
> WHERE
> CONTAINS( *, 'keyworkd')
> OR
> au_lname LIKE '%keyworkd%'
> (1 row(s) affected)
> StmtText
> ----
--
> ----
--
> --
> |--Filter(WHERElike([authors].[au_lname], '%keyworkd%', NULL) OR
> [Expr1004]))
> |--Nested Loops(Left Semi Join, WHERElike([authors].[au_lname],
> '%keyworkd%', NULL))OUTER REFERENCES[authors].[au_id]),
DEFINE[Expr1004]
> = [PROBE VALUE]))
> |--Clustered Index
> Scan(OBJECT[pubs].[dbo].[authors].[UPKCL_auidind]))
> |--Filter(WHERE[authors].[au_id]=[FULLTEXT:authors].[KEY]))

> |--Table Spool
> |--Remote Scan(OBJECTCONTAINS))
> (6 row(s) affected)
>
>
|||Thanks Hilary for introducing me into this.
Now, I see the execution plan for this query and I see that 87% of the query
is the remote scan (contains).
I was concerned about the perf impact of having CONTAINS and LIKE both
together and it seems that is minimal.
What if I have 100k records? How the clustered index (like expr) would
perform? How compared to a query which only does a remote scan?
Thanks again for your help,
Matias
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uHajidvVFHA.2768@.tk2msftngp13.phx.gbl...
>I didn't think you were being serious before.
> Basically it does a remote scan of the full text catalog (|--Remote
> Scan(OBJECTCONTAINS))), it then spools this data locally and does a join
> against the authors table - scanning the au_id column using the auidind
> index (this is a clustered index scan)
> Scan(OBJECT[pubs].[dbo].[authors].[UPKCL_auidind]))
> |--Filter(WHERE[authors].[au_id]=[FULLTEXT:authors].[KEY]))
> |--Table Spool
> It then does a nested loop against the authors table using the join (Left
> Semi Join, WHERElike([authors].[au_lname], '%keyworkd%', NULL))
> and filters the results.
> Put this query in query analyzer and hit ctrl - L
> and you can see it for yourself.
>
> --
> 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
> "Matias Woloski" <woloski@.NOSPAMsion.com> wrote in message
> news:OJYMB6lVFHA.1328@.tk2msftngp13.phx.gbl...
> --
> --
> --
> DEFINE[Expr1004]
>
>
|||The nested loop is changed to a merge join
|--Filter(WHERElike([fulltext].[charcol], '%keyworkd%', NULL) OR
[Expr1004]))
|--Merge Join(Left Semi Join,
MERGE[fulltext].[pk])=([FULLTEXT:fulltext].[KEY]),
RESIDUAL[fulltext].[pk]=[FULLTEXT:fulltext].[KEY]) OR
like([fulltext].[charcol], '%keyworkd%', NULL))
|--Clustered Index
Scan(OBJECT[pubs].[dbo].[fulltext].[primarykey]), ORDERED FORWARD)
|--Sort(ORDER BY[FULLTEXT:fulltext].[KEY] ASC))
|--Remote Scan(OBJECTCONTAINS))
(5 row(s) affected)
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
"Matias Woloski" <woloski@.NOSPAMsion.com> wrote in message
news:uZPS3KxVFHA.2496@.TK2MSFTNGP10.phx.gbl...
> Thanks Hilary for introducing me into this.
> Now, I see the execution plan for this query and I see that 87% of the
query[vbcol=seagreen]
> is the remote scan (contains).
> I was concerned about the perf impact of having CONTAINS and LIKE both
> together and it seems that is minimal.
> What if I have 100k records? How the clustered index (like expr) would
> perform? How compared to a query which only does a remote scan?
> Thanks again for your help,
> Matias
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uHajidvVFHA.2768@.tk2msftngp13.phx.gbl...
join[vbcol=seagreen]
(Left[vbcol=seagreen]
-[vbcol=seagreen]
-[vbcol=seagreen]
-[vbcol=seagreen]
WHERElike([authors].[au_lname],[vbcol=seagreen]
|--Filter(WHERE[authors].[au_id]=[FULLTEXT:authors].[KEY]))
>