Mam procedurę przechowywaną, która akceptuje ciąg i porównuje go do kilku pól. Jednak jeden z pól zawiera dodatkowe znaki, które należy usunąć przed porównaniem go do ciągu wyszukiwania.

Na przykład

Create Or Alter Proc FindPosts3
(
     @criteria AS nvarchar(25)
)
AS
Begin

     Select P.OwnerUserId,P.CreationDate,p.Score,p.CommentCount,p.Body1
     From Posts1 p Inner Join
     (
         Select p.OwnerUserId,max(p.CreationDate) as CreationDate
         From Posts1 p
         Group by p.OwnerUserId

     ) Sub On P.OwnerUserId = Sub.OwnerUserId AND P.CreationDate = Sub.CreationDate
     Where p.Score = @criteria OR p.CommentCount = @criteria Or udfstripHtmlTags(p.body1) = @criteria   

End

Przykładowe wartości pola to:

OwernerUserID---CreationDate----Score----CommentCount----Body
1               Aug 20, 2010     18      6               <p>null<p>
2               Dec 15, 2008      7      3               <b>variable<b>
3               Mar 07, 2011     15      20              <i>Arrays<i>

Podczas wyszukiwania pola ciała, znaczniki <p>, <b> and <i> są usuwane za pomocą funkcji Udfstriphtmltag. Powoduje to, że pole ciała w klauzuli gdzie nie jest już argument "SARG" lub wyszukiwania, który spowalnia zapytanie.

W tym przykładzie usuwam znaczniki HTML z pola ciała za pomocą funkcji o nazwie "Udfstriphtmltag" przed porównaniem go do kryteriów. Jak się spodziewa, że powoduje, że skanowanie indeksu występuje zamiast indeksu poszukiwania indeksu, który został utworzony dla pola ciała. W rzeczywistości powoduje to również indeksy dla pól i pola {X0}}, aby nie być używane w tej wersji zapytania. W innej wersji, w której używam związku zamiast "lub", indeksy dla pól i pola {x1}} są nadal używane. Jednak zapytanie jest nadal tak powolne ze względu na skanowanie indeksu dla pola ciała.

Rozebranie znaków z pola to wymóg. Czy istnieje alternatywna technika, której mogę użyć zamiast funkcji skalarnej w klauzuli Gdzie?

NB. To nie jest mój oryginalny kod problemu. Jest raczej kodem na bazie bazy danych stackoverflow. Mój oryginalny kod problemowy opiera się na wielu wrażliwych danych / polach, więc łatwiej było mi korzystać z alternatywnej bazy danych. Podobnie, pole ciała w bazy danych stackoverflow zmieniono na nvarchar(30), więc mogłem utworzyć indeks. Wreszcie zapytanie działa w mniej niż sekundę bez funkcji skalarnej.

1
abujafar 21 listopad 2020, 05:47

1 odpowiedź

Najlepsza odpowiedź

Nie jestem pewien, czy jest to bardziej występujący niż funkcja skalarna

Masz dość wyodrębniania łańcucha, zmodyfikowałem funkcję analizy, aby zaakceptować dwa w przeciwieństwie do ograniczników.

Przykład

Declare @YourTable Table ([OwernerUserID] int,[CreationDate] varchar(50),[Score] int,[CommentCount] int,[Body] varchar(150))  
Insert Into @YourTable Values 
 (1,'Aug 20, 2010',18,6,'<p>null</p>')
,(2,'Dec 15, 2008',7,3,'<b>variable</b>')
,(3,'Mar 07, 2011',15,20,'<i>Arrays</i>')
 
Select A.* 
 From @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract](Body,'>','</') B
 Where B.RetVal like 'variable%'

Zwroty

OwernerUserID   CreationDate    Score   CommentCount    Body
2               Dec 15, 2008    7       3               <b>variable</b>

Funkcja ceniona tabelą, jeśli jesteś zainteresowany

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
            Cross Apply XMLData.nodes('x') AS B(i)
          ) C1
    Where charindex(@Delim2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/
2
John Cappelletti 21 listopad 2020, 03:55