問題描述

我有一個(gè)數(shù)據(jù)庫，在該數(shù)據(jù)庫中我可以在一次自由文本搜索中返回有關(guān)多個(gè)實(shí)體的信息，這是一個(gè)示例數(shù)據(jù)庫:

I have a database in which I return information about several entities in a single free text search, here is an example database:

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

我目前的方法是這樣工作的:

My current method is working as such:

創(chuàng)建臨時(shí)表(EntityId、DisplayName、LongName、EntityType)

Create Temp Table (EntityId, DisplayName, LongName, EntityType)

在用逗號(hào)替換空格并將其用作 CSV 之前，獲取搜索詞并替換不需要的字符.

Take search terms and replace unwanted characters before replacing spaces with commas and using this as a CSV.

SET @searchTerms = LTRIM(RTRIM(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(
        REPLACE(LTRIM(RTRIM(@searchTerms)), ',', ' '),
        '[', ''),
        ']', ''),
        '#', ''),
        '&', ''),
        ';', ''),
        '?', ''),
        '`', ''),
        '''', ''),
        '*', ''),
        '"', ''),
        '<', ' '),
        '>', ' '),
        '-', ' '),
        '(', ' '),
        ')', ' '),
        '\', ' '),
        '/', ' ')))

        SET @searchTerms = REPLACE(@searchTerms, ' ', ',')

        DECLARE @SearchTerm AS nvarchar(50);

        DECLARE @DevelopmentCursor AS CURSOR;
        SET @DevelopmentCursor = CURSOR
        FOR
        SELECT
          *
        FROM general.Csvtoquery(@searchTerms)
        WHERE value != ''

接下來，我遍歷搜索詞，將每個(gè)實(shí)體插入到我的臨時(shí)表中:

Next I loop over my search terms inserting each entity into my Temp table:

            INSERT INTO #tempsearchtable (EntityId, Name, LongName, EntityType)
            SELECT
                tc.ClientId,
                tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName,
                tc.Title + ' ' + tc.FirstName + ' ' + tc.LastName + ', ' + COALESCE(a.NameOrNumber, '') + ', ' + COALESCE(a.Street, '') + ', ' + COALESCE(a.Town, '') + ', ' + + ', ' + COALESCE(a.County, '') + ', ' + COALESCE(a.Postcode, '') + ', ' + COALESCE(a.Country, '')  + ', ' + COALESCE(tc.EmailAddress, '')  + ', ' + COALESCE(REPLACE(tc.Telephone, ' ', ''), '')  + ', ' + COALESCE(REPLACE(tc.Mobile, ' ', ''), ''),
                'Client'
            FROM 
                dbo.Clients tc
            LEFT JOIN 
                dbo.[Address] a ON tc.AddressId = a.AddressId
            WHERE 
                tc.FirstName LIKE '%' + @SearchTerm + '%'
                OR tc.LastName LIKE '%' + @SearchTerm + '%'
                OR tc.EmailAddress = @SearchTerm
                OR REPLACE(tc.Telephone, ' ', '') LIKE '%' + @SearchTerm + '%'
                OR REPLACE(tc.Mobile, ' ', '') LIKE '%' + @SearchTerm + '%'
                OR a.NameOrNumber LIKE '%' + @SearchTerm + '%'
                OR a.Street LIKE '%' + @SearchTerm + '%'
                OR a.Postcode LIKE '%' + @SearchTerm + '%'
                OR a.County LIKE '%' + @SearchTerm + '%'
                OR a.Town LIKE '%' + @SearchTerm + '%'
                OR a.Country LIKE '%' + @SearchTerm + '%'

我現(xiàn)在再次循環(huán)搜索.這是為了確保我只得到特定的匹配.我刪除了 LongName 不包含我的搜索詞的任何內(nèi)容.

I now loop my searches again. This is to ensure I am only getting specific matches. I delete anything where the LongName doesn't contain my search term.

在刪除臨時(shí)表之前，我從臨時(shí)表中選擇了所有內(nèi)容.

I select all from the temp table before dropping it.

雖然這確實(shí)有效，而且效果很好，但搜索速度比我想要的要慢，我一直在尋找加快速度的建議.其中之一是創(chuàng)建一個(gè)索引表并將所有實(shí)體轉(zhuǎn)儲(chǔ)到其中，并且只有 1 個(gè)循環(huán)獲取特定搜索.這稍微快一點(diǎn)，但這也意味著我只有最后一個(gè)任務(wù)設(shè)置為將數(shù)據(jù)轉(zhuǎn)儲(chǔ)到索引時(shí)的數(shù)據(jù).實(shí)時(shí)搜索勢(shì)在必行.

While this does work, and works pretty well, the search is slower than I'd like and I was looking for suggestions to speed this up. One of which was to create an index table and dump all the entities into this, and just have 1 loop getting the specific searches. This is slightly faster but it also means I only have data for when the last task was set to dump the data into the index. Live searches are imperative.

感謝您的任何建議.

推薦答案

我不確定這是否會(huì)更快，但是您是否嘗試過創(chuàng)建一個(gè)字符串并在該字符串上使用 LIKE?

I'm not sure if this would be any faster, but have you tried creating one string and using LIKE on that one string?

類似于:

SELECT
  ...
FROM 
  dbo.Clients tc
  LEFT JOIN 
    dbo.[Address] a ON tc.AddressId = a.AddressId
WHERE 
  REPLACE( tc.FirstName + '|' + tc.LastName + '|' + tc.EmailAddress + tc.Telephone + '|' + ....., ' ', '' ) LIKE '%' + @SearchTerm + '%'

考慮到 SQL 在解析方面并不是那么好，我想知道 LIKE 是否執(zhí)行了惰性表達(dá)式搜索，可以使這種方法比使用大量 OR 語句更快.'|'管道標(biāo)志是為了防止jared"等搜索詞匹配Jar Jar"、Edwards"等的名字 + 姓氏.

Considering that SQL is not that great with parsing, I wonder if the LIKE performs a lazy expression search that could make this approach faster than using a barrage of OR statements. The '|' Pipe signs are to prevent search terms like "jared" from matching a FirstName + LastName of "Jar Jar" "Edwards", etc.

這篇關(guān)于搜索查詢 - 搜索多個(gè)表和列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！