[FIXED] So erstellen Sie eine benutzerdefinierte Funktion für SQL Server

Ausgabe

Ich habe eine ziemlich große gespeicherte Prozedur, die ich versuche, für die Lesbarkeit zu vereinfachen.

Es enthält viele Vereinigungsklauseln mit Aussagen wie der unten gezeigten:

Abb.1

SELECT COUNT(1) AS Total
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))
    AND (EXISTS (
      SELECT 1 AS Total
        FROM OrderShipments 
        WHERE (Orders.Id = Order_Id)
          AND (CarrierApproved = 0)))
    AND (IsQuote = 0)
    AND (Cancelled = 0)
    AND (Archived = 0)
    AND (Completed = 0)
    AND (Holding = 0)

Es gibt jedoch viele wiederkehrende Muster in jeder Aussage.

Das folgende Muster taucht mehrmals auf Abb.2

Abb.2

  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))

Ich versuche (aus Gründen der Lesbarkeit) den Code in der gespeicherten Masterprozedur zu reduzieren

Also dachte ich, ich würde einen UDF-Leistungstest durchführen, ich habe mir die folgende Abb. 3 ausgedacht

Abb. 3

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION TestFunction (@OrderId int)
RETURNS TABLE
AS
  RETURN
  (
  -- Add the SELECT statement with parameter references here
  SELECT 1 AS Total
    FROM (
      SELECT OrderLineItems.Id AS Id
        FROM OrderLineItems
        WHERE @OrderId = Order_Id) AS Sub
    WHERE EXISTS (
        SELECT 1 AS Total
          FROM NormalizedLineItems
          WHERE (Sub.Id = OrderLineItem_Id)
            AND (OutOfStock = 1)))
GO

Obwohl das obige kompiliert, bin ich mir nicht sicher, ob ich auf dem richtigen Weg bin, ich habe alle möglichen Probleme beim Versuch, das obige UDF auf die ursprüngliche Abfrage anzuwenden.

Ich suche ein konkretes Beispiel dafür, wie Abb. 2 von Abb. 1 in eine UDF abstrahiert werden kann, damit ich die Lösung zumindest auf Leistung testen kann, um zu sehen, ob es sich lohnt.

Hinweis: Ich weiß, dass benutzerdefinierte Funktionen ein Performance-Albtraum sein können, aber ich bin noch nicht einmal in einem Stadium, in dem ich sie testen kann.

Lösung

Erstellen Sie eine order_ids-Tabelle mit nur einer Spalte namens order_id

Insert into order_ids
select order_id from 
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))

Dann können Sie Ihr SQL wie folgt vereinfachen:

SELECT COUNT(1) AS Total
  FROM Orders 
  join order_ids
  on order_ids.order_id = Orders.order_id
 ...

Wenn Ihre wiederkehrende Anweisung nur in einer Abfrage enthalten ist, ist
Common Table Expression die beste Wahl:

with CTE_order_ids as
(select order_id from 
  FROM Orders 
  WHERE (NOT EXISTS (
      SELECT 1 
        FROM (
          SELECT Id 
            FROM OrderLineItems 
            WHERE Orders.Id = Order_Id) AS Sub
        WHERE EXISTS (
            SELECT 1 
              FROM NormalizedLineItems 
              WHERE (Sub.Id = OrderLineItem_Id)
                AND (OutOfStock = 1))))
)
SELECT COUNT(1) AS Total
  FROM Orders 
  join CTE_order_ids 
  on order_ids.order_id = Orders.order_id
 ...

Beantwortet von – Jaugar Chang

Antwort geprüft von – Senaida (FixError Volunteer)