Performance Einbruch FB 2.5 bei Abfrage auf zusätzliches Feld

[Union]

Gegeben ist folgende Abfrage:

Code:

select  
    lsptext.art,
    lsptext."POSITION" Pos,
    lsptext.textline
from sendpos
left outer join lscoll on lscoll.lskopf_id = sendpos.lskopf_id
inner join lspack on 
  lspack.lskopf_id = lscoll.lskopf_id 
  and lspack.colli_nr = lscoll.colli_nr
inner join lsptext on 
   lsptext.lspos_id = lspack.lspos_id 
   and lsptext.art in ('G1', 'G2', 'G3', 'G4', 'G5', 'G6', 'D1', 'D2', 'D3', 'D4', 'D5') -- JOIN
where sendpos.sendk_id = 101821515

Die Ausführungszeit beträgt ~ 0.7s, Datenmenge ist 1.

Will ich nun zusätzlich das "POSITION" Feld abfragen, so erhöht sich die Abfragezeit auf ~5 s. Dabei spielt das Abfragekonstrukt keine Rolle:

Abfrage mit JOIN erweitert

Code:

and lsptext."POSITION" = 25

Abfrage als Subquery

Code:

select x.* from
(
-- <obige Query>
) x
where x.Pos = 25

Abfrage mit WHERE erweitert

Code:

and lsptext."POSITION" = 25

Alle relevanten Felder sind indiziert

Code:

CREATE TABLE LSPTEXT 
(
  ID   DOUBLE PRECISION NOT NULL,
  LSKOPF_ID   DOUBLE PRECISION,
  LSPOS_ID   DOUBLE PRECISION,
  "POSITION"   SMALLINT,
  KSL   SMALLINT,
  ART   CHAR(4),
  TEXTLINE   CHAR(132),
CONSTRAINT PK_LSPTEXT PRIMARY KEY (ID)
);

/*  Index definitions for LSPTEXT */

CREATE INDEX LSPTEXT_ART ON LSPTEXT(ART);
CREATE INDEX LSPTEXT_ID ON LSPTEXT(ID);
CREATE INDEX LSPTEXT_LSKOPF_ID ON LSPTEXT(LSKOPF_ID);
CREATE INDEX LSPTEXT_LSPOS_ID ON LSPTEXT(LSPOS_ID);
CREATE INDEX LSPTEXT_POSITION ON LSPTEXT("POSITION");

Laut PLAN werden die auch benutzt:

Code:

Select - PLAN JOIN (JOIN (JOIN (SENDPOS INDEX (SENDPOS_LSNR), LSCOLL INDEX (LSCOLL_LSKOPF_ID)), LSPACK INDEX (LSPACK_LSKOPF_ID, LSPACK_COLLI_NR)), LSPTEXT INDEX (LSPTEXT_LSPOS_ID, LSPTEXT_POSITION))

[haentschman]

Hallöle...
lsptext."POSITION" Pos, ...was passiert wenn du das Feld ohne "" in einen gültigen Namen umbenennst?
lsptext."POSITION" as Pos, ...was passiert hier?

[Union]

Danke, das Umbenennen hat es beschleunigt.

Nichtdesdotrotz ist "POSITION" ein gültiger Name, im Gegensatz zu POSITION. Deshalb gibt es ja das Quoting. Es handelt sich auch um ein DB-agnostisches Projekt. Im FireDAC SQL wird bei solchen unklaren Fällen auch das ID-Escape verwendet.

{id <Bezeichnername>}

[IBExpert]

mal ein genereller tip an alle mit firebird fragen:

es hilft ungeheuer weiter, das problem zu verstehen, wenn ihr alle relevanten Objekte
als metadaten hier mit aufführt, insbesondere wenn es um joins über mehrere Tabellen
geht. Nur eines von 3 beteiligten ist da wenig zielführend und metadaten sind selten
geheimniswürdig. (ddl findet man in ibexpert auf der seite ddl, in anderen tools
ggf auch irgendwo)

