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)