Full Text Searching....THOUSANDS of records!

Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in acommon word like "damage" I get like 32,000 records. Now, theserver handles those records in about one second but the ASP page thatreturns the results takes about one MINUTE to download. When Isave the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks

Your Top 500 query is good, but you could also do a SELECT COUNT SQL query first getting exactly how may records would be returned. Just replace the fields to be returned by "COUNT(*)".
// Instantiate a Command object...
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = yourConnectionObject;
dbCommand.CommandText = "SELECT COUNT(*) " +
"FROM table-name WHERE column-name = 'some-value'";
dbCommand.CommandType = CommandType.Text;

// Execute the Command object...
int returnValue = (int)dbCommand.ExecuteScalar();
if ( returnValue > 500 )
string errorMessage = "Your query brings back " + returnValue.ToString() + " records!";
else
// Execute your regular query...
Or you could also just execute your normal query and test the number of rows in the DataTable:
if ( dataSet.Tables[0].Rows.Count > 500 )
string errorMessage = "Your query brings back " + dataSet.Tables[0].Rows.Count.ToString() + " records!";
else
// Display it...
The last could be your best bet as it only incurs one trip to the database.
NC...

|||Thanks!
What I actually did (after I posted the question) is leave the TOP 200(was 500 but I shortened it) and as I was displaying the results, Iupdated a variable. At the end of the page, I say somethinglike: "Over 200 records found, try narrowing your search".
The disadvantage is that you never really know how many records therewas (201 would be the same as 10,000) and it's at the bottom of thepage. But, I can live with it.
How much overhead would the extra SELECT COUNT method cost? Icould profile it I guess. But everyone is complaining about thespeed now.
I wrote the program 4 years ago when I was a rookie.
At least now even very common words just take a second or two to get the page. :-)
cbmeeks
|||

Run a search for CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE Microsoft proprietry implementation of ANSI SQL in SQL Server BOL (books online). Full Text is an add on to SQL Server so you must populate the Microsoft search catalog to get expected results. Hope this helps.

sql

full text search error - catalogue does not exist

Hi guys, I sent the following queries to my DB and they seem to work with success messages after each line but when I try to test it I get the message at the bottom. Any ideas? there's a good chance my test sql is all wrong!

sp_fulltext_database 'enable'

sp_fulltext_catalog 'Fulltextcatalog1','create'

sp_fulltext_table 'test','create','Fulltextcatalog1','PK_test'

sp_fulltext_column 'test','text','add'

sp_fulltext_table 'test','activate'

sp_fulltext_table 'test','start_full'

sp_fulltext_table test, 'Start_change_tracking'

sp_fulltext_table test, 'Start_background_updateindex'

-- now test it --

SELECT * FROM test WHERE FREETEXT(*,'spotless')

gets this result:
Error -2147217900
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.Just tried this (where my field name is 'text' and my table is called 'test') but get the same catalogue error
SELECT * FROM test WHERE CONTAINS(text,'spotless')

Full text or not full text?

I have problem with the speed of queries.
I'm searching for UK car number (registration) plates consisting of 2
letters, followed by 2 numbers, followed by 3 letters.
ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
Most of these number plates are in their own separate row, but some of
them are in a comma delimited string,
Now suppose I want to find 'AB12ABC' in a string that consists of
'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
Now at the moment, I've got a database table consisting of nearly
380,000 rows.
I've got indexs placed on the 'plate' column, and I've even tried
setting up full-text indexing, none of which have increased the speed
of the query:
SET NOCOUNT ON
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
BEGIN
SELECT 'Yes'
END
ELSE
BEGIN
SELECT 'N/A'
END
which, at [resent takes around 30 seconds to complete, so running a
report for say 300 number plates to find if they have been ordered or
not, takes around 30+ minutes to complete.
If there another way of doing things that any one can suggest? Apart
from splitting the strings up?That is the price of not normalizing. If the data was in its own
table, as all repeating groups should be, performance would not be a
problem at all. It would be pretty much instantaneous.
If doing them one at a time takes 30 seconds each, don't do them one
at a time. Load all 300 into a table, and run them all in one pass.
Performance won't be good, but it should not be as bad.
--This version just lists the ones that match
SELECT M.RegNum
FROM MatchList as M
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
--This version lists them all, with results of the match for each
SELECT M.RegNum,
CASE WHEN V.plates IS NOT NULL
THEN 'Yes'
ELSE 'N/A'
END as Found
FROM MatchList as M
LEFT OUTER
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
Roy Harvey
Beacon Falls, CT
On 6 Jul 2006 05:03:40 -0700, "pinhead" <dlynes2005@.gmail.com> wrote:

>I have problem with the speed of queries.
>I'm searching for UK car number (registration) plates consisting of 2
>letters, followed by 2 numbers, followed by 3 letters.
>ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
>Most of these number plates are in their own separate row, but some of
>them are in a comma delimited string,
>Now suppose I want to find 'AB12ABC' in a string that consists of
>'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
>Now at the moment, I've got a database table consisting of nearly
>380,000 rows.
>I've got indexs placed on the 'plate' column, and I've even tried
>setting up full-text indexing, none of which have increased the speed
>of the query:
>SET NOCOUNT ON
>IF EXISTS (SELECT id
>FROM vclivePlates
>WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
>BEGIN
>SELECT 'Yes'
>END
>ELSE
>BEGIN
>SELECT 'N/A'
>END
>which, at [resent takes around 30 seconds to complete, so running a
>report for say 300 number plates to find if they have been ordered or
>not, takes around 30+ minutes to complete.
>If there another way of doing things that any one can suggest? Apart
>from splitting the strings up?|||You run into performance problem because you store few plates in one
row. If you can modify the database and store each plate in its own
row, then you won't have to use wildcard in the beginning of your
search criteria and the server will be able to use index seek instead
of table scan. If you must use one row to store few plates, then you
can try something else. In your post you said that most plates are in
there own rows and only some rows store more then one plate. If only
small percentage of the rows store few plates, then maybe this will
help - create a computed column on the table that counts the number of
comas in the column that holds the registration plate. Then create an
index on that column. Modify you query so it will look like this:
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
1)
OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
This might cause the server to use the indexes, but it depends on the
number of rows that contain more then one plate.
Adi
pinhead wrote:
> I have problem with the speed of queries.
> I'm searching for UK car number (registration) plates consisting of 2
> letters, followed by 2 numbers, followed by 3 letters.
> ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> Most of these number plates are in their own separate row, but some of
> them are in a comma delimited string,
> Now suppose I want to find 'AB12ABC' in a string that consists of
> 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> Now at the moment, I've got a database table consisting of nearly
> 380,000 rows.
> I've got indexs placed on the 'plate' column, and I've even tried
> setting up full-text indexing, none of which have increased the speed
> of the query:
> SET NOCOUNT ON
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> BEGIN
> SELECT 'Yes'
> END
> ELSE
> BEGIN
> SELECT 'N/A'
> END
> which, at [resent takes around 30 seconds to complete, so running a
> report for say 300 number plates to find if they have been ordered or
> not, takes around 30+ minutes to complete.
> If there another way of doing things that any one can suggest? Apart
> from splitting the strings up?|||Having a major braindead day today, so forgive me for this...
how would I go about counting the number of commas in the data row?
Adi wrote:[vbcol=seagreen]
> You run into performance problem because you store few plates in one
> row. If you can modify the database and store each plate in its own
> row, then you won't have to use wildcard in the beginning of your
> search criteria and the server will be able to use index seek instead
> of table scan. If you must use one row to store few plates, then you
> can try something else. In your post you said that most plates are in
> there own rows and only some rows store more then one plate. If only
> small percentage of the rows store few plates, then maybe this will
> help - create a computed column on the table that counts the number of
> comas in the column that holds the registration plate. Then create an
> index on that column. Modify you query so it will look like this:
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> 1)
> OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
>
> This might cause the server to use the indexes, but it depends on the
> number of rows that contain more then one plate.
> Adi
> pinhead wrote:|||Sorry -
DATALENGTH(plates) - DATALENGTH(REPLACE(plates, ',', ''))
works well for me
pinhead wrote:[vbcol=seagreen]
> Having a major braindead day today, so forgive me for this...
> how would I go about counting the number of commas in the data row?
>
> Adi wrote:

Full text or not full text?

I have problem with the speed of queries.
I'm searching for UK car number (registration) plates consisting of 2
letters, followed by 2 numbers, followed by 3 letters.
ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
Most of these number plates are in their own separate row, but some of
them are in a comma delimited string,
Now suppose I want to find 'AB12ABC' in a string that consists of
'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
Now at the moment, I've got a database table consisting of nearly
380,000 rows.
I've got indexs placed on the 'plate' column, and I've even tried
setting up full-text indexing, none of which have increased the speed
of the query:
SET NOCOUNT ON
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
BEGIN
SELECT 'Yes'
END
ELSE
BEGIN
SELECT 'N/A'
END
which, at [resent takes around 30 seconds to complete, so running a
report for say 300 number plates to find if they have been ordered or
not, takes around 30+ minutes to complete.
If there another way of doing things that any one can suggest? Apart
from splitting the strings up?That is the price of not normalizing. If the data was in its own
table, as all repeating groups should be, performance would not be a
problem at all. It would be pretty much instantaneous.
If doing them one at a time takes 30 seconds each, don't do them one
at a time. Load all 300 into a table, and run them all in one pass.
Performance won't be good, but it should not be as bad.
--This version just lists the ones that match
SELECT M.RegNum
FROM MatchList as M
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
--This version lists them all, with results of the match for each
SELECT M.RegNum,
CASE WHEN V.plates IS NOT NULL
THEN 'Yes'
ELSE 'N/A'
END as Found
FROM MatchList as M
LEFT OUTER
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
Roy Harvey
Beacon Falls, CT
On 6 Jul 2006 05:03:40 -0700, "pinhead" <dlynes2005@.gmail.com> wrote:
>I have problem with the speed of queries.
>I'm searching for UK car number (registration) plates consisting of 2
>letters, followed by 2 numbers, followed by 3 letters.
>ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
>Most of these number plates are in their own separate row, but some of
>them are in a comma delimited string,
>Now suppose I want to find 'AB12ABC' in a string that consists of
>'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
>Now at the moment, I've got a database table consisting of nearly
>380,000 rows.
>I've got indexs placed on the 'plate' column, and I've even tried
>setting up full-text indexing, none of which have increased the speed
>of the query:
>SET NOCOUNT ON
>IF EXISTS (SELECT id
>FROM vclivePlates
>WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
>BEGIN
>SELECT 'Yes'
>END
>ELSE
>BEGIN
>SELECT 'N/A'
>END
>which, at [resent takes around 30 seconds to complete, so running a
>report for say 300 number plates to find if they have been ordered or
>not, takes around 30+ minutes to complete.
>If there another way of doing things that any one can suggest? Apart
>from splitting the strings up?|||You run into performance problem because you store few plates in one
row. If you can modify the database and store each plate in its own
row, then you won't have to use wildcard in the beginning of your
search criteria and the server will be able to use index seek instead
of table scan. If you must use one row to store few plates, then you
can try something else. In your post you said that most plates are in
there own rows and only some rows store more then one plate. If only
small percentage of the rows store few plates, then maybe this will
help - create a computed column on the table that counts the number of
comas in the column that holds the registration plate. Then create an
index on that column. Modify you query so it will look like this:
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
1)
OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
This might cause the server to use the indexes, but it depends on the
number of rows that contain more then one plate.
Adi
pinhead wrote:
> I have problem with the speed of queries.
> I'm searching for UK car number (registration) plates consisting of 2
> letters, followed by 2 numbers, followed by 3 letters.
> ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> Most of these number plates are in their own separate row, but some of
> them are in a comma delimited string,
> Now suppose I want to find 'AB12ABC' in a string that consists of
> 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> Now at the moment, I've got a database table consisting of nearly
> 380,000 rows.
> I've got indexs placed on the 'plate' column, and I've even tried
> setting up full-text indexing, none of which have increased the speed
> of the query:
> SET NOCOUNT ON
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> BEGIN
> SELECT 'Yes'
> END
> ELSE
> BEGIN
> SELECT 'N/A'
> END
> which, at [resent takes around 30 seconds to complete, so running a
> report for say 300 number plates to find if they have been ordered or
> not, takes around 30+ minutes to complete.
> If there another way of doing things that any one can suggest? Apart
> from splitting the strings up?|||Having a major braindead day today, so forgive me for this...
how would I go about counting the number of commas in the data row?
Adi wrote:
> You run into performance problem because you store few plates in one
> row. If you can modify the database and store each plate in its own
> row, then you won't have to use wildcard in the beginning of your
> search criteria and the server will be able to use index seek instead
> of table scan. If you must use one row to store few plates, then you
> can try something else. In your post you said that most plates are in
> there own rows and only some rows store more then one plate. If only
> small percentage of the rows store few plates, then maybe this will
> help - create a computed column on the table that counts the number of
> comas in the column that holds the registration plate. Then create an
> index on that column. Modify you query so it will look like this:
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> 1)
> OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
>
> This might cause the server to use the indexes, but it depends on the
> number of rows that contain more then one plate.
> Adi
> pinhead wrote:
> > I have problem with the speed of queries.
> >
> > I'm searching for UK car number (registration) plates consisting of 2
> > letters, followed by 2 numbers, followed by 3 letters.
> >
> > ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> >
> > Most of these number plates are in their own separate row, but some of
> > them are in a comma delimited string,
> >
> > Now suppose I want to find 'AB12ABC' in a string that consists of
> > 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> >
> > Now at the moment, I've got a database table consisting of nearly
> > 380,000 rows.
> >
> > I've got indexs placed on the 'plate' column, and I've even tried
> > setting up full-text indexing, none of which have increased the speed
> > of the query:
> >
> > SET NOCOUNT ON
> >
> > IF EXISTS (SELECT id
> > FROM vclivePlates
> > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> > BEGIN
> > SELECT 'Yes'
> > END
> > ELSE
> > BEGIN
> > SELECT 'N/A'
> > END
> >
> > which, at [resent takes around 30 seconds to complete, so running a
> > report for say 300 number plates to find if they have been ordered or
> > not, takes around 30+ minutes to complete.
> >
> > If there another way of doing things that any one can suggest? Apart
> > from splitting the strings up?|||Sorry -
DATALENGTH(plates) - DATALENGTH(REPLACE(plates, ',', ''))
works well for me
pinhead wrote:
> Having a major braindead day today, so forgive me for this...
> how would I go about counting the number of commas in the data row?
>
> Adi wrote:
> > You run into performance problem because you store few plates in one
> > row. If you can modify the database and store each plate in its own
> > row, then you won't have to use wildcard in the beginning of your
> > search criteria and the server will be able to use index seek instead
> > of table scan. If you must use one row to store few plates, then you
> > can try something else. In your post you said that most plates are in
> > there own rows and only some rows store more then one plate. If only
> > small percentage of the rows store few plates, then maybe this will
> > help - create a computed column on the table that counts the number of
> > comas in the column that holds the registration plate. Then create an
> > index on that column. Modify you query so it will look like this:
> >
> > IF EXISTS (SELECT id
> > FROM vclivePlates
> > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> > 1)
> > OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
> >
> >
> > This might cause the server to use the indexes, but it depends on the
> > number of rows that contain more then one plate.
> >
> > Adi
> >
> > pinhead wrote:
> > > I have problem with the speed of queries.
> > >
> > > I'm searching for UK car number (registration) plates consisting of 2
> > > letters, followed by 2 numbers, followed by 3 letters.
> > >
> > > ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> > >
> > > Most of these number plates are in their own separate row, but some of
> > > them are in a comma delimited string,
> > >
> > > Now suppose I want to find 'AB12ABC' in a string that consists of
> > > 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> > >
> > > Now at the moment, I've got a database table consisting of nearly
> > > 380,000 rows.
> > >
> > > I've got indexs placed on the 'plate' column, and I've even tried
> > > setting up full-text indexing, none of which have increased the speed
> > > of the query:
> > >
> > > SET NOCOUNT ON
> > >
> > > IF EXISTS (SELECT id
> > > FROM vclivePlates
> > > WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> > > BEGIN
> > > SELECT 'Yes'
> > > END
> > > ELSE
> > > BEGIN
> > > SELECT 'N/A'
> > > END
> > >
> > > which, at [resent takes around 30 seconds to complete, so running a
> > > report for say 300 number plates to find if they have been ordered or
> > > not, takes around 30+ minutes to complete.
> > >
> > > If there another way of doing things that any one can suggest? Apart
> > > from splitting the strings up?sql

Full Text in Portuguese

Hi,
I’m trying to use Full Text Index with Portuguese language. It seems to be
everything well configure in the SQL 2005 Server, but when I do queries to
the database it returns me values that contains Portuguese noise words (that
are included in the noise words file noisePTS.txt in the server). Any idea?
Thanks,
Rui ReisHello,
When you create full text index of table, did you choose Portuguese
language word breaker for indexed columns? If not, you may want to create
new catalog to test
Since the issue is related to specific local language, you may want to
contact local support for more qualified support. Please see
http://support.microsoft.com for regional support phone numbers.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||This seems odd to me. Granted, I have never used Full Text Indexing, but if
the character set is understood by the system, I don't see why the language
would make any difference. I would expect noise words to work the same,
regardless of the language.
Have you tested the noise words with any other language to determine that
this is indeed the problem? I would try some english noise words in a
custom noise file and see if you have the same issue. If you still have the
same issue, then you can pretty much conclude that the problem is with the
system configuration rather than the language.
"Tecnica" <abola@.nospam.nospam> wrote in message
news:95D051B8-D8B9-44D7-8839-C1088674ABF5@.microsoft.com...
> Hi,
> I'm trying to use Full Text Index with Portuguese language. It seems to be
> everything well configure in the SQL 2005 Server, but when I do queries to
> the database it returns me values that contains Portuguese noise words
(that
> are included in the noise words file noisePTS.txt in the server). Any
idea?
> Thanks,
> Rui Reis
>

Full Text Catalog MSSearch.exe owning CPU cycles (60-80%)

I've got a server that even when it's not being used for any queries is running at 60-80%cpu capacity. I've got full text catalog going on on the sql server and noticed that MSSearch.exe (related to full text search) is taking up almost all my CPU Time.

why is this? and what can I do to stop it?

Does your CPU cycle comes down when you stop your Full text catlog on your server?|||

I believe that is by default as and when the FT catalog population occurs on my server, I see the same.

Also mention the service pack level on SQL Server.

|||

MSSearch.exe is Microsoft Desktop Search. The SQL Server full text search service is msftesql.exe. I don't think your issue is actually related to your full-text catalog in SQL Server.

You can restrict Microsoft Desktop Search to only index certain directories, which can help reduce its overhead in the future once it is done indexing.

Hope this helps,
Steve

|||

We are also having some issues here. First, Commerce 2007 out of the box is setup for 10 Full Text Catalogs. This is not enough for us, but just bumping up the number allowed after the 10 are already created seems to have no effect. I do not get any more full text catalogs even though I am adding lots of new virtual catalogs to the server. I would like to know how to increase this and actually have it take effect.

Second, we bulk load our catalogs, we do not "Import" them. I would like to disable all of the full text indexing and catalog building while I am loading. Would I just use sp_fulltext_table to do this? Is there a Commerce 2007 API that I need to call so that Commerce Server is aware that I do not want this enabled. I then want to turn it back on when I finish my bulk load and let it incrementally populate.

Thanks!

-Vince

|||

I'm sorry I'm on SQL Server 2000 and MSSearch IS used for indexing on that and all previous versions I believe. it changed in sql server 2005, but before it was mssearch.exe.

I've just recently learned of using the sp_fulltext_service built in stored procedure...

check this out for all of you who are finding FTS to be a resouce pig:

http://technet.microsoft.com/en-us/library/ms175058.aspx

syntax used is

sp_fulltext_service 'resource_usage', 2
GO

by default the resource_usage is set to 3... I've turned it down to 1 and when I've done that the ram usage drops from 150,000k to 15,000 and doesn't go over 20,000 really.

also if another process (LIKE SQL SERVER) needs the CPU's and if you've got resource usage down to 1 it will pause indexing until the processor demand is lowered again.

THIS HAS PROVEN VERY HELPFUL!

enjoy!

Full Text Catalog MSSearch.exe owning CPU cycles (60-80%)

I've got a server that even when it's not being used for any queries is running at 60-80%cpu capacity. I've got full text catalog going on on the sql server and noticed that MSSearch.exe (related to full text search) is taking up almost all my CPU Time.

why is this? and what can I do to stop it?

Does your CPU cycle comes down when you stop your Full text catlog on your server?|||

I believe that is by default as and when the FT catalog population occurs on my server, I see the same.

Also mention the service pack level on SQL Server.

|||

MSSearch.exe is Microsoft Desktop Search. The SQL Server full text search service is msftesql.exe. I don't think your issue is actually related to your full-text catalog in SQL Server.

You can restrict Microsoft Desktop Search to only index certain directories, which can help reduce its overhead in the future once it is done indexing.

Hope this helps,
Steve

|||

We are also having some issues here. First, Commerce 2007 out of the box is setup for 10 Full Text Catalogs. This is not enough for us, but just bumping up the number allowed after the 10 are already created seems to have no effect. I do not get any more full text catalogs even though I am adding lots of new virtual catalogs to the server. I would like to know how to increase this and actually have it take effect.

Second, we bulk load our catalogs, we do not "Import" them. I would like to disable all of the full text indexing and catalog building while I am loading. Would I just use sp_fulltext_table to do this? Is there a Commerce 2007 API that I need to call so that Commerce Server is aware that I do not want this enabled. I then want to turn it back on when I finish my bulk load and let it incrementally populate.

Thanks!

-Vince

|||

I'm sorry I'm on SQL Server 2000 and MSSearch IS used for indexing on that and all previous versions I believe. it changed in sql server 2005, but before it was mssearch.exe.

I've just recently learned of using the sp_fulltext_service built in stored procedure...

check this out for all of you who are finding FTS to be a resouce pig:

http://technet.microsoft.com/en-us/library/ms175058.aspx

syntax used is

sp_fulltext_service 'resource_usage', 2
GO

by default the resource_usage is set to 3... I've turned it down to 1 and when I've done that the ram usage drops from 150,000k to 15,000 and doesn't go over 20,000 really.

also if another process (LIKE SQL SERVER) needs the CPU's and if you've got resource usage down to 1 it will pause indexing until the processor demand is lowered again.

THIS HAS PROVEN VERY HELPFUL!

enjoy!

full text body search not finding all results

I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
Archiver. When I run queries against the database for a specific word i.e
"testing", only 2 results show up when there should be more results.
I know the data is there because I can see it, it's just that when I do a
search it doesn't find it. I have full text-catalog enabled on the database.
I've tried deleting and re-creating the catalog but still the same thing.
The question is are they really there or are they being indexed.
Use a like to verify that it is really there. If it is then it is a problem
with it being indexed. Review the gatherer logs to see if it indexes the
rows correctly.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
> Archiver. When I run queries against the database for a specific word i.e
> "testing", only 2 results show up when there should be more results.
> I know the data is there because I can see it, it's just that when I do a
> search it doesn't find it. I have full text-catalog enabled on the
> database.
>
> I've tried deleting and re-creating the catalog but still the same thing.
|||That's what my problem is, it's not being indexed correctly.
How do I fix that?
"Hilary Cotter" wrote:

> The question is are they really there or are they being indexed.
> Use a like to verify that it is really there. If it is then it is a problem
> with it being indexed. Review the gatherer logs to see if it indexes the
> rows correctly.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>
>
|||Can you see if there are any errors in the gatherer logs? If not can you
send some of the problem docs to me offline or post them here? Send me the
originals before they went into the database.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...[vbcol=seagreen]
> That's what my problem is, it's not being indexed correctly.
> How do I fix that?
> "Hilary Cotter" wrote:
|||I'm new to SQL so I'll need some help here.
1. How do I get gatherer logs?
2. When you say problem docs, what do you mean exactly? (these are test
emails).
"Hilary Cotter" wrote:

> Can you see if there are any errors in the gatherer logs? If not can you
> send some of the problem docs to me offline or post them here? Send me the
> originals before they went into the database.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...
>
>
|||You can find the gatherer logs on a SQL 2005 server in C:\Program
Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
Where X is your instance name.
The gatherer logs themselves will look like this:
SQLFT0001000015.LOG
test email should be indexable in a char or varchar column. Attachments may
not be depending on how you store them. Are you storing them in varbinary or
image columns? Do you have a document type column associated with the image
or varbinary columns?
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...[vbcol=seagreen]
> I'm new to SQL so I'll need some help here.
> 1. How do I get gatherer logs?
> 2. When you say problem docs, what do you mean exactly? (these are test
> emails).
> "Hilary Cotter" wrote:
|||Ok, I'll look at them. I assume I just open them with Notepad. What am I
looking for?
As far as your questions:
1. Not sure if they're being stored in verbinary or image columns. However
this GFI Mail Archive stores it.
2. Don't know if I have a document type column associated with the
verbinary or image columns.
"Hilary Cotter" wrote:

> You can find the gatherer logs on a SQL 2005 server in C:\Program
> Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
> Where X is your instance name.
> The gatherer logs themselves will look like this:
> SQLFT0001000015.LOG
> test email should be indexable in a char or varchar column. Attachments may
> not be depending on how you store them. Are you storing them in varbinary or
> image columns? Do you have a document type column associated with the image
> or varbinary columns?
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...
>
>
|||Can you script out your table, indexes, and full text indexes and post them
here.
Key to solving your problem is discovering how the GFI archive stores it. If
it is text, or msg you should be fine. If it is something proprietary you
will be unable to index them.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...[vbcol=seagreen]
> Ok, I'll look at them. I assume I just open them with Notepad. What am I
> looking for?
> As far as your questions:
> 1. Not sure if they're being stored in verbinary or image columns.
> However
> this GFI Mail Archive stores it.
> 2. Don't know if I have a document type column associated with the
> verbinary or image columns.
> "Hilary Cotter" wrote:
|||I went to the GFI web site and notice that their product does work with SQL
FTS. Their technical support group should be able to help you through this
problem.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lkK28IEHHA.3520@.TK2MSFTNGP04.phx.gbl...
> Can you script out your table, indexes, and full text indexes and post
> them here.
> Key to solving your problem is discovering how the GFI archive stores it.
> If it is text, or msg you should be fine. If it is something proprietary
> you will be unable to index them.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...
>

Full Cached Lookup with Parameters

Parameterized queries are only allowed on partial or none cache style lookup transforms, not 'full' ones. Is there some "trick" to parameterizing a full cache lookup, or should the join simply be done at the source, obviating the need for a full cache lookup at all (other suggestion certainly welcome)

More particularly, I'd like to use the lookup transform in a surrogate key pipeline. However, the dimension is large (900 million rows), so its would be useful to restrict the lookup transform's cache by a join to the source.

For example:

Source query is: select a,b,c from t where z=@.filter (20,000 rows)

Lookup transform query: select surrogate_key,business_key from dimension (900 M rows, not tenable)

Ideal Lookup transform query:

select distinct surrogate_key

,business_key

from dimension d inner join

t on d.business_key = t.c

where t.z = @.filter

Where do the parameters come from? Obviously, you can't parametrize based on incoming row data, because in full cache mode the lookup data is cached before the incoming data is processed.

So your choice is to have some fixed query, or query based on package variables. If this is a fixed query, just create a stored procedure and use it as source. Unfortunately, Lookup Transform does not provide a way to pass variables to the query, but you can probably workaround it by having an auxilarly SQL table where you can store the values of the variables, and then use it in the stored procedure you call from Lookup.|||

You are correct, the filter is based on a package variable. I suppose an auxiiliary SQL table for variable storage is the way to go. I was hoping to get away without creating more tables, but its not really much of a price to pay.

Thanks.

|||Hi,

I have a workaround in my blog post here. Simon Sabin has another one in the comments.

Regards,

dong|||

I use a different approach than the view or file because many instances of the package will be running simultaneously, only differing with regard to the parameters.

The workaround involves using the "Application Name = ;' parameter of the connection string, which maps to the program_name attributes of master..sysprocesses in SQL Server.

Public Sub Main()

Dim connString As OleDbConnectionStringBuilder = New OleDbConnectionStringBuilder()

If Not Dts.Connections.Contains(connectionName) Then

Dts.Events.FireError(0, String.Empty, String.Format("Connection {0} not found", connectionName), String.Empty, 0)

End If

If Dts.Connections.Contains(connectionName) Then

connString.ConnectionString = Dts.Connections(connectionName).ConnectionString

If connString.ContainsKey(ApplicationName) Then

connString.Remove(ApplicationName)

End If

connString.Add(ApplicationName, "SSIS:" + Dts.Variables("User::TheParameter").Value.ToString())

Dts.Connections(connectionName).ConnectionString = connString.ToString()

Dts.Events.FireInformation(0, String.Empty, connectionName + " connection string set to " + connString.ToString(), String.Empty, 0, True)

End If

Dts.TaskResult = Dts.Results.Success

End Sub

|||A bit confused here. I guess the above code is trying to create OLE-DB connections with different Application Names. But where is the logic to limit the query result to be only a sub-set of the lookup table?|||Yes, the script does change the application name in the connection string for the OLEDB connections. The point of the script is the make the application name available in the program_name column of master..sysprocesses. master.dbo.sysprocesses.program_name is then extracted with a table valued function (see getID() below), which is joined to the lookup table, mimicing a parameter.

-- fully cached lookup query (parameterized via table valued function)
select *
from lookup t
inner join dbo.getID() l
where t.theID = l.theID

-- table valued function
CREATE FUNCTION [dbo].[getID] ()
RETURNS @.T TABLE
(theID INT NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT @.T (theID)
SELECT top 1 theID = CAST(REPLACE(program_name,'SSIS:','') as int)
FROM master..sysprocesses
WHERE spid = @.@.SPID
AND ISNUMERIC(REPLACE(program_name,'SSIS:','')) = 1
RETURN
END

FTS in SQL2005 and JOINS

Hi, I'm having a slight problem with some of my FTS queries, namely it
seems that in SQL 2005 the execution plans are constructed by first
doing the normal query and then joining with the FTS results.
For instance:
SELECT P.id,P.name
FROM CONTAINSTABLE(prd_Names,phrase,'"hp*"') F
INNER JOIN prd_Names P ON F.[key]=P.id
ORDER BY P.name,F.rank;
Causes a massive index scan on prd_Names table first and only then
joins with the smaller set returned by CONTAINSTABLE. IIRC in 2000 I
didn't have this problem (I think it was the other way around).
Is there any way to make the query behave properly?
The DTD is very simple prd_Names is a table with 3 columns
id (pkey) int
name varchar(100)
phrase varchar(500)
a clustered index on id
and an index on the name column.
BTW the table contains ~400k rows.
Thanks.
I've no idea whether this would work, but have you tried to re-order the
query so that the inner join is the other way around?
Griff
|||Griff wrote:
> I've no idea whether this would work, but have you tried to re-order the
> query so that the inner join is the other way around?
Yep, tried that with no effect. I also rewrote it without using the
JOIN statement: WHERE CONTAINS(..) etc. and also WHERE P.id IN (SELECT
... FROM CONTAINSTABLE()) but neither helped.
Thanks.
|||I think I've missed something - why are you doing an inner join in the first
place?
|||Griff wrote:
> I think I've missed something - why are you doing an inner join in the first
> place?
Because CONTAINSTABLE returns a table with [key],[rank] columns which I
need (afaik it's the normal way to use CONTAINSTABLE)