8

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around 250 distinct COMPANYIDs over those 12 million rows.

Is it possible, when defining the full-text indices, to give each COMPANY its own "branch" in the tree?

4
  • Are you seeing performance problems now? E.g. if there is an index on CompanyID, and you filter on that, are you seeing the same full-text performance as if there were no filter on that column? I don't have a ton of experience with I would hope SQL Server is smart enough to narrow down the full-text search space to the rows that match the less expensive filter first. Sep 5 '14 at 14:41
  • Actually, I have just written the app for one company so far, and everyone liked it so much they want me to put it into production for all companies, and I haven't had a chance to create a mockup with 12 million meaningful dummy data rows yet. Values like "Lastname1", "Lastname2", "City1", etc won't have enough variation and could skew the test results. The data change so frequently that I'm not sure SQL Server will reliably know which index is the narrower one in any given query, and the number of rows per company varies greatly. One company might have only 1000 rows, another 60,000.
    – Tim
    Sep 5 '14 at 18:47
  • Nobody here is going to be able to speculate, given the level of detail here, how well SQL Server will handle this scenario. You're going to have to construct realistic, meaningful test data and run tests of your workload on your hardware... Sep 5 '14 at 18:55
  • But I would still like an answer to my question. I'm not asking anybody to speculate.
    – Tim
    Sep 5 '14 at 21:15
3

No is the short answer, and you don't really need this. Full-text indexes are inverted indexes so they store the split words by the unique doc_id that you have to specify when you create the full-text index. This must be a "unique, single-key, non-nullable column" ideally an integer. What is essentially a foreign key does not figure and there is no easy way to partition them on that basis.

You could spoof something like this it with a table per company and full-text index per table. You would need some kind of code logic sat in front to determine which table to insert to / fetch from. This would be a considerable headache to manage at is almost certainly not worth it.

If you had some serious volume ( eg more like 23 billion records ) then you could look at a sharding solution, eg something like an Azure VM per company with an app sat in front of them to determine which machine to connect to. But clearly you don't need that either.

There were also a number of improvements in SQL 2008 to full-text which is now more integrated into the database engine. One scenario, where you specify a WHERE clause against a normal column and use the full-text functions, is referred to as a 'Mixed Query' and discussed here. This is still a great article even though the information is for SQL 2008.

If you're generally concerned about performance and plans, why not spin up some test data, introduce some skew and try it out. I knocked up this script with ~2 million rows in a few minutes:

!!TODO introduce some skew
USE master
GO

SET NOCOUNT ON
GO

DBCC TRACEON(610)   -- Minimal logging
GO

GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
BEGIN
    ALTER DATABASE fullTextDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo' )
CREATE DATABASE fullTextDemo
GO

ALTER DATABASE fullTextDemo SET RECOVERY SIMPLE
GO

USE fullTextDemo
GO

IF OBJECT_ID('dbo.yourAddresses') IS NOT NULL DROP TABLE dbo.yourAddresses
IF OBJECT_ID('dbo.companies') IS NOT NULL DROP TABLE dbo.companies
GO

CREATE TABLE dbo.companies (
    companyId       INT IDENTITY NOT NULL,
    companyName     NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_companies PRIMARY KEY ( companyId )
)
GO

CREATE TABLE dbo.yourAddresses (
    rowId           INT IDENTITY,
    companyId       INT NOT NULL FOREIGN KEY REFERENCES dbo.companies ( companyId ),
    searchTerms     NVARCHAR(2048) NOT NULL

    CONSTRAINT PK_yourAddresses PRIMARY KEY ( rowId )
)
GO

-- Populate the companies
;WITH cte AS (
SELECT TOP 250 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.companies ( companyName )
SELECT NEWID()
FROM cte
GO

-- Generate 2,636,000 records
INSERT dbo.yourAddresses ( companyId, searchTerms )
SELECT c.companyId, m.[text]
FROM dbo.companies c
    CROSS JOIN ( SELECT * FROM sys.messages ) m
WHERE m.language_id = 1033
AND m.[text] Like '[a-z]%'
GO

CREATE INDEX _idx ON dbo.yourAddresses ( companyId ) INCLUDE ( searchTerms )
GO

-- !!TODO look at compression
--ALTER INDEX PK_yourAddresses ON dbo.yourAddresses REBUILD WITH ( DATA_COMPRESSION = PAGE )
--GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_yourAddresses' )
CREATE FULLTEXT CATALOG ftc_yourAddresses
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.yourAddresses ( searchTerms ) KEY INDEX PK_yourAddresses ON ftc_yourAddresses WITH CHANGE_TRACKING MANUAL  -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

ALTER FULLTEXT INDEX ON dbo.yourAddresses START FULL POPULATION;
GO


DECLARE @i INT 
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO



SELECT TOP 1000 *
FROM dbo.yourAddresses ft
WHERE companyId = 42
 AND CONTAINS ( searchTerms, 'data' )
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
GO

SELECT TOP 1000 *
FROM dbo.yourAddresses a
    INNER JOIN CONTAINSTABLE ( dbo.yourAddresses, searchTerms, 'data' ) ct ON a.rowId = ct.[key]
WHERE a.companyId = 42
OPTION ( MERGE JOIN )
GO

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords (DB_ID(), OBJECT_ID('dbo.yourAddresses') )

SELECT TOP 100 *
FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('dbo.yourAddresses') )
ORDER BY document_id
GO
3
  • Thanks very much for taking the time to write that script, for the link to the "mixed" query article, and for the billions versus millions perspective :-)
    – Tim
    Sep 7 '14 at 10:45
  • 1
    According to the article, a solution to the underlying problem was introduced in SQL Server 2008.
    – Tim
    Sep 7 '14 at 10:51
  • Glad it was useful. I should probably say the covering index and query hint in my script are just experiments not recommendations, these are options you might have if you do have performance problems. The index is potentially a bit wide and the usual warnings with hints apply.
    – wBob
    Sep 7 '14 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.