Full text search catalog

Hi,
I am using a full-text catalog to perform search inside a 1GB table. I
have created fulltext catalog as usual methods, selected the necessary
fields for the catalog from the table. When I want to make a stress test
over the search fucntion, it deoesnt return any data if I use more then 7
concurrent connections.
I heard that, this was a problem on sql server version 7. But this is a
sql server 2000 version. And from the white paper of full text catalogs, it
says it is able to handle up to 5124 concurrent connections to a fulltext
catalog.
What would be causing this ?
PS: Computer's props: Dual P3 500 Mhz, 1GB Ram, 18 GB SCSI HDD, Win2k
Server, Sql Server 2K Standard Ed. Computer only works for sql server
processes, not other process bothers the computer.
Thanks and Best Regards.
Murtix Van Basten.
Murtix,
When you say a "1GB Table", do you mean 1GB rows or the overall size of this
table? Also, how long are you waiting for the queries to return results and
what is the expected number of results per query? SQL FT Indexing and FT
Search query performance is a function of the total number rows as well as
the expected number of rows returned. Are you using the exact SQL FTS query
in each of the 7 concurrent connection or is each query different? Could you
post an example of your SQL FTS query?
Also, what is the return value for this query: EXEC sp_fulltext_service
'resource_usage'
Regards,
John
"Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in message
news:406a219f_4@.news.athenanews.com...
> Hi,
> I am using a full-text catalog to perform search inside a 1GB table. I
> have created fulltext catalog as usual methods, selected the necessary
> fields for the catalog from the table. When I want to make a stress test
> over the search fucntion, it deoesnt return any data if I use more then 7
> concurrent connections.
> I heard that, this was a problem on sql server version 7. But this is
a
> sql server 2000 version. And from the white paper of full text catalogs,
it
> says it is able to handle up to 5124 concurrent connections to a fulltext
> catalog.
> What would be causing this ?
> PS: Computer's props: Dual P3 500 Mhz, 1GB Ram, 18 GB SCSI HDD, Win2k
> Server, Sql Server 2K Standard Ed. Computer only works for sql server
> processes, not other process bothers the computer.
> Thanks and Best Regards.
> Murtix Van Basten.
>
|||John,
the table has 784.000 rows. Table has 19 columns, and most important
column (BODY) is Long Text type. Only 5 columns are being used in the FTS
indexing. That is one of them.
when I run it as 1 concurrent user, it takes 3-5 seconds to get a
result. I expect not more than 40 results per query. It can be less or none.
I use WAS Tool to run a stress test on the web[age that triggers the query.
And I use same exact parameters for each 7 concurrent connection. When I put
8th one, the return is nothing.
You can see the webpage that triggers the FTS from:
http://www.learnasp.com/search/a.asp...glish&limit=40
The FTS query is:
SET @.statement = 'SELECT TOP ' + @.num_matches + ' ''1000'' As Rank, list_,
messageid_, M.creatstamp_, hdrfrom_, hdrsubject_' + ' FROM messages_ AS M
INNER JOIN lists_ ON M.list_ = lists_.name_ ' + ' WHERE 1=1' + @.add_pred +
@.list_security + @.lang_pred + @.order_by
-- Execute the SELECT statement.
EXEC (@.statement)
messages_ is the table. If you wanna see, I can paste the whole SP.
Regards.
Murtix Van Basten.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uALwGlsFEHA.692@.TK2MSFTNGP09.phx.gbl...
> Murtix,
> When you say a "1GB Table", do you mean 1GB rows or the overall size of
this
> table? Also, how long are you waiting for the queries to return results
and
> what is the expected number of results per query? SQL FT Indexing and FT
> Search query performance is a function of the total number rows as well as
> the expected number of rows returned. Are you using the exact SQL FTS
query
> in each of the 7 concurrent connection or is each query different? Could
you
> post an example of your SQL FTS query?
> Also, what is the return value for this query: EXEC sp_fulltext_service
> 'resource_usage'
> Regards,
> John
>
> "Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in
message
> news:406a219f_4@.news.athenanews.com...
I
7
is
> a
> it
fulltext
>
|||can you paste the entire sp here?
You aren't doing TSQL paging are you?
"Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in message
news:406a6f30_5@.news.athenanews.com...
> John,
> the table has 784.000 rows. Table has 19 columns, and most important
> column (BODY) is Long Text type. Only 5 columns are being used in the FTS
> indexing. That is one of them.
> when I run it as 1 concurrent user, it takes 3-5 seconds to get a
> result. I expect not more than 40 results per query. It can be less or
none.
> I use WAS Tool to run a stress test on the web[age that triggers the
query.
> And I use same exact parameters for each 7 concurrent connection. When I
put
> 8th one, the return is nothing.
> You can see the webpage that triggers the FTS from:
>
http://www.learnasp.com/search/a.asp...glish&limit=40
> The FTS query is:
> SET @.statement = 'SELECT TOP ' + @.num_matches + ' ''1000'' As Rank,
list_,
> messageid_, M.creatstamp_, hdrfrom_, hdrsubject_' + ' FROM messages_ AS M
> INNER JOIN lists_ ON M.list_ = lists_.name_ ' + ' WHERE 1=1' + @.add_pred
+
> @.list_security + @.lang_pred + @.order_by
> -- Execute the SELECT statement.
> EXEC (@.statement)
> messages_ is the table. If you wanna see, I can paste the whole SP.
> Regards.
> Murtix Van Basten.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uALwGlsFEHA.692@.TK2MSFTNGP09.phx.gbl...
> this
> and
as
> query
> you
sp_fulltext_service
> message
table.
> I
test
then
> 7
> is
catalogs,
> fulltext
>
|||Here is the spGeneral:
-- starts here --
CREATE PROCEDURE spGeneral
@.num_matches varchar(4),
@.additional_predicates varchar(5000) = '',
@.order_by_list varchar(500) = '',
@.list_lang varchar(255),
@.lists smallint = 0
AS
BEGIN
DECLARE
@.add_pred varchar(510),
@.lang_pred varchar(255),
@.order_by varchar(510),
@.statement varchar(8000),
@.list_security varchar(100),
@.querystart datetime,
@.queryend datetime
SET NOCOUNT ON
-- The clock is ticking
SET @.querystart = GetDate()
-- List security
IF @.lists = 1
SET @.list_security = ' AND security_ = ''closed'''
ELSE
IF @.lists = 2
SET @.list_security = ' AND security_ = ''private'''
ELSE
IF @.lists = 3
SET @.list_security = ' AND security_ IN (''closed'', ''private'')'
ELSE
IF @.lists = 4
SET @.list_security = ''
ELSE
SET @.list_security = ' AND security_ = ''open'''
-- Check if language specified
IF @.list_lang = ''
SET @.lang_pred = ''
ELSE
BEGIN
IF @.lists = 4
SET @.lang_pred = ' PrimLang_ IN (' + @.list_lang + ')'
ELSE
SET @.lang_pred = ' AND PrimLang_ IN (' + @.list_lang + ')'
END
-- Additional Predicates
IF @.additional_predicates <> ''
BEGIN
SET @.add_pred = ' AND (' + @.additional_predicates + ')'
-- Remove ambiguity
SET @.add_pred = Replace(@.add_pred, "creatstamp_", "M.creatstamp_")
END
ELSE
SET @.add_pred = ''
-- Insert ORDER BY, if needed.
IF @.order_by_list <> ''
BEGIN
SET @.order_by = ' ORDER BY ' + @.order_by_list
-- Remove ambiguity
SET @.order_by = Replace(@.order_by, "creatstamp_", "M.creatstamp_")
END
ELSE
SET @.order_by = ''
SET @.statement = 'SELECT TOP ' + @.num_matches +
' ''1000'' As Rank, list_, messageid_, M.creatstamp_, hdrfrom_,
hdrsubject_' +
' FROM messages_ AS M INNER JOIN lists_ ON M.list_ =
lists_.name_ ' +
' WHERE 1=1' + @.add_pred + @.list_security + @.lang_pred + @.order_by
-- Execute the SELECT statement.
EXEC (@.statement)
-- Time's up!
SET @.queryend = GetDate()
SELECT DateDiff(ms, @.querystart, @.queryend) As ExecTime
SET NOCOUNT OFF
END
GO
-- ends here --
Murtix Van Basten
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ebtBRlyFEHA.3984@.TK2MSFTNGP10.phx.gbl...
> can you paste the entire sp here?
> You aren't doing TSQL paging are you?
> "Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in
message
> news:406a6f30_5@.news.athenanews.com...
FTS
> none.
> query.
> put
>
http://www.learnasp.com/search/a.asp...glish&limit=40
> list_,
M
@.add_pred
> +
of
results
FT
well
> as
Could
> sp_fulltext_service
> table.
necessary
> test
> then
this
> catalogs,
Win2k
server
>

Full Text in Sql Server 2005

Hello,
Aplogies ifg this is the worng news group for this question.
I have been looking in detail at the full text catlog in Sql server 2000. I
have found some of the facilaities very useful but a very big disavantage
for me is the wildcard seacrhing. You can only search ast the end of a word
or phrase, i.e.
sail*
sail*oat doies not work and neither does *sail
Does anyone know if these features will be in the 2005 version of SQL server
Kind Regards
Philip
no its still only at the end.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:414185ad$0$22762$db0fefd9@.news.zen.co.uk...
> Hello,
> Aplogies ifg this is the worng news group for this question.
> I have been looking in detail at the full text catlog in Sql server 2000.
I
> have found some of the facilaities very useful but a very big disavantage
> for me is the wildcard seacrhing. You can only search ast the end of a
word
> or phrase, i.e.
> sail*
> sail*oat doies not work and neither does *sail
> Does anyone know if these features will be in the 2005 version of SQL
server
> Kind Regards
> Philip
>
|||Hello Hillary,
Thankyou for ht einformaion,
do you know uf they are planning on adding to this feature in the future?
Phil
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> no its still only at the end.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:414185ad$0$22762$db0fefd9@.news.zen.co.uk...
2000.[vbcol=seagreen]
> I
disavantage
> word
> server
>
|||don't have a clue. IBM's equivalent of SQL FTS does have it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||BTW - when you have feature requests like this send them to
sqlwish@.microsoft.com
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||Philip,
I doubt very much if Microsoft is planning on adding this functionality as
it is dependent upon the SQL Server 2005 MSFTESQL search engine and Beta2 is
feature complete. If you want I can forward this request to my contacts at
Microsoft.
Thanks,
John
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...[vbcol=seagreen]
> Hello Hillary,
> Thankyou for ht einformaion,
> do you know uf they are planning on adding to this feature in the future?
> Phil
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O2YdYQzlEHA.2764@.TK2MSFTNGP11.phx.gbl...
> 2000.
> disavantage
a
>
|||Hello John,
That would be absolutly fantastic thanks.
Take care of yourself
Philip
"John Kane" <jt-kane@.comcast.net> wrote in message
news:eGDGMebmEHA.3452@.TK2MSFTNGP15.phx.gbl...
> Philip,
> I doubt very much if Microsoft is planning on adding this functionality as
> it is dependent upon the SQL Server 2005 MSFTESQL search engine and Beta2
is[vbcol=seagreen]
> feature complete. If you want I can forward this request to my contacts at
> Microsoft.
> Thanks,
> John
>
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:4141c6a9$0$22759$db0fefd9@.news.zen.co.uk...
future?[vbcol=seagreen]
of[vbcol=seagreen]
> a
SQL
>
|||Don't forget that you can request the feature yourself, just as well as John
can. :-) Simply send your request to sqlwish@.microsoft.com. It both goes
directly to the inbox of the folks who make the product decisions and it
also gets logged into an internal database so we can keep track of how many
folks are asking for similar things. You may not get a personalized
response, but you will be heard.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip Poole" <philip@.dontreply.org> wrote in message
news:4146a884$0$22762$db0fefd9@.news.zen.co.uk...
> Hello John,
> That would be absolutly fantastic thanks.
> Take care of yourself
> Philip
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eGDGMebmEHA.3452@.TK2MSFTNGP15.phx.gbl...
> is
> future?
> of
> SQL
>
|||Thanks, Stephen,
I had forgotten about that! Although, I am having lunch with someone in the
SQL Dev group soon and I was going to discuss this subject at that time
along with other related FTS subjects.
Philip, it would be best if you emailed sqlwish@.microsoft.com directly.
Regards,
John
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:uCYSy$mmEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Don't forget that you can request the feature yourself, just as well as
John
> can. :-) Simply send your request to sqlwish@.microsoft.com. It both goes
> directly to the inbox of the folks who make the product decisions and it
> also gets logged into an internal database so we can keep track of how
many
> folks are asking for similar things. You may not get a personalized
> response, but you will be heard.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Philip Poole" <philip@.dontreply.org> wrote in message
> news:4146a884$0$22762$db0fefd9@.news.zen.co.uk...
Beta2[vbcol=seagreen]
server[vbcol=seagreen]
end
>
|||For what it is worth we badly need the feature as well for a big prospect we
have. I'll send a message to sqlwish as well.
Regards
- Michele