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
>
