Re: Indexing delay for one row.
From: John Kane (jt-kane_at_comcast.net)
Date: 03/07/04
- Next message: John Kane: "Re: Indexing delay for one row."
- Previous message: Brad: "Re: SQL search condition"
- In reply to: Jean G.: "Re: Indexing delay for one row."
- Next in thread: John Kane: "Re: Indexing delay for one row."
- Reply: John Kane: "Re: Indexing delay for one row."
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 7 Mar 2004 13:14:16 -0800
You're welcome, Jean,
Interesting... I can confirm an increase in the pooling / updating when
using Change Tracking with Update Index in Background, using the below SQL
script and WAITFOR and varying the delay from 1 to 18 seconds. I get no
results until approx. a 15 second delay and then inconsistant results
between 16 to 18 seconds delay.
Jean, could you confirm your exact version of SQL Server that you are seeing
this behievor - via SELECT @@version ?
I can repo this on SQL Server 2000 SP3, but will also attempt a repo on SP2.
It is possible that a change was made to the pooling frequence under SP3 to
reduce the overhead for SQL Server when there is little FT update activity.
I'll try and confirm this with Microsoft.
-- Repo of CT with UIiB pooling frequency change under SQL Server 2000
SP3...
use pubs
go
select @@version -- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be sysname
TimeStampCol timestamp NULL
)
go
-- Truncate Data, if necessary..
-- TRUNCATE TABLE FTSTable
Insert FTSTable values ('Mary had a little lamb', replicate('0x1',100),
'txt', NULL)
Insert FTSTable values ('My dog barked loudly', replicate('0x7',300), 'txt',
NULL)
Insert FTSTable values ('The fox jumped over the fence',
replicate('0x8',800), 'txt', NULL)
Insert FTSTable values ('I dove into the pond', replicate('0x2',100), 'txt',
NULL)
-- Full-Text Indexing (FTI) setup using "Change Tracking" and "Update Index
in Background"...
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','TextCol','add' -- assumes English
language text
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
go
-- Start FT Indexing... and confirm via "Microsoft Search" source entries in
the server's Application Event log...
EXEC sp_fulltext_table 'FTSTable', 'Start_change_tracking'
EXEC sp_fulltext_table 'FTSTable', 'Start_background_updateindex'
go
-- Insert additional and unique word with CT and UIiB enabled - Delete,
Insert and increase waitfor delay until select 1 row
DELETE FTSTable where TextCol like '%text%' -- delete inserted row, if
necessary
INSERT FTSTable values('Test TEXT Data for row 5', NULL, 'doc', NULL)
WAITFOR DELAY '00:00:16' -- wait for x seconds and then run SQL FTS query to
get estimate of return time
select TextCol from FTSTable where contains(TextCol,'test')
-- results: @ 15 seconds - 0 rows, @ 16 seconds - 1 row, @ 17 seconds - 1
row, @ 18 seconds - 1 row
select * from FTSTable
/* -- Clean up: Un-comment the following code to remove the FT Catalog,
tables
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
go
*/
Regards,
John
"Jean G." <jgrandbois@esri.com> wrote in message
news:13481311-6636-4B56-AF30-B90D7D791FD8@microsoft.com...
> John, thanks for your interest. Below are the results of the queries you
asked for. As you can see, the table is quite small, with just one row in
it. The text is "my dog has fleas". It's the only index in the catalog.
After cutting and pasting this info for you, I inserted a second row, just
to be sure I wasn't imagining things. And sure enough, it took another 20
seconds. This is just a toy table/catalog I created to demonstrate the delay
behavior I observed on a larger system.
>
> SELECT @@language
> us_english
>
> SELECT @@version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
>
> SELECT FullTextServiceProperty('ResourceUsage') as MSSearch_Resource_Usage
> 3
>
> EXEC sp_help_fulltext_catalogs
> ftcatid NAME PATH STATUS
NUMBER_FULLTEXT_TABLES
> -------------------------------------------------------------------------
> 5 SDE_DEFAULT_CAT e:\applications\sql server 0 4
> 6 CAT2 e:\applications\sql server 0 1
>
>
> EXEC sp_help_fulltext_tables
> TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME FULLTEXT_KEY_COLID
FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
> --------------------------------------------------------------------------
--------------
> sdetest ft_test ft_test_pk 1 1 CAT2
> sdetest sde_xml_idx832 xmlix832_pk 1 1
SDE_DEFAULT_CAT
> sdetest sde_xml_idx851 xmlix851_pk 1 1
SDE_DEFAULT_CAT
> sdetest sde_xml_idx853 xmlix853_pk 1 1
SDE_DEFAULT_CAT
> sdetest sde_xml_idx855 xmlix855_pk 1 1
SDE_DEFAULT_CAT
>
> EXEC sp_help_fulltext_columns
> TABLE_OWNER TABLE_ID TABLE_NAME FULLTEXT_COLUMN_NAME FULLTEXT_COLID
FULLTEXT_BLOBTP_COLNAME
> FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE
> --------------------------------------------------------------------------
-------------------------
> sdetest 1517612845 ft_test text_tag 2
NULL NULL 1033
> sdetest 1245611876 sde_xml_idx832 text_tag 5
NULL NULL 1033
> sdetest 1329088171 sde_xml_idx851 text_tag 5
NULL NULL 1033
> sdetest 621609653 sde_xml_idx853 text_tag 5
NULL NULL 1033
> sdetest 1405612446 sde_xml_idx855 text_tag 5
NULL NULL 1033
>
>
> select avg(datalength(text_tag)) "Avg. Text Length" from ft_test
> 16
>
> select max(datalength(text_tag)) "Max. Text Length" from ft_test
> 16
- Next message: John Kane: "Re: Indexing delay for one row."
- Previous message: Brad: "Re: SQL search condition"
- In reply to: Jean G.: "Re: Indexing delay for one row."
- Next in thread: John Kane: "Re: Indexing delay for one row."
- Reply: John Kane: "Re: Indexing delay for one row."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