Außerdem bringt es auch erheblich vorteile, wenn man eine Angabe hat, in welcher Tabelle
wieviele Datensätze sind. noch besser wäre dabei zu jeder Tabelle die Statistikwerte
(kann man für die beteiligten Tabellen in ibexpert per drag and drop in das
Statstikfenster zeihen, da steht dann alles, wenn man die checkbox dort oben
"Version info" angeklickt hat. Aber selbst die Anzahl der Datensätze hilft schon
gerne auch geraten, wenn ein count() zu viel verlangt ist

[IBExpert]

Zitat von Union:

Danke, das Umbenennen hat es beschleunigt.

Nichtdesdotrotz ist "POSITION" ein gültiger Name, im Gegensatz zu POSITION. Deshalb gibt es ja das Quoting. Es handelt sich auch um ein DB-agnostisches Projekt. Im FireDAC SQL wird bei solchen unklaren Fällen auch das ID-Escape verwendet.

{id <Bezeichnername>}

Komponenten machen da manchmal seltsame dinge, nicht immer aber auch das was man erwartet. Wenn ein externes Tool das auch macht, dann weiss man wo man suchen kann, wenn nicht, ist es meist die komponente, beispielsweise beim dataset packetrecords or recordcount.

[Union]

Die o.a. Queries waren manuell gemacht.

Ich habe eine weitere Lösung erraten:

Code:

cast("POSITION" as Integer)  = 25

Erhöht die Laufzeit nicht.

Code:

"POSITION"   = Cast(25 as smallint)

aber sehr wohl. Es hat scheints nicht so viel mit dem reservierten Namen (der allerdings durch das Quoting entschärft wurde) zu tun, sondern mit dem Vergleich bei unterschiedlichen numerischen Datentypen (SMALLINT / INTEGER).

[Delphi.Narium]

Eine eher befremdlich wirkende Variante:

SQL-Code:

select
    lt.art,
    lt.Pos,
    lt.textline
from sendpos
left outer join lscoll on
  lscoll.lskopf_id = sendpos.lskopf_id
inner join lspack on
      lspack.lskopf_id = lscoll.lskopf_id
  and lspack.colli_nr  = lscoll.colli_nr
inner join (
  select
    lspos_id, art, "POSITION" as Pos, textline
  from lsptext -- hier erstmal aus der Tabelle lsptext das rausfiltern, was wir garantiert benötigen
  where "POSITION" = 25
  and art in ('G1', 'G2', 'G3', 'G4', 'G5', 'G6', 'D1', 'D2', 'D3', 'D4', 'D5')
) lt on
   lt.lspos_id = lspack.lspos_id
where sendpos.sendk_id = 101821515

Unter Oracle hab' ich mit solchen Konstrukten in der Vergangenheit zuweilen schonmal ein paar Stunden sparen können (es ging aber auch in den Bereich von 100 bis 200 Mio. Datensätzen)
Ob sich Firebird mit solchen Konstrukten beschleunigen lässt, weiß ich nicht, es könnte daher also auch nach hinten losgehen.

Überspitzen könnte man es dann mit:

SQL-Code:

select
    lt.art,
    lt.Pos,
    lt.textline
from (
  select 
    lskopf_id
  from sendpos 
  where sendpos.sendk_id = 101821515 -- Menge möglichst klein halten vor dem Join mit anderen Tabellen.
) sp
left outer join lscoll on
  lscoll.lskopf_id = sp.lskopf_id
inner join lspack on
      lspack.lskopf_id = lscoll.lskopf_id
  and lspack.colli_nr  = lscoll.colli_nr
inner join (
  select
    lspos_id, art, "POSITION" as Pos, textline
  from lsptext -- hier erstmal aus der Tabelle lsptext das rausfiltern, was wir garantiert benötigen
  where "POSITION" = 25
  and art in ('G1', 'G2', 'G3', 'G4', 'G5', 'G6', 'D1', 'D2', 'D3', 'D4', 'D5')
) lt on
   lt.lspos_id = lspack.lspos_id

Kann was bringen, muss aber nicht, je nach Datenmenge in den "Zwischenergebnissen".

Was meinst Du mit Datenmenge ist 1 ?
Ergebnismenge oder Gesamtdatenbestand?

[Union]

Das Resultset enthält nur einen Satz. Ich habe es aber gelöst. Es lag an dem Vergleich SMALLINT / INTEGER.

Die Datenmengen

SENDPOS 1195320

LSCOLL 1367358

LSPOS 11404522

LSPACK 10821130

LSPTEXT 7165841

Das Ausführen der Query ohne den Vergleich auf das Feld POSITION mit 25 ergibt (0,6 s):

Code:

G1 25  LI-Gép - Közúti/tengeri - ragassz "Közúti/tengeri" LI matricát a kollira

[Blup]

Ich vermute das Problem nicht in der Typ-Umwandlung.

Laut Plan benutzt die Abfrage für den Zugriff LSPTEXT Index (LSPTEXT_LSPOS_ID, LSPTEXT_POSITION).
LSPTEXT_POSITION hat wahrscheinlich eine schlechte Selectivität.
Alle Datensätze die LSPTEXT_LSPOS_ID liefert, werden mit allen Datensätzen aus LSPTEXT_POSITION verglichen und die Schnittmenge gebildet.
Das dauert länger als bei allen Datensätzen aus LSPTEXT_LSPOS_ID direkt das Feld "POSITION" zu prüfen.
Auf die Schnittmenge wirkt dann erst die Bedingung für lsptext.art.

"POSITION" ist als smallint definiert, durch die Typumwandlung vor dem Vergleich kann der Index LSPTEXT_POSITION nicht mehr angewendet werden.

Der Index "LSPTEXT_POSITION" macht in dieser Tabelle keinen Sinn.
Ich kann mir keine Abfrage vorstellen, die "POSITION" erfordert, aber bei der "LSPOS_ID" nicht berücksichtigt wird.
Hier ist ein kombinierter Index über "LSPOS_ID" und "POSITION" effektiv und kann den INDEX LSPTEXT_LSPOS_ID und LSPTEXT_POSITION ersetzen.

[Union]

@blup
Deine Vermutungen sind hier nicht zutreffend. Denn wenn die Bedingung

Code:

cast("POSITION" as Integer) = 25

0,6 s dauert und

Code:

"POSITION" = 25

5 s dann liegt es nicht am Index. Für Sonderfälle sind zusammengesetzte Indizes bestimmt gut, ansonsten ist FB meiner Erfahrung nach sehr gut in der Lage, sich den Zugriffsplan aus einzelnen Feldindizes zusammenzubauen..