多表多列自由文本搜索性能优化方案咨询

Great question! Let's break down your current implementation and walk through actionable optimizations to get that real-time search performance you need.

First, let's recap your scenario: you have three entity tables (dbo.Electrician, dbo.Painters, dbo.Clients) and need to support free-text search across all of them to return matching entities. Your current cursor-based + LIKE matching approach isn't fast enough for real-time use, even after trying index table tweaks.

Database Schema & Sample Data #

dbo.Electrician

ElectricianId | Company | TelNo | Mobile | Addr1 | Postcode
123 | Sparky 1 | 01234567 | 0789078 | 42 lower ave | Ex2345
124 | Sparky 2 | 01235678 | 0777777 | 1 Street | Ta6547
125 | Sparky 3 | 05415644 | 0799078 | 4 Air Road | Gl4126

dbo.Painters

PainterId | Company | TelNo | Mobile | Addr1 | Postcode
333 | Painter 1 | 01234568 | 07232444 | 4 Higher ave | Ex2345
334 | Painter 2 | 01235679 | 07879879 | 5 Street | Ta6547
335 | Painter 3 | 05415645 | 07654654 | 5 Sky Road | Gl4126

dbo.Clients

ClientId | Name | TelNo | Mobile | Addr1 | Postcode
100333 | Mr Chester | 0154 5478 | 07878979 | 9 String Rd | PL41 1X
100334 | Mrs Garrix | 0254 6511 | 07126344 | 10 String Rd | PL41 1X
100335 | Ms Indy Pendant | 0208 1154 | 07665654 | 11 String Rd | PL41 1X

Key Bottlenecks in Your Current Implementation #

Your existing workflow has several performance killers:

• Cursor-based iteration: Cursors use RBAR (Row-By-Agonizing-Row) processing, which is drastically slower than set-based operations for bulk data.
• LIKE '%term%' with column functions: Using REPLACE on columns (e.g., REPLACE(tc.Telephone, ' ', '')) in WHERE clauses invalidates indexes, forcing full table scans.
• Redundant temp table operations: Inserting all single-term matches then deleting non-matching records creates unnecessary IO overhead.
• Unindexed temp tables: Without indexes, filtering and querying the temp table becomes slow as matching record counts grow.

Actionable Optimizations #

1. Replace Cursors with Set-Based Operations

Cursors are one of the biggest performance drains here. Instead, store cleaned search terms in a table variable and use set logic to process all terms at once:

DECLARE @SearchTerms TABLE (Term NVARCHAR(50) NOT NULL);
INSERT INTO @SearchTerms (Term)
SELECT DISTINCT value 
FROM general.Csvtoquery(@searchTerms) 
WHERE value != '';

This lets you use JOIN or EXISTS clauses to match all terms in a single pass, no loops required.

2. Use SQL Server Full-Text Search

This is the most impactful fix for free-text search performance. Full-text search is purpose-built for fuzzy, multi-term queries and is orders of magnitude faster than LIKE '%term%'.

Step 1: Enable Full-Text Search on your database

EXEC sp_fulltext_database 'enable';

Step 2: Create Full-Text Catalogs & Indexes

Create full-text indexes for each entity table, targeting columns you want to search:

-- Create a default full-text catalog if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.fulltext_catalogs WHERE name = 'FT_SearchCatalog')
CREATE FULLTEXT CATALOG FT_SearchCatalog AS DEFAULT;

-- Index for Clients table (adjust primary key index name to match yours)
CREATE FULLTEXT INDEX ON dbo.Clients (
    Name, 
    TelNo, 
    Mobile, 
    Addr1, 
    Postcode
) KEY INDEX PK_Clients_ClientId;

-- Repeat for Electrician and Painters tables
CREATE FULLTEXT INDEX ON dbo.Electrician (
    Company, 
    TelNo, 
    Mobile, 
    Addr1, 
    Postcode
) KEY INDEX PK_Electrician_ElectricianId;

CREATE FULLTEXT INDEX ON dbo.Painters (
    Company, 
    TelNo, 
    Mobile, 
    Addr1, 
    Postcode
) KEY INDEX PK_Painters_PainterId;

Step 3: Query with Full-Text Functions

Use CONTAINS to match exact terms, or FREETEXT for semantic matches. To ensure all search terms are present (AND logic):

INSERT INTO #tempsearchtable (EntityId, DisplayName, LongName, EntityType)
SELECT 
    c.ClientId,
    c.Name,
    c.Name + ', ' + COALESCE(c.Addr1, '') + ', ' + COALESCE(c.Postcode, '') + ', ' + COALESCE(c.TelNo, '') + ', ' + COALESCE(c.Mobile, ''),
    'Client'
FROM dbo.Clients c
WHERE EXISTS (
    SELECT 1 FROM @SearchTerms st 
    WHERE CONTAINS((c.Name, c.TelNo, c.Mobile, c.Addr1, c.Postcode), st.Term)
)
AND (SELECT COUNT(*) FROM @SearchTerms) = (
    SELECT COUNT(*) FROM @SearchTerms st 
    WHERE CONTAINS((c.Name, c.TelNo, c.Mobile, c.Addr1, c.Postcode), st.Term)
);

