[sql] records selecteren waar waarde niet bij voorkomt

[PdeBie]

Ik heb even assistentie nodig met een bepaalde query.

Ik heb twee tabellen 'orders' en 'ordertrack'. Per order record bestaan er meerdere ordertrack records.
Een order heeft (vanzelfsprekend) een ID en de bijbehorende ordertracks zijn hieraan gekoppeld met dit ID.
De ordertracks hebben een kolom 'status' om de diverse statussen die een order doorloopt te kunnen volgen.

even de create scripts. Kolommen die niet aan de orde zijn, zijn weg gelaten.

SQL:

CREATE TABLE [dbo].[OrderHead](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Sender] [int] NOT NULL,
    [Receiver] [int] NOT NULL,
    [OrderID] [int] NOT NULL,
    [DeliveryDate] [numeric](8, 0) NOT NULL
 CONSTRAINT [PK_OrderHead] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[OrderTrack](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderId] [int] NOT NULL,
    [OrderStatus] [int] NOT NULL,
    [DateChanged] [datetime] NOT NULL,
    [ChangedBy] [varchar](25) NOT NULL,
    [IPAddress] [varchar](15) NOT NULL,
    [Sender] [int] NOT NULL,
    [Receiver] [int] NOT NULL
 CONSTRAINT [PK_OrderTrack] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Een order kan diverse statussen hebben. Van 1 (nieuwe order) t/m 20 (afgehandeld). Daarnaast zijn er enkele ordertrack orderstatus codes (bijvoorbeeld het getal 100 = order geprint) die we bijhouden om te monitoren wat de gebruiker met de order gedaan heeft.

Nu wil ik van een bepaalde receiver alle orders (na een bepaalde bezorgdatum) terug krijgen die niet geprint zijn. Dus alle orders waarbij er in de orderstatus tabel geen regel voorkomt met orderstatus 100.

Daar had ik de volgende query voor bedacht.

SQL:

DECLARE
    @receiver INT = 340,
    @deliverydate INT = 20121201,
    @status INT = 100

SELECT oh.id AS [ID], oh.orderid AS [Ordernummer], oh.sender AS [Afzender], oh.receiver AS [Ontvanger], oh.deliverydate AS [Bezorgdatum]
FROM orderhead oh
WHERE oh.receiver = @receiver AND oh.deliverydate > @deliverydate AND id NOT IN
(
    SELECT DISTINCT oh.id
    FROM orderhead oh INNER JOIN ordertrack ot ON oh.ID = ot.OrderId
    WHERE oh.receiver = @receiver AND oh.deliverydate > @deliverydate AND ot.OrderStatus = @status
)
ORDER BY oh.OrderId

Echter heb ik het idee dat dit veel effectiever kan. Iemand ideeën?

[martennis]

Misschien zoals onderstaand? Is denk ik in ieder geval sneller, aangezien je 1x de subquery doet ipv de query voor elk record opnieuw.

Verder ontkom je er denk ik niet aan om 2 collecties met elkaar te vergelijken.

SQL:

DECLARE 
    @receiver INT = 340, 
    @deliverydate INT = 20121201, 
    @status INT = 100 

SELECT oh.id AS [ID], oh.orderid AS [Ordernummer], oh.sender AS [Afzender], oh.receiver AS [Ontvanger], oh.deliverydate AS [Bezorgdatum] 
FROM orderhead oh
LEFT JOIN
(
    SELECT DISTINCT oh.id 
    FROM orderhead oh INNER JOIN ordertrack ot ON oh.ID = ot.OrderId 
    WHERE oh.receiver = @receiver AND oh.deliverydate > @deliverydate AND ot.OrderStatus = @status 
) AS printStatus ON printStatus.id = oh.id
WHERE printStatus.id IS NULL AND oh.receiver = @receiver AND oh.deliverydate > @deliverydate
ORDER BY oh.OrderId

[ Voor 16% gewijzigd door martennis op 24-12-2012 11:13 ]

[PdeBie]

