FB: Hilfe bei Join

[hoika]

Hallo #,

Folgende 2 Tabellen habe ich

Tab1 "Customer"
Id Integer
Name Varchar(X)

Tab2 "Customer_ADC"
Id Integer (PrimKey)
CustomerId Integer -> Ref Customer.Id
StartDate Date
EndDate Date


Die ASC enthält pro Kunde mehrere Einträge.
Ich brauche jetzt den aktuellen Eintrag pro Kunde.
Aktuell heisst EndDate>=:Heute, das StartDate lassen wir mal aussen vor

Sowas mache ich bisher immer mit einer SP etwa so (Pseudocode)

SQL-Code:

ForEach Customer
  Select First 1 ADC.* From ADC
  Where 
    (ADC.CustomerId=Customer.Id)
    (EndDate>=:CheckDate)
  Order by EndDateDesc

Wie bekomme ich das mit einer SQL-Abfrage raus ?
Ob das Join oder Left Join ist, ist mir egal

Das es etwa 2000 Customer-Einträge sind,
sollte möglichst keine SubQuery drin sein.


Hm.
Mein SP sieht jetzt erst mal so aus.

SQL-Code:

CREATE PROCEDURE CUSTOMERASC_GETCURRENTASC (
    THECHECKDATE DATE)
RETURNS (
    Customer_Id INTEGER,
    ASC_Id INTEGER,
    ASC_StartDate DATE,
    ASC_EndDate DATE)
AS
begin
  For Select Customer.Id From Customer
  Join Customer_ASC On Customer_ASC.CustomerId=Customer.Id
  Into :Customer_Id do
  begin
    ASC_Id        = NULL;
    ASC_StartDate = NULL;
    ASC_EndDate   = NULL;

    Select First 1 Id,StartDate,EndDate
    From Customer_ASC
    Where
      (Customer_ASC.CustomerId=:Customer_Id) And
      (Customer_ASC.EndDate>=:TheCheckDate)
    Order By Customer_ASC.EndDate Desc
      Into :ASC_Id,:ASC_StartDate,:ASC_EndDate;

    if (ASC_Id Is Not NUll) then
    begin
      Suspend;
    end
  end  /* Fro Select  */
end

Danke


Heiko

[nahpets]

Hallo,

wie wäre es mit Max(EndDate) und where Max(EndDate) >= :heute?
Kann Firebird sowas?

[hoika]

Hallo,

naja, ich hatte die CustomerId in Customer_ASC vergessen.

Egal, die SP läuft ...
Wäre halt nur schön, dass als normale performante Query zu haben.


Heiko

[joachimd]

Ich habe das mal kurz in ADS nachgebaut:

SQL-Code:

select 
  a.id as AID, c.id as customerid, c.name,
  a.startdate, a.enddate 
  from #customer c
  left join #Customer_ADC a on a.customerid=c.id
  where startdate<=curdate() 
    and enddate>=curdate()

Damit bekommst Du alle aktiven Einträge - allerdings mit Dubletten, falls es mehrere aktive Einträge pro Kunde gibt. Die müsstest Du dann wieder per max() oder min() rausfischen.
Ach ja: ich habe temporäre Tabellen verwendet, daher das #-Zeichen im Tabellennamen.

[hoika]

Hallo,

da es keine Überlappungen der Datumsbereiche gibt,
müsste das sogar so gehen.

Danke

Probier ich aus.
Mal sehen, wie sich FB bei dem Left Join schlägt.


Heiko

[Billa]

Hmmm...

Da habe ich gleich ein paar Fragen:

1. Gibt es immer einen "aktuellen" Eintrag für jeden Kunden?
2. Gibt es Zeiträume in der Zukunft? Also (EndDate > heute) und (Startdate > heute)
3. Ist die Reihenfolge in der Datenbank "natürlich", d.h. jüngere Einträge betreffen auch spätere Zeiträume?


Die Performance hängt m.E. nach wesentlich von der Qualität und Selektivität der Indizes ab. Gerade beim Join.

Und: Ja, es gibt so etwas wie MAX(Enddate) ...

[hoika]

Hallo,

1. Gibt es immer einen "aktuellen" Eintrag für jeden Kunden?
nein

2. Gibt es Zeiträume in der Zukunft? Also (EndDate > heute) und (Startdate > heute)
könnte sein, deshalb das
Order by EndDate Desc und (Customer_ASC.EndDate>=:TheCheckDate)

ohhhhhh,

stimmt, Zukunfs-Einträge dürfen nicht angeeigt werden,
da fehlt noch was in meiner SP *änder*


3. Ist die Reihenfolge in der Datenbank "natürlich", d.h. jüngere Einträge betreffen auch spätere Zeiträume?

Die Zeiträume dürfen sich nicht überlappen
(Das ist noch die nächste DB-Baustelle)


Max(EndDate) muss aber pro Kunde sein


Egal, die SP ist fertig und läuft.


Heiko

[Billa]

Also, weil 2. vorkommen kann, ist m.E. Max(Enddate) nur sinnvoll mit entsprechender Einschränkung in der where-Klausel. Und weil auch 1. gilt, würde ich vermuten, dass die Lösung mit SP erstmal die effektivste ist. Self-Join kommt bestimmt aus Performance-Gründen nicht in Frage.

[omata]

SQL-Code:

SELECT *
FROM customer c
INNER JOIN customer_adc ca
  ON c.id = ca.customerid
WHERE enddate = (SELECT MAX(enddate)
                 FROM customer_adc
                 WHERE customerid = ca.customerid)

[hoika]

Halo,

omata,

verstanden habe ich es nicht ... ;(
Es sei denn, intern ist es ein SubSelect ?

Der Plan:

PLAN (CUSTOMER_ASC INDEX (FK_CUSTOMER_ASC_CUSTOMER))
PLAN JOIN (CA NATURAL, C INDEX (IDX_CUSTOMER_ID))

lässt das vermuten

(auch auf EndDate ist ein Index, der hier aber nicht verwendet wird).


Aber die Sache mit dem Self-Join (auf Customer_ASC) klingt interessant,
ich braucht ja nur die Customer_ID.
Da muss ja gar keine Customer-Table verwenden,
ein

For Select Distinct CustomerId from Customer_ASC reicht.

Erst mal ein paar Daten reinpacken,
dann mache ich ein paar Tests.

Wo wir gerade beim Plan sind,
kennt jemand ein freies Tool wie IB Planalyzer für Firebird ?
Manchmal steigt das Teil bei FB2 aus (FB1.5 geht noch)



Heiko

PS: Erst mal ein bissel laufen gehen (>=20 km ...)