3. Pre-Process Formatted Fields

Stop using REPLACE on columns in WHERE clauses. Instead, add persisted computed columns for cleaned data and index them:

-- Add cleaned telephone column to Clients
ALTER TABLE dbo.Clients 
ADD CleanedTelNo AS REPLACE(TelNo, ' ', '') PERSISTED;
CREATE INDEX IX_Clients_CleanedTelNo ON dbo.Clients (CleanedTelNo);

-- Repeat for Mobile column
ALTER TABLE dbo.Clients 
ADD CleanedMobile AS REPLACE(Mobile, ' ', '') PERSISTED;
CREATE INDEX IX_Clients_CleanedMobile ON dbo.Clients (CleanedMobile);

Now you can query directly against these indexed columns without function overhead.

4. Optimize Temp Table Usage

• Add indexes upfront: Define indexes on your temp table when creating it to speed up filtering and queries:

  CREATE TABLE #tempsearchtable (
      EntityId INT NOT NULL,
      DisplayName NVARCHAR(255) NOT NULL,
      LongName NVARCHAR(1000) NOT NULL,
      EntityType NVARCHAR(50) NOT NULL,
      INDEX IX_TempSearch_EntityType_EntityId NONCLUSTERED (EntityType, EntityId)
  );
  

• Avoid insert-then-delete: Filter records during insertion to only include matches for all search terms, eliminating redundant cleanup steps.

5. Combine Multi-Entity Queries with UNION ALL

Merge searches across all three tables into a single UNION ALL query to reduce multiple temp table inserts:

INSERT INTO #tempsearchtable (EntityId, DisplayName, LongName, EntityType)
-- Electricians
SELECT 
    e.ElectricianId,
    e.Company,
    e.Company + ', ' + COALESCE(e.Addr1, '') + ', ' + COALESCE(e.Postcode, '') + ', ' + COALESCE(e.TelNo, '') + ', ' + COALESCE(e.Mobile, ''),
    'Electrician'
FROM dbo.Electrician e
WHERE EXISTS (
    SELECT 1 FROM @SearchTerms st WHERE CONTAINS((e.Company, e.TelNo, e.Mobile, e.Addr1, e.Postcode), st.Term)
)
AND (SELECT COUNT(*) FROM @SearchTerms) = (
    SELECT COUNT(*) FROM @SearchTerms st WHERE CONTAINS((e.Company, e.TelNo, e.Mobile, e.Addr1, e.Postcode), st.Term)
)

UNION ALL

-- Painters
SELECT 
    p.PainterId,
    p.Company,
    p.Company + ', ' + COALESCE(p.Addr1, '') + ', ' + COALESCE(p.Postcode, '') + ', ' + COALESCE(p.TelNo, '') + ', ' + COALESCE(p.Mobile, ''),
    'Painter'
FROM dbo.Painters p
WHERE EXISTS (
    SELECT 1 FROM @SearchTerms st WHERE CONTAINS((p.Company, p.TelNo, p.Mobile, p.Addr1, p.Postcode), st.Term)
)
AND (SELECT COUNT(*) FROM @SearchTerms) = (
    SELECT COUNT(*) FROM @SearchTerms st WHERE CONTAINS((p.Company, p.TelNo, p.Mobile, p.Addr1, p.Postcode), st.Term)
)

UNION ALL

-- Clients
SELECT 
    c.ClientId,
    c.Name,
    c.Name + ', ' + COALESCE(c.Addr1, '') + ', ' + COALESCE(c.Postcode, '') + ', ' + COALESCE(c.TelNo, '') + ', ' + COALESCE(c.Mobile, ''),
    'Client'
FROM dbo.Clients c
WHERE EXISTS (
    SELECT 1 FROM @SearchTerms st WHERE CONTAINS((c.Name, c.TelNo, c.Mobile, c.Addr1, c.Postcode), st.Term)
)
AND (SELECT COUNT(*) FROM @SearchTerms) = (
    SELECT COUNT(*) FROM @SearchTerms st WHERE CONTAINS((c.Name, c.TelNo, c.Mobile, c.Addr1, c.Postcode), st.Term)
);

6. Try Memory-Optimized Temp Tables (SQL Server 2016+)

If you're on a supported SQL Server version, use memory-optimized temp tables to drastically reduce IO overhead:

CREATE TABLE #tempsearchtable (
    EntityId INT NOT NULL,
    DisplayName NVARCHAR(255) NOT NULL,
    LongName NVARCHAR(1000) NOT NULL,
    EntityType NVARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

Final Recommendations #

Start with full-text search—it will give you the biggest performance jump immediately. Then replace cursors with set-based operations and clean up your column function usage. These changes should get you to real-time search speeds.

内容的提问来源于stack exchange，提问作者Harambe