die is volgens sql manager - client statistics iets trager zelfs lijkt het op (luttele milliseconden verschil maar tussen beide query's). Al heb ik dit nog niet echt tegen de live data (grotere aantallen orders) kunnen testen.

Ik zal eens overleggen met de eindgebruiker of de query qua snelheid acceptabel is. Vind het persoonlijk snel genoeg gaan, dus ik ga er vanuit dat het geen probleem op gaat leveren.

Maar mocht iemand toch nog een tip hebben, hoor ik het graag. Het is nooit erg om te leren.

[P.O. Box]

ik snap niet zo goed waarom je een subquery nodig acht... een join met daarna in de where een IS NULL voor een record uit de gejoinde tabel lijkt mij voldoende...

[WoeiWoei]

Zoals P.O Box aangeeft zou ik een left join gebruiken en dan op een null waarde controleren. Over het algemeen performen joins beter dan sub query' s en ze zijn imo beter leesbaar.

Vb:

SQL:

DECLARE 
    @receiver INT = 340, 
    @deliverydate INT = 20121201, 
    @status INT = 100 

SELECT oh.id AS [ID], oh.orderid AS [Ordernummer], oh.sender AS [Afzender], oh.receiver AS [Ontvanger], oh.deliverydate AS [Bezorgdatum] 
FROM orderhead oh 
Left join ordertrack ot on oh.id =ot.OrderId and ot.OrderStatus =@status
WHERE ot.id is null and oh.receiver = @receiver AND oh.deliverydate > @deliverydate
ORDER BY oh.OrderId

[SlaadjeBla]

WoeiWoei schreef op maandag 24 december 2012 @ 18:57:
Zoals P.O Box aangeeft zou ik een left join gebruiken en dan op een null waarde controleren. Over het algemeen performen joins beter dan sub query' s en ze zijn imo beter leesbaar.

Ik weet niet welke DBMS gebruikt wordt, maar SQL Server optimaliseert subqueries naar joins in zijn execution plan. Dus performance-wise zou je er niets mee opschieten. Ik veronderstel dat andere DBMS'en hun query op een soortgelijke manier optimaliseren.

Qua leesbaarheid ben ik het helemaal met je eens, dus je zou beter joins kunnen gebruiken.

[PdeBie]

Ik gebruik inderdaad SQL Server en zoals ik in een eerdere post aangaf werken beide queries ongeveer even snel.

De reden dat ik voor een subquery gekozen had is vrij simpel: ik kende de 'truc' met de join niet
Maar ik moet ook zeggen dat ik nog niet weet of ik dat wel leesbaarder vind. Kan kwestie van gewenning zijn, dus ik ga het een tijdje proberen en dan kijken wat ik fijner vind lezen.

[ACM]

SlaadjeBla schreef op maandag 24 december 2012 @ 19:02:
Qua leesbaarheid ben ik het helemaal met je eens, dus je zou beter joins kunnen gebruiken.

Een query met een join is mogelijk beter leesbaar dan een query met subselect... Maar het doel van de query wordt er juist veel vager door als je een join gebruikt waar een subquery gebruikelijker is. Zeker bij wat uitgebreidere JOINS en WHERE-clauses valt de 'bla IS NULL' wel erg makkelijke weg.

Ik zou dit soort dingen over het algemeen met een NOT EXISTS oplossen, dat is imho het meest natuurlijk. Als je geen directe relatie tussen records hebt past een NOT IN beter en een JOIN is in dit soort gevallen eigenlijk altijd een beetje een hacky oplossing. Er zijn natuurlijk altijd meerdere wegen die naar Rome leiden en e.e.a is ook onderhevig aan smaak

SQL:

DECLARE
    @receiver INT = 340,
    @deliverydate INT = 20121201,
    @status INT = 100

SELECT oh.id AS [ID], oh.orderid AS [Ordernummer], oh.sender AS [Afzender], oh.receiver AS [Ontvanger], oh.deliverydate AS [Bezorgdatum]
FROM orderhead oh
WHERE oh.receiver = @receiver AND oh.deliverydate > @deliverydate 
 AND NOT EXISTS
 (
    SELECT *
    FROM ordertrack ot 
    WHERE ot.OrderId = oh.id
    AND ot.OrderStatus = @status
 )
ORDER BY oh.OrderId

De subquery wordt er ook gelijk een stuk eenvoudiger van en de hele sql-constructie is veel natuurlijker geworden.

[ Voor 15% gewijzigd door ACM op 31-12-2012 11:34 ]

[Zoijar]

WoeiWoei schreef op maandag 24 december 2012 @ 18:57:
Zoals P.O Box aangeeft zou ik een left join gebruiken en dan op een null waarde controleren. Over het algemeen performen joins beter dan sub query' s en ze zijn imo beter leesbaar.

Volgens mij zijn subqueries idd net zo snel. Waar je op moet letten is dat je geen dependent subqueries schrijft, dus waar iets in de subquery afhangt van de omliggende query results.

[Gomez12]

Zoijar schreef op maandag 31 december 2012 @ 11:36:
[...]

Volgens mij zijn subqueries idd net zo snel. Waar je op moet letten is dat je geen dependent subqueries schrijft, dus waar iets in de subquery afhangt van de omliggende query results.

Meten is weten (zoals altijd).
Sommige sql-engines kunnen soms nog wel eens rare execution plans maken die op papier niet 100% kloppen.

[ACM]

Zoijar schreef op maandag 31 december 2012 @ 11:36:
Volgens mij zijn subqueries idd net zo snel. Waar je op moet letten is dat je geen dependent subqueries schrijft, dus waar iets in de subquery afhangt van de omliggende query results.

Dat maakt ook niet per se uit. Vziw wordt mijn voorbeeld in bijvoorbeeld PostgreSQL op (vrijwel?) exact dezelfde wijze uitgevoerd als de vergelijkbare JOIN. Ik zou eerst kijken hoe goed de "mooiste" of "duidelijkste" query werkt. En daarna e.e.a. pas met alternatieven proberen, zelfs bij MySQL is het tegenwoordig niet standaard meer zo dat een (dependant) subquery slechter is voor de performance.

[ Voor 3% gewijzigd door ACM op 31-12-2012 12:56 ]