Compartilhar via

Implementando o operador OR em procedimentos armazenados compilados nativamente

Não há suporte para operadores OR em predicados de consulta em procedimentos armazenados compilados nativamente. Como os operadores NOT também não têm suporte em predicados de consulta em procedimentos armazenados compilados nativamente, os efeitos dos operadores OR não podem ser simulados apenas com o uso de operadores lógicos equivalentes. No entanto, os efeitos de um operador OR podem ser simulados com variáveis de tabela com otimização de memória.

Operador OR na cláusula WHERE

Se você tiver um operador OR em uma cláusula WHERE, poderá usar a seguinte abordagem para simular seu comportamento:

  1. Crie uma variável de tabela com otimização de memória com o esquema apropriado. Isso requer um tipo de tabela predefinido com otimização de memória.

  2. Começando com o operador OR de nível superior, separe a cláusula WHERE em duas partes de acordo com os predicados unidos pelo operador OR. Se você tiver mais de um operador OR em uma cláusula WHERE, talvez seja necessário fazer isso mais de uma vez. Repita esta etapa até que nenhum operador OR permaneça. Por exemplo, se você tiver o seguinte predicado:

    pred1 OR (pred2 AND (pred3 OR pred4)) OR (pred5 AND pred6)

    Após esta etapa, você deverá ter os seguintes predicados:

    pred1  
pred5 AND pred6  
pred2 AND pred3  
pred2 AND pred4

  3. Execute uma consulta com cada uma das duas partes encontradas na Etapa 2 como o predicado. Insira o resultado de cada consulta na variável de tabela com otimização de memória criada na Etapa 1.

  4. Se necessário, remova duplicatas da variável de tabela com otimização de memória.

  5. Use o conteúdo da variável de tabela com otimização de memória como resultado da consulta.

O exemplo a seguir usa tabelas do banco de dados AdventureWorks2012 que foram atualizadas para In-Memory OLTP. Para baixar os arquivos deste exemplo, goto AdventureWorks Databases – 2012, 2008R2 e 2008. Para aplicar In-Memory exemplo de código OLTP ao AdventureWorks2012, acesse o SQL Server 2014 In-Memory Exemplo de OLTP.

Adicione o procedimento armazenado a seguir ao banco de dados. Converteremos esse procedimento armazenado para usar a compilação nativa.

CREATE PROCEDURE Sales.usp_fuzzySearchSalesOrderDetail_ondisk  
  @SalesOrderId int = 0, @SalesOrderDetailId int = 0,   
  @CarrierTrackingNumber nvarchar(25) = N'', @ProductId int = 0,   
  @minUnitPrice money = 0, @maxUnitPrice money = 0  
AS BEGIN  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_ondisk s  
  WHERE  s.SalesOrderId = @SalesOrderId  
      OR s.SalesOrderDetailId = @SalesOrderDetailId  
      OR s.CarrierTrackingNumber = @CarrierTrackingNumber  
      OR s.ProductID = @ProductId  
      OR (s.UnitPrice > @minUnitPrice AND s.UnitPrice < @maxUnitPrice)  
END  
GO

Após a conversão, a tabela e o esquema de procedimento armazenado são os seguintes:

CREATE TYPE Sales.fuzzySearchSalesOrderDetailType AS TABLE  
(  
  SalesOrderId int not null,  
  SalesOrderDetailId int not null,  
  ModifiedDate datetime2(7) not null  
  INDEX ix_fuzzySearchSalesOrderDetailType NONCLUSTERED (SalesOrderId, SalesOrderDetailId)  
) WITH (MEMORY_OPTIMIZED = ON)  
GO  
  
CREATE TYPE Sales.fuzzySearchSalesOrderDetailTempType AS TABLE  
(  
  SalesOrderId int not null,  
  SalesOrderDetailId int not null,  
  recordcount int not null  
  INDEX ix_fuzzySearchSalesOrderDetailTempType NONCLUSTERED (SalesOrderId, SalesOrderDetailId)  
) WITH (MEMORY_OPTIMIZED = ON)  
GO  
  
CREATE PROCEDURE Sales.usp_fuzzySearchSalesOrderDetail_inmem  
  @SalesOrderId int = 0, @SalesOrderDetailId int = 0,   
  @CarrierTrackingNumber nvarchar(25) = N'', @ProductId int = 0,   
  @minUnitPrice money = 0, @maxUnitPrice money = 0  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'ENGLISH')  
  
  DECLARE @retValue Sales.fuzzySearchSalesOrderDetailType  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, ModifiedDate)  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  WHERE s.SalesOrderId = @SalesOrderId  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, ModifiedDate)  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  WHERE s.SalesOrderDetailId = @SalesOrderDetailId  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, ModifiedDate)  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  WHERE s.CarrierTrackingNumber COLLATE Latin1_General_BIN2 = @CarrierTrackingNumber COLLATE Latin1_General_BIN2   
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, ModifiedDate)  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  WHERE s.ProductID = @ProductId  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, ModifiedDate)  
  SELECT SalesOrderId, SalesOrderDetailId, ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  WHERE (s.UnitPrice > @minUnitPrice AND s.UnitPrice < @maxUnitPrice)  
  
  -- After the above statements, there will be duplicates inside @retValue  
  -- Delete the duplicates from @retValue  
  DECLARE @duplicates Sales.fuzzySearchSalesOrderDetailTempType  
  
  INSERT INTO @duplicates (SalesOrderId, SalesOrderDetailId, recordcount)   
  SELECT SalesOrderId, SalesOrderDetailId, COUNT(*) AS recordCount  
  FROM @retValue  
  GROUP BY SalesOrderId, SalesOrderDetailId  
  
  -- Now we have one row per pair  
  -- clear and rebuild the result set  
  DELETE FROM @retValue  
  
  INSERT INTO @retValue  
  SELECT s.SalesOrderId, s.SalesOrderDetailId, s.ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  JOIN @duplicates d ON s.SalesOrderId = d.SalesOrderId AND s.SalesOrderDetailId = d.SalesOrderDetailId  
  
  -- After this every pair of (SalesOrderId, SalesOrderDetailId) in @retValue should be unique.  
  SELECT SalesorderId, SalesOrderDetailId, ModifiedDate FROM @retValue  
END  
GO

Operador OR na condição JOIN

Se você tiver um operador OR em uma condição JOIN de uma instrução SELECT, poderá usar a abordagem a seguir para simular seu comportamento. Se você tiver mais de um operador OR em uma condição JOIN ou tiver várias condições JOIN com operadores OR, talvez seja necessário fazer isso mais de uma vez.

Se você tiver condições OUTER JOIN, poderá combinar essa solução alternativa com a solução alternativa para condições OUTER JOIN.

  1. Crie uma variável de tabela com otimização de memória com o esquema apropriado. Isso requer um tipo de tabela predefinido com otimização de memória.

  2. Separe o predicado na condição JOIN em duas partes, conforme os predicados unidos pelo operador OR. Se você tiver várias condições de JUNÇÃO, talvez seja necessário fazer isso para cada condição de JUNÇÃO e depois criar um conjunto de combinações dos fragmentos resultantes. Por exemplo, se você tiver três condições JOIN com um operador OR em cada condição JOIN, poderá ter predicados 2x2x2=8.

  3. Para cada predicado produzido pela Etapa 2, crie uma consulta que inserirá seu resultado na variável de tabela com otimização de memória criada na Etapa 1.

  4. Se necessário, remova duplicatas da variável de tabela com otimização de memória.

  5. Use o conteúdo da variável de tabela com otimização de memória como resultado da consulta.

O exemplo a seguir usa tabelas do banco de dados AdventureWorks2012 que foram atualizadas para In-Memory OLTP. Para baixar os arquivos deste exemplo, goto AdventureWorks Databases – 2012, 2008R2 e 2008. Para aplicar In-Memory exemplo de código OLTP ao AdventureWorks2012, acesse o SQL Server 2014 In-Memory Exemplo de OLTP.

Adicione o procedimento armazenado a seguir ao banco de dados. Converteremos esse procedimento armazenado para usar a compilação nativa. Este exemplo usa cláusulas de INNER JOIN.

CREATE PROCEDURE Sales.usp_fuzzySearchSalesSpecialOffers_ondisk  
  @SpecialOfferId int  
AS BEGIN  
  
  SELECT s.SalesOrderId, s.SalesOrderDetailId, s.SpecialOfferID, s.ModifiedDate  
  FROM Sales.SalesOrderDetail_ondisk s  
  JOIN Sales.SpecialOffer_onDisk offer   
    ON s.SpecialOfferID = offer.SpecialOfferID   
    OR s.ProductID IN (SELECT ProductId FROM Sales.SpecialOfferProduct sop WHERE sop.SpecialOfferID = @SpecialOfferId)  
END

Após a conversão, a tabela e o esquema de procedimento armazenado são os seguintes:

CREATE TYPE Sales.fuzzySearchSalesSpecialOffers_Type AS TABLE  
(  
  SalesOrderId int not null,  
  SalesOrderDetailId int not null,  
  SpecialOfferId int not null,  
  ModifiedDate datetime2(7) not null  
  INDEX ix_fuzzySearchSalesSpecialOffers_Type NONCLUSTERED (SalesOrderId, SalesOrderDetailId)  
) WITH (MEMORY_OPTIMIZED = ON)  
GO  
  
CREATE TYPE Sales.fuzzySearchSalesSpecialOffers_TempType AS TABLE  
(  
  SalesOrderId int not null,  
  SalesOrderDetailId int not null,  
  SpecialOfferId int not null,  
  recordcount int null  
  INDEX ix_fuzzySearchSalesSpecialOffers_TempType NONCLUSTERED (SalesOrderId, SalesOrderDetailId)  
) WITH (MEMORY_OPTIMIZED = ON)  
GO  
  
CREATE PROCEDURE Sales.usp_fuzzySearchSalesSpecialOffers_inmem  
  @SpecialOfferId int  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'ENGLISH')  
  
  DECLARE @retValue Sales.FuzzySearchSalesSpecialOffers_Type  
  
  -- Find all special offers matching the conditions  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, SpecialOfferid, ModifiedDate)  
  SELECT s.SalesOrderId, s.SalesOrderDetailId, s.SpecialOfferID, s.ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  JOIN Sales.SpecialOffer_inmem offer   
    ON s.SpecialOfferID = offer.SpecialOfferID  
  
  INSERT INTO @retValue (SalesOrderId, SalesOrderDetailId, SpecialOfferid, ModifiedDate)  
  SELECT s.SalesOrderId, s.SalesOrderDetailId, s.SpecialOfferID, s.ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  JOIN Sales.SpecialOfferProduct_inmem sop   
    ON sop.SpecialOfferId = @SpecialOfferId AND s.ProductID = sop.ProductId  
  
  -- Now we need to remove the duplicates from @matchingSpecialOffers  
  DECLARE @duplicates Sales.fuzzySearchSalesSpecialOffers_TempType  
  
  INSERT INTO @duplicates (SalesOrderId, SalesOrderDetailId, SpecialOfferid, recordcount)  
  SELECT SalesOrderId, SalesOrderDetailId, SpecialOfferId, COUNT(*)   
  FROM @retValue  
  GROUP BY SalesOrderId, SalesOrderDetailId, SpecialOfferId  
  
  -- now there should be no duplicates within @duplicate  
  -- use @duplicate for join.  
  SELECT s.SalesOrderId, s.SalesOrderDetailId, s.SpecialOfferID, s.ModifiedDate  
  FROM Sales.SalesOrderDetail_inmem s  
  JOIN @duplicates offer   
    ON    s.SalesOrderId = offer.SalesOrderId   
      AND s.SalesOrderDetailId = offer.SalesOrderDetailID   
      AND s.SpecialOfferId = offer.SpecialOfferId  
END  
GO

Efeitos colaterais

Se você tiver mais de um operador OR na cláusula WHERE ou na condição JOIN, o número de consultas que você precisa executar para simular o comportamento poderá aumentar exponencialmente. Isso pode diminuir o desempenho da consulta e pode aumentar o uso de memória devido à necessidade de usar variáveis de tabela com otimização de memória.

Consulte Também

Problemas de migração para procedimentos armazenados compilados nativamente

  • Last updated on