Partager via

Implémentation de l’opérateur OR dans des procédures stockées compilées en mode natif

Les opérateurs OR ne sont pas pris en charge dans les prédicats de requête des procédures stockées compilées de manière native. Étant donné que les opérateurs NOT ne sont pas non plus pris en charge dans les prédicats de requête dans les procédures stockées compilées en mode natif, les effets des opérateurs OR ne peuvent pas être simulés par le biais de l’utilisation d’opérateurs logiques équivalents uniquement. Toutefois, les effets d’un opérateur OR peuvent être simulés avec des variables de table optimisées en mémoire.

OR, opérateur dans la clause WHERE

Si vous avez un opérateur OR dans une clause WHERE, vous pouvez utiliser l’approche suivante pour simuler son comportement :

  1. Créez une variable de table optimisée en mémoire avec le schéma approprié. Cela nécessite un type de table prédéfini à mémoire optimisée.

  2. En commençant par l'opérateur OR de niveau supérieur, séparez la clause WHERE en deux parties selon les prédicats reliés par l'opérateur OR. Si vous avez plusieurs opérateurs OR dans une clause WHERE, vous devrez peut-être effectuer cette opération plusieurs fois. Répétez cette étape jusqu’à ce qu’aucun opérateur OR ne reste. Par exemple, si vous avez le prédicat suivant :

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

    Après cette étape, vous devez disposer des prédicats suivants :

    pred1  
pred5 AND pred6  
pred2 AND pred3  
pred2 AND pred4

  3. Exécutez une requête avec chacune des deux parties trouvées à l’étape 2 comme prédicat. Insérez le résultat de chaque requête dans la variable de table optimisée en mémoire créée à l’étape 1.

  4. Si nécessaire, supprimez les doublons de la variable de table optimisée en mémoire.

  5. Utilisez le contenu de la variable de table optimisée en mémoire comme résultat de la requête.

L’exemple suivant utilise des tables de la base de données AdventureWorks2012 qui ont été mises à jour pour In-Memory OLTP. Pour télécharger les fichiers de cet exemple, goto AdventureWorks Databases - 2012, 2008R2 et 2008. Pour appliquer In-Memory exemple de code OLTP à AdventureWorks2012, accédez à SQL Server 2014 In-Memory exemple OLTP.

Ajoutez la procédure stockée suivante à la base de données. Nous allons convertir cette procédure stockée pour utiliser la compilation native.

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

Après la conversion, le schéma de table et de procédure stockée est le suivant :

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

Opérateur OR dans la condition JOIN

Si vous avez un opérateur OR dans une condition JOIN d’une instruction SELECT, vous pouvez utiliser l’approche suivante pour simuler son comportement. Si vous avez plusieurs opérateurs OR dans une condition JOIN ou si vous avez plusieurs conditions JOIN avec des opérateurs OR, vous devrez peut-être effectuer cette opération plusieurs fois.

Si vous avez des conditions OUTER JOIN, vous pouvez combiner cette méthode de contournement avec celle applicable aux conditions OUTER JOIN.

  1. Créez une variable de table optimisée en mémoire avec le schéma approprié. Cela nécessite un type de table prédéfini à mémoire optimisée.

  2. Séparez le prédicat dans la condition JOIN en deux parties en fonction des prédicats joints par l’opérateur OR. Si vous avez plusieurs conditions JOIN, vous devrez peut-être effectuer cette opération pour chaque condition JOIN, puis créer un ensemble de combinaisons des fragments résultants. Par exemple, si vous avez trois conditions JOIN avec un opérateur OR dans chaque condition JOIN, vous pouvez avoir 2x2x2=8 prédicats.

  3. Pour chaque prédicat produit à l’étape 2, créez une requête qui insère son résultat dans la variable de table optimisée en mémoire créée à l’étape 1.

  4. Si nécessaire, supprimez les doublons de la variable de table optimisée en mémoire.

  5. Utilisez le contenu de la variable de table optimisée en mémoire comme résultat de la requête.

L’exemple suivant utilise des tables de la base de données AdventureWorks2012 qui ont été mises à jour pour In-Memory OLTP. Pour télécharger les fichiers de cet exemple, goto AdventureWorks Databases - 2012, 2008R2 et 2008. Pour appliquer In-Memory exemple de code OLTP à AdventureWorks2012, accédez à SQL Server 2014 In-Memory exemple OLTP.

Ajoutez la procédure stockée suivante à la base de données. Nous allons convertir cette procédure stockée pour utiliser la compilation native. Cet exemple utilise des conditions de jointure interne (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

Après la conversion, le schéma de table et de procédure stockée est le suivant :

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

Effets secondaires

Si vous avez plusieurs opérateurs OR dans la clause WHERE ou la condition JOIN, le nombre de requêtes que vous devez exécuter pour simuler le comportement peut augmenter exponentiellement. Cela peut ralentir les performances des requêtes et augmenter l’utilisation de la mémoire en raison de la nécessité d’utiliser des variables de table optimisées en mémoire.

Voir aussi

Problèmes de migration pour les procédures stockées compilées en mode natif

  • Last updated on