問題描述

我有一個數據庫，在該數據庫中我可以在一次自由文本搜索中返回有關多個實體的信息，這是一個示例數據庫:

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:

創建臨時表(EntityId、DisplayName、LongName、EntityType)

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

在用逗號替換空格并將其用作 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 != ''

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

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 + '%'

我現在再次循環搜索.這是為了確保我只得到特定的匹配.我刪除了 LongName 不包含我的搜索詞的任何內容.

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.

在刪除臨時表之前，我從臨時表中選擇了所有內容.

I select all from the temp table before dropping it.

雖然這確實有效，而且效果很好，但搜索速度比我想要的要慢，我一直在尋找加快速度的建議.其中之一是創建一個索引表并將所有實體轉儲到其中，并且只有 1 個循環獲取特定搜索.這稍微快一點，但這也意味著我只有最后一個任務設置為將數據轉儲到索引時的數據.實時搜索勢在必行.

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.

感謝您的任何建議.

推薦答案

我不確定這是否會更快，但是您是否嘗試過創建一個字符串并在該字符串上使用 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 是否執行了惰性表達式搜索，可以使這種方法比使用大量 OR 語句更快.'|'管道標志是為了防止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.

這篇關于搜索查詢 - 搜索多個表和列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！