|
|
You're in the
sql server full text search
group:Which search happen first ? (Yukon full text search)
sql server full text search:
Xin Chen,
I believe you are concerned about the Yukon FTS service (MSFTESQL) reading all of a large FT Catalog when only results for a subset of rows (userid = 5) is required and therefore taking a long time to return results. Correct? If so, then Yukon FTS does understand this restriction in the where clause and will only read the *rows* in the FT Catalog based upon the where clause restriction and will return results faster. If not, could you further explain your question? Thanks, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "Xin Chen" <xchen@xtremework.com> wrote in message news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > In Yukon, if I do a fulltext search and also put let's say "userid = 5" in > the where clause, for example, [Where contains("description","yukon") and > userid = 5] > Does Yukon search for all the "yukon" among all the datarows first or search > for userid =5 first? > > Obviously search for userid = 5 first then full text search will be faster, > How does Yukon handle this ? > > --Xin Chen > >
It could be done first, it depends on parallelism. However, results are
returned from the full text catalogs and then merge with the results set returned by userid=5 to whittle down the results to matches of both conditions. -- 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 [quoted text, click to view] "Xin Chen" <xchen@xtremework.com> wrote in message news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > In Yukon, if I do a fulltext search and also put let's say "userid = 5" in > the where clause, for example, [Where contains("description","yukon") and > userid = 5] > Does Yukon search for all the "yukon" among all the datarows first or search > for userid =5 first? > > Obviously search for userid = 5 first then full text search will be faster, > How does Yukon handle this ? > > --Xin Chen > >
In Yukon, if I do a fulltext search and also put let's say "userid = 5" in
the where clause, for example, [Where contains("description","yukon") and userid = 5] Does Yukon search for all the "yukon" among all the datarows first or search for userid =5 first? Obviously search for userid = 5 first then full text search will be faster, How does Yukon handle this ? --Xin Chen
You're welcome, Xin Chin!
Yes, I believe you can do something similar as you're hinting at via Linked Servers between each database or server. The following is quoted from the SQL Server 2005 Dec '04 CTP BOL "Support for CONTAINS full-text predicate against linked servers SQL Server full-text search functionality can be applied on text data in remote servers when you used the CONTAINS predicate with SELECT queries against linked servers. In Microsoft® SQL ServerT 2005, you can use four-part names in CONTAINS or FREETEXT full-text predicates to execute queries against linked servers." As I've not yet tested this, and there is no exact syntax sample in the Yukon BOL on how to do this between databases on the same server (vs. on different servers), but I believe the syntax would look something like this after establishing the Linked Servers: select * from tableA where CONTAINS(serverA.databaseA..tableA.columnA, ' "searchA" ') union all select * from tableA where CONTAINS(serverA.databaseB..tableA.columnA, ' "searchB" ') union all select * from tableA where CONTAINS(serverA.databaseC..tableA.columnA, ' "searchC" ') -- etc... However, Yukon FTS does not support Distributed Partition Views, so having distributed or propagated search servers, will have to wait till the next version of SQL Server after Yukon... Hope that helps & keep reading my blog as I'll be posting more on Yukon in the very near future! Thanks, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "Xin Chen" <xchen@xtremework.com> wrote in message news:O7Hj0A6GFHA.904@tk2msftngp13.phx.gbl... > John, That is exactly what I asked. Thank you very much for your answer. By > the way, I enjoy you blog a lot. > Answer question for you. Sometime we divide a large database into identical > smaller ones(let's say 5 databases), but keep all the data structure the > same. If I fulltext index on each of the 5 databases, then is there a way I > can merge the full text search results from 5 database server to provide a > complete search result? Does Yukon offer this? I think it is critical > feature when you have distributed data. If Yukon doesn't offer this, do you > have any idea on how best handle this? Thank you very much ! > > --Xin Chen > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:#elrXe1GFHA.3612@TK2MSFTNGP09.phx.gbl... > > Xin Chen, > > I believe you are concerned about the Yukon FTS service (MSFTESQL) reading > > all of a large FT Catalog when only results for a subset of rows (userid = > > 5) is required and therefore taking a long time to return results. > Correct? > > > > If so, then Yukon FTS does understand this restriction in the where clause > > and will only read the *rows* in the FT Catalog based upon the where > clause > > restriction and will return results faster. If not, could you further > > explain your question? > > > > Thanks, > > John > > -- > > SQL Full Text Search Blog > > http://spaces.msn.com/members/jtkane/ > > > > > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > > news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > > > In Yukon, if I do a fulltext search and also put let's say "userid = 5" > in > > > the where clause, for example, [Where contains("description","yukon") > and > > > userid = 5] > > > Does Yukon search for all the "yukon" among all the datarows first or > > search > > > for userid =5 first? > > > > > > Obviously search for userid = 5 first then full text search will be > > faster, > > > How does Yukon handle this ? > > > > > > --Xin Chen > > > > > > > > > > > >
John, That is exactly what I asked. Thank you very much for your answer. By
the way, I enjoy you blog a lot. Answer question for you. Sometime we divide a large database into identical smaller ones(let's say 5 databases), but keep all the data structure the same. If I fulltext index on each of the 5 databases, then is there a way I can merge the full text search results from 5 database server to provide a complete search result? Does Yukon offer this? I think it is critical feature when you have distributed data. If Yukon doesn't offer this, do you have any idea on how best handle this? Thank you very much ! --Xin Chen [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:#elrXe1GFHA.3612@TK2MSFTNGP09.phx.gbl... > Xin Chen, > I believe you are concerned about the Yukon FTS service (MSFTESQL) reading > all of a large FT Catalog when only results for a subset of rows (userid = > 5) is required and therefore taking a long time to return results. Correct? > > If so, then Yukon FTS does understand this restriction in the where clause > and will only read the *rows* in the FT Catalog based upon the where clause > restriction and will return results faster. If not, could you further > explain your question? > > Thanks, > John > -- > SQL Full Text Search Blog > http://spaces.msn.com/members/jtkane/ > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > > In Yukon, if I do a fulltext search and also put let's say "userid = 5" in > > the where clause, for example, [Where contains("description","yukon") and > > userid = 5] > > Does Yukon search for all the "yukon" among all the datarows first or > search > > for userid =5 first? > > > > Obviously search for userid = 5 first then full text search will be > faster, > > How does Yukon handle this ? > > > > --Xin Chen > > > > > >
You're welcome, Xin Chen,
Keep in mind that the Yukon Dec '04 CTP BOL is still a working effort, i.e., not complete and that the entry for "Querying Linked Servers" [ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/0e0eaf74-442e-41ed-8198 -ba2c6337e54b.htm] only references CONTAINS and FREETEXT that can be used with linked servers. Furthermore, FREETEXTTABLE [ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4523ae15-4260-40a7-a53c -8df15e1fee79.htm] states that "table cannot specify a server name and cannot be used in queries against linked servers", while CONTAINSTABLE has no such restriction. The latter might be a "DOC" bug, i.e., CONTAINSTABLE may also have the same restriction as FREETEXTTABLE, but not yet documented. If CONTAINSTABLE and FREETEXTTABLE cannot be used with Linked Servers and as they are the only commands that return RANK, then returning RANK from a cross-database or cross-server join via linked servers may not be possible. Performance is always a concern in situations such as a cross-database join (FTS or non-FTS) and like they say, the proof is in the pudding and testing would need to be done to confirm that such a multi-database FTS join would perform well. Still, its an interesting feature that is not available in SQL Server 2000 that will have to be more explored under SQL Server 2005! Yes, Google does use their own-brand of distributed search. Here is a paper that describes the Google Cluster Architecture - http://www.computer.org/micro/mi2003/m2022.pdf. Note, that their "15,000 commodity class PC's with fault-tolerant software" is now reported to be over 100,000 today. Comparing a new database-specific search feature to an Internet-scale Search Engine, is not an apple-to-apple comparison, but still Microsoft SQL FTS Dev could take some pointers from Google for the next version of SQL Sever after Yukon, IMHO... Regards, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "Xin Chen" <xchen@xtremework.com> wrote in message news:u4Q7jPBHFHA.2456@TK2MSFTNGP09.phx.gbl... > Thanks again, John, I will try it out on Yukon. I am not sure if the Union > All will still make the rank relevent. I heard Microsft said that rank from > one database will be irrelvent to that of other database on full text > search. > > Another thing I worried is the performance. If the search research is large > data set, merge result and union them on a single server will mean large > network traffic. I wish I know how google does this. I am sure they must > use some type of distributed search. How could they do it so FAST?? Any > opinions? > > --Xin Chen > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:uiz98n7GFHA.4084@TK2MSFTNGP14.phx.gbl... > > You're welcome, Xin Chin! > > Yes, I believe you can do something similar as you're hinting at via > Linked > > Servers between each database or server. The following is quoted from the > > SQL Server 2005 Dec '04 CTP BOL "Support for CONTAINS full-text predicate > > against linked servers > > SQL Server full-text search functionality can be applied on text data in > > remote servers when you used the CONTAINS predicate with SELECT queries > > against linked servers. In Microsoft?SQL ServerT 2005, you can use > > four-part names in CONTAINS or FREETEXT full-text predicates to execute > > queries against linked servers." > > > > As I've not yet tested this, and there is no exact syntax sample in the > > Yukon BOL on how to do this between databases on the same server (vs. on > > different servers), but I believe the syntax would look something like > this > > after establishing the Linked Servers: > > > > select * from tableA where CONTAINS(serverA.databaseA..tableA.columnA, ' > > "searchA" ') > > union all > > select * from tableA where CONTAINS(serverA.databaseB..tableA.columnA, ' > > "searchB" ') > > union all > > select * from tableA where CONTAINS(serverA.databaseC..tableA.columnA, ' > > "searchC" ') > > -- etc... > > > > However, Yukon FTS does not support Distributed Partition Views, so having > > distributed or propagated search servers, will have to wait till the next > > version of SQL Server after Yukon... > > > > Hope that helps & keep reading my blog as I'll be posting more on Yukon in > > the very near future! > > Thanks, > > John > > > > -- > > SQL Full Text Search Blog > > http://spaces.msn.com/members/jtkane/ > > > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > > news:O7Hj0A6GFHA.904@tk2msftngp13.phx.gbl... > > > John, That is exactly what I asked. Thank you very much for your > answer. > > By > > > the way, I enjoy you blog a lot. > > > Answer question for you. Sometime we divide a large database into > > identical > > > smaller ones(let's say 5 databases), but keep all the data structure the > > > same. If I fulltext index on each of the 5 databases, then is there a > way > > I > > > can merge the full text search results from 5 database server to provide > a > > > complete search result? Does Yukon offer this? I think it is critical > > > feature when you have distributed data. If Yukon doesn't offer this, do > > you > > > have any idea on how best handle this? Thank you very much ! > > > > > > --Xin Chen > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > news:#elrXe1GFHA.3612@TK2MSFTNGP09.phx.gbl... > > > > Xin Chen, > > > > I believe you are concerned about the Yukon FTS service (MSFTESQL) > > reading > > > > all of a large FT Catalog when only results for a subset of rows > (userid > > = > > > > 5) is required and therefore taking a long time to return results. > > > Correct? > > > > > > > > If so, then Yukon FTS does understand this restriction in the where > > clause > > > > and will only read the *rows* in the FT Catalog based upon the where > > > clause > > > > restriction and will return results faster. If not, could you further > > > > explain your question? > > > > > > > > Thanks, > > > > John > > > > -- > > > > SQL Full Text Search Blog > > > > http://spaces.msn.com/members/jtkane/ > > > > > > > > > > > > > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > > > > news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > > > > > In Yukon, if I do a fulltext search and also put let's say "userid = > > 5" > > > in > > > > > the where clause, for example, [Where > contains("description","yukon") > > > and > > > > > userid = 5] > > > > > Does Yukon search for all the "yukon" among all the datarows first > or > > > > search > > > > > for userid =5 first? > > > > > > > > > > Obviously search for userid = 5 first then full text search will be > > > > faster, > > > > > How does Yukon handle this ? > > > > > > > > > > --Xin Chen > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Thanks again, John, I will try it out on Yukon. I am not sure if the Union
All will still make the rank relevent. I heard Microsft said that rank from one database will be irrelvent to that of other database on full text search. Another thing I worried is the performance. If the search research is large data set, merge result and union them on a single server will mean large network traffic. I wish I know how google does this. I am sure they must use some type of distributed search. How could they do it so FAST?? Any opinions? --Xin Chen [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:uiz98n7GFHA.4084@TK2MSFTNGP14.phx.gbl... > You're welcome, Xin Chin! > Yes, I believe you can do something similar as you're hinting at via Linked > Servers between each database or server. The following is quoted from the > SQL Server 2005 Dec '04 CTP BOL "Support for CONTAINS full-text predicate > against linked servers > SQL Server full-text search functionality can be applied on text data in > remote servers when you used the CONTAINS predicate with SELECT queries > against linked servers. In Microsoft?SQL ServerT 2005, you can use > four-part names in CONTAINS or FREETEXT full-text predicates to execute > queries against linked servers." > > As I've not yet tested this, and there is no exact syntax sample in the > Yukon BOL on how to do this between databases on the same server (vs. on > different servers), but I believe the syntax would look something like this > after establishing the Linked Servers: > > select * from tableA where CONTAINS(serverA.databaseA..tableA.columnA, ' > "searchA" ') > union all > select * from tableA where CONTAINS(serverA.databaseB..tableA.columnA, ' > "searchB" ') > union all > select * from tableA where CONTAINS(serverA.databaseC..tableA.columnA, ' > "searchC" ') > -- etc... > > However, Yukon FTS does not support Distributed Partition Views, so having > distributed or propagated search servers, will have to wait till the next > version of SQL Server after Yukon... > > Hope that helps & keep reading my blog as I'll be posting more on Yukon in > the very near future! > Thanks, > John > > -- > SQL Full Text Search Blog > http://spaces.msn.com/members/jtkane/ > > > "Xin Chen" <xchen@xtremework.com> wrote in message > news:O7Hj0A6GFHA.904@tk2msftngp13.phx.gbl... > > John, That is exactly what I asked. Thank you very much for your answer. > By > > the way, I enjoy you blog a lot. > > Answer question for you. Sometime we divide a large database into > identical > > smaller ones(let's say 5 databases), but keep all the data structure the > > same. If I fulltext index on each of the 5 databases, then is there a way > I > > can merge the full text search results from 5 database server to provide a > > complete search result? Does Yukon offer this? I think it is critical > > feature when you have distributed data. If Yukon doesn't offer this, do > you > > have any idea on how best handle this? Thank you very much ! > > > > --Xin Chen > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:#elrXe1GFHA.3612@TK2MSFTNGP09.phx.gbl... > > > Xin Chen, > > > I believe you are concerned about the Yukon FTS service (MSFTESQL) > reading > > > all of a large FT Catalog when only results for a subset of rows (userid > = > > > 5) is required and therefore taking a long time to return results. > > Correct? > > > > > > If so, then Yukon FTS does understand this restriction in the where > clause > > > and will only read the *rows* in the FT Catalog based upon the where > > clause > > > restriction and will return results faster. If not, could you further > > > explain your question? > > > > > > Thanks, > > > John > > > -- > > > SQL Full Text Search Blog > > > http://spaces.msn.com/members/jtkane/ > > > > > > > > > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > > > news:ubckCiwGFHA.3504@TK2MSFTNGP10.phx.gbl... > > > > In Yukon, if I do a fulltext search and also put let's say "userid = > 5" > > in > > > > the where clause, for example, [Where contains("description","yukon") > > and > > > > userid = 5] > > > > Does Yukon search for all the "yukon" among all the datarows first or > > > search > > > > for userid =5 first? > > > > > > > > Obviously search for userid = 5 first then full text search will be > > > faster, > > > > How does Yukon handle this ? > > > > > > > > --Xin Chen > > > > > > > > > > > > > > > > > > > >
John, thanks again for your reply and the great article you mentioned. I
will be reading it very carefully to see if I can find something I can use. I had once written a crawler written in C# to collect the web pages and store them in Yukon. I think Yukon (beta 2) corrupted the database when I got to about 1 million page in it. I broke the text content of 1 page into 10 row, so the database(FT) is about 10 million rows. I had hope to see Yukon to index 10 million web page with adequate performance. I think in term of full text indexing and search, its fundamental techniques are pretty common. It just matter of who can implement it better and get the best scalability(or distributed search I believe) which is badly needed when dealing with billion's web pages. --Xin Chen [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message
news:uohVL1CHFHA.2752@TK2MSFTNGP12.phx.gbl... > You're welcome, Xin Chen, > Keep in mind that the Yukon Dec '04 CTP BOL is still a working effort, i.e., > not complete and that the entry for "Querying Linked Servers" > [ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/0e0eaf74-442e-41ed-8198 > -ba2c6337e54b.htm] only references CONTAINS and FREETEXT that can be used > with linked servers. Furthermore, FREETEXTTABLE > [ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4523ae15-4260-40a7-a53c > -8df15e1fee79.htm] states that "table cannot specify a server name and > cannot be used in queries against linked servers", while CONTAINSTABLE has > no such restriction. The latter might be a "DOC" bug, i.e., CONTAINSTABLE > may also have the same restriction as FREETEXTTABLE, but not yet documented. > > If CONTAINSTABLE and FREETEXTTABLE cannot be used with Linked Servers and as > they are the only commands that return RANK, then returning RANK from a > cross-database or cross-server join via linked servers may not be possible. > > Performance is always a concern in situations such as a cross-database join > (FTS or non-FTS) and like they say, the proof is in the pudding and testing > would need to be done to confirm that such a multi-database FTS join would > perform well. Still, its an interesting feature that is not available in SQL > Server 2000 that will have to be more explored under SQL Server 2005! > > Yes, Google does use their own-brand of distributed search. Here is a paper > that describes the Google Cluster Architecture - > http://www.computer.org/micro/mi2003/m2022.pdf. Note, that their "15,000 > commodity class PC's with fault-tolerant software" is now reported to be > over 100,000 today. Comparing a new database-specific search feature to an > Internet-scale Search Engine, is not an apple-to-apple comparison, but still > Microsoft SQL FTS Dev could take some pointers from Google for the next > version of SQL Sever after Yukon, IMHO... > > Regards, > John > -- > SQL Full Text Search Blog > http://spaces.msn.com/members/jtkane/ > > > "Xin Chen" <xchen@xtremework.com> wrote in message > news:u4Q7jPBHFHA.2456@TK2MSFTNGP09.phx.gbl... > > Thanks again, John, I will try it out on Yukon. I am not sure if the > Union > > All will still make the rank relevent. I heard Microsft said that rank > from > > one database will be irrelvent to that of other database on full text > > search. > > > > Another thing I worried is the performance. If the search research is > large > > data set, merge result and union them on a single server will mean large > > network traffic. I wish I know how google does this. I am sure they must > > use some type of distributed search. How could they do it so FAST?? Any > > opinions? > > > > --Xin Chen > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > news:uiz98n7GFHA.4084@TK2MSFTNGP14.phx.gbl... > > > You're welcome, Xin Chin! > > > Yes, I believe you can do something similar as you're hinting at via > > Linked > > > Servers between each database or server. The following is quoted from > the > > > SQL Server 2005 Dec '04 CTP BOL "Support for CONTAINS full-text > predicate > > > against linked servers > > > SQL Server full-text search functionality can be applied on text data in > > > remote servers when you used the CONTAINS predicate with SELECT queries > > > against linked servers. In Microsoft?SQL ServerT 2005, you can use > > > four-part names in CONTAINS or FREETEXT full-text predicates to execute > > > queries against linked servers." > > > > > > As I've not yet tested this, and there is no exact syntax sample in the > > > Yukon BOL on how to do this between databases on the same server (vs. on > > > different servers), but I believe the syntax would look something like > > this > > > after establishing the Linked Servers: > > > > > > select * from tableA where CONTAINS(serverA.databaseA..tableA.columnA, ' > > > "searchA" ') > > > union all > > > select * from tableA where CONTAINS(serverA.databaseB..tableA.columnA, ' > > > "searchB" ') > > > union all > > > select * from tableA where CONTAINS(serverA.databaseC..tableA.columnA, ' > > > "searchC" ') > > > -- etc... > > > > > > However, Yukon FTS does not support Distributed Partition Views, so > having > > > distributed or propagated search servers, will have to wait till the > next > > > version of SQL Server after Yukon... > > > > > > Hope that helps & keep reading my blog as I'll be posting more on Yukon > in > > > the very near future! > > > Thanks, > > > John > > > > > > -- > > > SQL Full Text Search Blog > > > http://spaces.msn.com/members/jtkane/ > > > > > > > > > "Xin Chen" <xchen@xtremework.com> wrote in message > > > news:O7Hj0A6GFHA.904@tk2msftngp13.phx.gbl... > > > > John, That is exactly what I asked. Thank you very much for your > > answer. > > > By > > > > the way, I enjoy you blog a lot. > > > > Answer question for you. Sometime we divide a large database into > > > identical > > > > smaller ones(let's say 5 databases), but keep all the data structure > the > > > > same. If I fulltext index on each of the 5 databases, then is there a > > way > > > I > > > > can merge the full text search results from 5 database server to > provide > > a > > > > complete search result? Does Yukon offer this? I think it is > critical > > > > feature when you have distributed data. If Yukon doesn't offer this, > do > > > you > > > > have any idea on how best handle this? Thank you very much ! > > > > > > > > --Xin Chen > > > > > > > > > > > > "John Kane" <jt-kane@comcast.net> wrote in message > > > > news:#elrXe1GFHA.3612@TK2MSFTNGP09.phx.gbl... > > > > > Xin Chen, > > > > > I believe you are concerned about the Yukon FTS service (MSFTESQL) > > > reading > > > > > all of a large FT Catalog when only results for a subset of rows > > (userid > > > = > > > > > 5) is required and therefore taking a long time to return results. > > > > Correct? > > > > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |