Full Text: FASTEST update

Hi,
My users must be able to do a full text search, and add items to the
database if they aren't in yet. But once an item is added, in a lot of cases
they need it directly after it.
But when they search for it after the insert, the article isn't found... It
takes at least 10 seconds before it's found in the full text index...
Is there a way to speed it up? That speed is really very important for my
applciation! I use now "Change tracking" and "Update Index in background".
Should I use incremential population? Without the "update index in
background"? When I don't use "update index in background": does this mean
that it will update the index immediately in the mean-thread (and will be
faster) or that it won't update at all?
Thanks a lot in advance,
Pieter
Pieter wrote on Thu, 9 Feb 2006 12:27:41 +0100:

> Hi,
> My users must be able to do a full text search, and add items to the
> database if they aren't in yet. But once an item is added, in a lot of
> cases they need it directly after it.
> But when they search for it after the insert, the article isn't found...
> It takes at least 10 seconds before it's found in the full text index...
> Is there a way to speed it up? That speed is really very important for my
> applciation! I use now "Change tracking" and "Update Index in background".
> Should I use incremential population? Without the "update index in
> background"? When I don't use "update index in background": does this mean
> that it will update the index immediately in the mean-thread (and will be
> faster) or that it won't update at all?
I don't think you'll get it faster than using Change Tracking with Update
Index In Background. Incremental is used when you want to create all the new
entries since the last full/incremental population, where you don't want
near real-time updates. If you disable Change Tracking then you will have to
schedule Incremental and/or Full populations yourself.
Dan
|||10 seconds is pretty good, especially if your content is binary.
Note that change tracking with update index in background provides near real
time indexing (10s or less). When you don't use this option the changes are
logged in a file and when you issue an update index they are indexed.
Incremental means that each row is retrieved and if it has been
modified/deleted/inserted it is reindexed. This can take as long or longer
than a full population.
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
"Pieter" <pietercoucke@.hotmail.com> wrote in message
news:OlyaZvWLGHA.3276@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My users must be able to do a full text search, and add items to the
> database if they aren't in yet. But once an item is added, in a lot of
> cases they need it directly after it.
> But when they search for it after the insert, the article isn't found...
> It takes at least 10 seconds before it's found in the full text index...
> Is there a way to speed it up? That speed is really very important for my
> applciation! I use now "Change tracking" and "Update Index in background".
> Should I use incremential population? Without the "update index in
> background"? When I don't use "update index in background": does this mean
> that it will update the index immediately in the mean-thread (and will be
> faster) or that it won't update at all?
> Thanks a lot in advance,
> Pieter
>
>

Full Text Search - How do I best update Index - Merge Replication

Hi,
How do I best update a Full Text Index on SQL2000 sp3, for Merge Replication
(Push Subscription)?
Here is the config:
Site 1:
I have one table with one column (ntext), which I have created a full text
index.
The table updated once per day, data is never updated, always insert new
rows.
The table is Read-Only.
Everyone in Site 1 is happy... full text working good.
Site2: - Branch Office
I have created a merge replication, (push subscription on Site1):
We now have a copy of this read-only database/table in site #2.
I created a full text index on it, this is also working good.
Now after 24 hours, Site 1 gets 1000 new rows of data.
Since I have a push subscription, these new 1000 rows of data will get
pushed to site 2.
How do I automatic the full text index so it will add these new rows (column
data) to the full text index?
Once again, these are not updates, always new rows of data.
I would like to avoid using the Full Text Scheduler.
If I configured a Trigger on Site 2, how would it know when the 1000 rows
where finished inserting rows?
Thanks
Russell Mangel
Las Vegas, NV
use change tracking with update index in background on the subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Russell Mangel" <russell@.tymer.net> wrote in message
news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I best update a Full Text Index on SQL2000 sp3, for Merge
Replication
> (Push Subscription)?
> Here is the config:
> Site 1:
> I have one table with one column (ntext), which I have created a full text
> index.
> The table updated once per day, data is never updated, always insert new
> rows.
> The table is Read-Only.
> Everyone in Site 1 is happy... full text working good.
> Site2: - Branch Office
> I have created a merge replication, (push subscription on Site1):
> We now have a copy of this read-only database/table in site #2.
> I created a full text index on it, this is also working good.
> Now after 24 hours, Site 1 gets 1000 new rows of data.
> Since I have a push subscription, these new 1000 rows of data will get
> pushed to site 2.
> How do I automatic the full text index so it will add these new rows
(column
> data) to the full text index?
> Once again, these are not updates, always new rows of data.
> I would like to avoid using the Full Text Scheduler.
> If I configured a Trigger on Site 2, how would it know when the 1000 rows
> where finished inserting rows?
> Thanks
> Russell Mangel
> Las Vegas, NV
>
>
>
|||Thanks for your reply, I will try this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ub1IZWPeEHA.2908@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> use change tracking with update index in background on the subscriber.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Russell Mangel" <russell@.tymer.net> wrote in message
> news:eDrSSZHeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> Replication
text[vbcol=seagreen]
> (column
rows
>

Full Text Search - Auto Change Tracking

Id like to know a bit about the behaviour of CHANGE_TRACKING AUTO, specifically when the update of the index takes place. In the docs it states 'these changes may not be reflected immediately' but in general when can the changes be expected to appear. Is there merit to updating manually, although the user needs ALTER permission on the table in that case vs. letting SQL Server run it automatically.

Thanks for any help - MikeIn case you or anyone else has found any relevant information about this topic I'd also be interested to hear about it since the documentation doesn't really clear things up.

kind regards,
heinz

Full Text Search - Auto Change Tracking

Id like to know a bit about the behaviour of CHANGE_TRACKING AUTO, specifically when the update of the index takes place. In the docs it states 'these changes may not be reflected immediately' but in general when can the changes be expected to appear. Is there merit to updating manually, although the user needs ALTER permission on the table in that case vs. letting SQL Server run it automatically.

Thanks for any help - Mike
In case you or anyone else has found any relevant information about this topic I'd also be interested to hear about it since the documentation doesn't really clear things up.

kind regards,
heinz

Full Text Index

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thankshelloplease help me !!!thanks|||

Yavari:

helloin Full Text SearchAre there method when add record in Field for properties "Full Text Index " , update catalogs ?thanks

I have no idea what you are asking. Could you rephrase your question(s?) differently?

Full Outer Join question.

Hi,
If I have two tables A and B and I am trying to insert rows, update rows and
delete rows in table B based on table A is it possible to do it one SQL
statement with a full outer Join?. I have written the SQL for getting the
data using a full outer join but want to achieve Inserting , updating and
deleting records in tableB with one statement instead of having if then
logic. wondering if there is a way.
Here is my DDL:
CREATE TABLE tblA (ProdID INT Primary key,
ProdName VARCHAR (30)
)
CREATE TABLE tblB (ProdID INT,
ProdName VARCHAR (40)
)
INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
--The following SQL gets the rows that exist in A and dont exist in B and
those that exist in B and dont exist in A.
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
--update on table A
Update tblA Set ProdName='Juices and Drinks' where prodiD=30
If i run the update on the column prodname in tblA i can rewrite my SQL as
Select A.ProdID,A.ProdName,
B.ProdID,B.ProdName
FROM tblA AS A
FULL OUTER JOIN tblB AS B
ON A.ProdID=B.ProdID
WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
OR A.ProdName <> b.ProdName
to get the products whose name are different
My goal is
1). Insert rows into tblB where they dont exist in tblA .
2). Update prodname in tblB when the prodname in tblB does not match the
prodname in tblA
3). Delete those rows in tblB which dont at all exist in tblA.
I want to accomplish this using a full outer join with one single insert and
update and delete. If thats not possible without using IF ELSE then I want
to know the best way to write less code to accomplish this.
ThanksMeher,
Why not use transactional replication to keep tblB up-to-date with tblA?
HTH
Jerry
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>|||These will work:
insert into tblB(prodId, prodName)
select prodId, prodName
from tblA
where not exists
( select *
from tblB
where tblB.prodId = tblA.prodId)
Update tblB
set prodName = tblA.prodName
from tblB
join tblA
on tblA.prodId = tblB.prodId
where tblA.prodName <> tblB.prodName
delete tblB
where not exists
( select *
from tblA
where tblB.prodId = tblA.prodId)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Meher Malakapalli" <mmalakapalliNOSPAM@.cohesioninc.com> wrote in message
news:%23gUuPLIyFHA.736@.tk2msftngp13.phx.gbl...
> Hi,
> If I have two tables A and B and I am trying to insert rows, update rows
> and delete rows in table B based on table A is it possible to do it one
> SQL statement with a full outer Join?. I have written the SQL for getting
> the data using a full outer join but want to achieve Inserting , updating
> and deleting records in tableB with one statement instead of having if
> then logic. wondering if there is a way.
> Here is my DDL:
> CREATE TABLE tblA (ProdID INT Primary key,
> ProdName VARCHAR (30)
> )
> CREATE TABLE tblB (ProdID INT,
> ProdName VARCHAR (40)
> )
> INSERT INTO tblA (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblA (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblA (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblA (ProdID,ProdName) VALUES (40,'Coffee')
> INSERT INTO tblA (ProdID,ProdName) VALUES (50,'Paper')
> INSERT INTO tblA (ProdID,ProdName) VALUES (60,'Eggs')
>
> INSERT INTO tblB (ProdID,ProdName) VALUES (10,'Fruits')
> INSERT INTO tblB (ProdID,ProdName) VALUES (20,'Vegetables')
> INSERT INTO tblB (ProdID,ProdName) VALUES (30,'Juices')
> INSERT INTO tblB (ProdID,ProdName) VALUES (70,'Milk')
> --The following SQL gets the rows that exist in A and dont exist in B and
> those that exist in B and dont exist in A.
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> --update on table A
> Update tblA Set ProdName='Juices and Drinks' where prodiD=30
> If i run the update on the column prodname in tblA i can rewrite my SQL as
> Select A.ProdID,A.ProdName,
> B.ProdID,B.ProdName
> FROM tblA AS A
> FULL OUTER JOIN tblB AS B
> ON A.ProdID=B.ProdID
> WHERE (A.ProdiD IS NULL or B.ProdID IS NULL)
> OR A.ProdName <> b.ProdName
> to get the products whose name are different
> My goal is
> 1). Insert rows into tblB where they dont exist in tblA .
> 2). Update prodname in tblB when the prodname in tblB does not match the
> prodname in tblA
> 3). Delete those rows in tblB which dont at all exist in tblA.
> I want to accomplish this using a full outer join with one single insert
> and update and delete. If thats not possible without using IF ELSE then I
> want to know the best way to write less code to accomplish this.
> Thanks
>

Full Catalog Update

Hi,
I ve found an issue about my seach engine text by using full-text catalogs.
So, my question is.. (maybe is stupid)
How to automatically update the catalog, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into
the tables, the catalogue is not updated until the procedure starts ?
Stan>
> Hi,
> I ve found an issue about my seach engine text by using full-text
catalogs.
> So, my question is.. (maybe is stupid)
> How to automatically update the catalog, when I update the tables ?
> I ve seen there's a scheduler but does it mean, when I add some records
into
> the tables, the catalogue is not updated until the procedure starts ?
> Stan
--
Here is an extract from SQL Books online:
Maintaining Full-Text Indexes
There are three ways to maintain a full-text index:
Full rebuild
Rescans all rows. Completely rebuilds the full-text index. You can perform
a full rebuild immediately or on a schedule, using SQL Server Agent.
Timestamp-based incremental rebuild
Rescans those rows that have changed since the last full or incremental
rebuild. This requires a timestamp column on the table. Changes that do not
update the timestamp, such as WRITETEXT and UPDATETEXT, are not detected.
You can perform an incremental rebuild immediately or on a schedule.
Change tracking
Maintains a list of all changes to the indexed data. Changes made with
WRITETEXT and UPDATETEXT are not detected. You can update the full-text
index with these changes immediately, on a schedule, or as they occur,
using the background update index option.
The method you use depends on factors such as the CPU and available memory,
the amount and rate of change of data, the amount of available disk space,
and the importance of the full-text index being current. Use these
recommendations as a guide for selecting a maintenance method.
Use change tracking with the background update index option when CPU and
memory are available, the value of an up-to-date index is high, and
immediate propagation can keep up with the rate of changes.
Use change tracking with scheduled propagation when CPU and memory can be
used at scheduled times, disk space for storing changes is available, and
changes between the scheduled times are not so significant that the
propagation takes longer than a full rebuild.
Use a full rebuild when a large percentage of records change or are added
at once. If a large percentage of records change over an extended period of
time, consider using change tracking with scheduled or background update
index.
Use an incremental rebuild when a large number, but not a large percentage,
of documents change at one time. If a large number of records change over
an extended period of time, consider using change tracking with scheduled
or background update index.
Hope this helps,
--
Eric Cárdenas
support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Full Catalog Update

Hi,
I ve found an issue about my seach engine text by using full-text catalogs.
So, my question is.. (maybe is stupid)
How to automatically update the catalog, when I update the tables ?
I ve seen there's a scheduler but does it mean, when I add some records into
the tables, the catalogue is not updated until the procedure starts ?
Stan
>
> Hi,
> I ve found an issue about my seach engine text by using full-text
catalogs.
> So, my question is.. (maybe is stupid)
> How to automatically update the catalog, when I update the tables ?
> I ve seen there's a scheduler but does it mean, when I add some records
into
> the tables, the catalogue is not updated until the procedure starts ?
> Stan
Here is an extract from SQL Books online:
Maintaining Full-Text Indexes
There are three ways to maintain a full-text index:
Full rebuild
Rescans all rows. Completely rebuilds the full-text index. You can perform
a full rebuild immediately or on a schedule, using SQL Server Agent.
Timestamp-based incremental rebuild
Rescans those rows that have changed since the last full or incremental
rebuild. This requires a timestamp column on the table. Changes that do not
update the timestamp, such as WRITETEXT and UPDATETEXT, are not detected.
You can perform an incremental rebuild immediately or on a schedule.
Change tracking
Maintains a list of all changes to the indexed data. Changes made with
WRITETEXT and UPDATETEXT are not detected. You can update the full-text
index with these changes immediately, on a schedule, or as they occur,
using the background update index option.
The method you use depends on factors such as the CPU and available memory,
the amount and rate of change of data, the amount of available disk space,
and the importance of the full-text index being current. Use these
recommendations as a guide for selecting a maintenance method.
Use change tracking with the background update index option when CPU and
memory are available, the value of an up-to-date index is high, and
immediate propagation can keep up with the rate of changes.
Use change tracking with scheduled propagation when CPU and memory can be
used at scheduled times, disk space for storing changes is available, and
changes between the scheduled times are not so significant that the
propagation takes longer than a full rebuild.
Use a full rebuild when a large percentage of records change or are added
at once. If a large percentage of records change over an extended period of
time, consider using change tracking with scheduled or background update
index.
Use an incremental rebuild when a large number, but not a large percentage,
of documents change at one time. If a large number of records change over
an extended period of time, consider using change tracking with scheduled
or background update index.
Hope this helps,
Eric Crdenas
support professional
This posting is provided "AS IS" with no warranties, and confers no rights.