Блог

У меня есть хранимая процедура, которая принимает строку и сравнивает ее с несколькими полями. Однако одно из полей содержит дополнительные символы, которые необходимо удалить, прежде чем сравнивать его со строкой поиска.

например

 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
 

Примерные значения полей:

 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>
 

При поиске в поле body <p>, <b> and <i> теги удаляются с помощью функции udfStripHtmlTag . Это приводит к тому, что поле Body в предложении where больше не является SARG аргументом поиска » или», что замедляет выполнение запроса.

В этом примере я удаляю HTML-теги из поля body, используя функцию с именем ‘udfStripHtmlTag’, прежде чем сравнивать ее с критериями. Как и ожидалось, это приводит к тому, что вместо поиска по индексу выполняется сканирование индекса с индексом, созданным для поля body . Фактически, это также приводит к тому, что индексы для оценки и commentcount полей больше не используются в этой версии запроса. В другой версии, где я использую объединение вместо «или», индексы для оценки и commentcount полей по-прежнему используются. Однако запрос по-прежнему выполняется так же медленно из-за сканирования индекса для основного поля.

Удаление символов из поля является обязательным требованием. Есть ли альтернативный метод, который я могу использовать вместо скалярной функции в предложении where?

примечание. Это не мой исходный код проблемы. Скорее это код, основанный на базе данных StackOverflow. Мой исходный код проблемы основан на большом количестве конфиденциальных данных / полей, поэтому мне было проще использовать альтернативную базу данных. Аналогично, поле body в базе данных StackOverflow было изменено на nvarchar(30) , чтобы я мог создать для него индекс. Наконец, запрос выполняется менее чем за секунду без скалярной функции.

Не уверен, что это более эффективно, чем ваша скалярная функция

Устав извлекать строку, я модифицировал функцию синтаксического анализа, чтобы принимать два разных разделителя.

Пример

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

ВОЗВРАТ

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

Табличная функция, если она заинтересована

 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,'[[',']]')
*/