Performance-Prob mit Subselect

[hoika]

Hallo #,

ich habe folgende Tabellen

project: project_id, projekt_state
pos: pos_id, project_id, pos_state
booking: booking_id,project_id
except_projects: except_projects:_id,project_id

in projects stehen Projekte (wer hätte das gedacht),
in pos stehen Projekt-Positionen,
in booking Projekt-Buchungen.

Ich möchte jetzt alle Buchungen der Projekte/Positionen, die nicht in
except_projects stehen

SQL-Code:

Select Booking.booking_id,Booking.project_id From Booking
Join Project On Project.project_id=Booking.project_id
Join Pos On Pos.project_id=Project.project_id
where Booking.project_id not in
(Select except_projects.project_id From except_projects)

es werden noch Stati abgefragt (project_state, pos_state)

Hm, soweit so gut,
jetzt habe ich aber ein massives Performance-Problem.

10 sec. Prepare, 10 sec. Fetch (lokal)


except_projects: 160 Einträge, aber 9 Mio. ausgelesen
project:: 195 Einträge, 80.000 mal ausgelesen
pos:: 4000 Einträge, 80.000 mal ausgelesen

Im Plan sind nur Index-Scans, kein natural.


Wie könnte ich die Abfrage ändern ?


Edit:
=====
Das ganze als SP dauert komplett nur 3 Sekunden,
also wieder SP basteln ;(



Heiko

[Dax]

Ich kenn mich mit Firebird nicht besonders aus, aber bringt es vielleicht was, die Abfrage so zu gestalten?

SQL-Code:

select s.b_id, s.p_id from 
  (Select Booking.booking_id as b_id,Booking.project_id as p_id From Booking 
   Join Project On Project.project_id=Booking.project_id 
   Join Pos On Pos.project_id=Project.project_id
  ) as s 
where s.p_id not in (Select except_projects.project_id From except_projects)

Überhaupt klingt das Problem sehr merkwürdig :gruebel: Ist FB vielleicht so eingestellt, keine temporäre Tabellen oder ähnliches für solche Queries anzulegen?

[hoika]

Hallo,

und nun das ganz gemeine,
die Query ist nur unter FB2 (die aktuellste) so extrem langsam,
unter FB1.5 hat die

FB1.5
SP: 8 ms execute, 3000 ms fetch
Query: 800 ms execute, 3000 ms fetch

FB2.0
SP: etwa wie FB1.5
Query: 8000 ms execute, 10000 ms fetch


Heiko

[mkinzler]

Und wenn du die 4 tabelle auch dazujoinst?

[DeddyH]

Du kannst ja auch mal hier nachlesen: http://www.sql-tips.de/index.php/INN...von_Subselects

Hth

[hoika]

Hallo mkinzler,

welche 4. Tabelle ?
Das ist ja ein Subselect mit NOT IN.

OK, man könnte ein outer join mit "IS NULL" machen.

wie DeddyH aus genau hingelinkt hat ... ;)
mal sehen


Heiko

[alzaimar]

SQL-Code:

Select Booking.booking_id,
       Booking.project_id 
  From (
       Booking
       Join Project On Project.project_id=Booking.project_id
       Join Pos On Pos.project_id=Project.project_id
       ) 
       Left join except_projects on except_projects.project_id = Booking.projectID
where except_projects.project_id is null

So gehts ohne subselect, vielleicht ist es schneller. Eigentlich sollte das ja in Nullkommanix gehen....

Ach ja: Ich weiss, das Du was gegen Left Joins hast, aber man weiss nie, was ein Optimizer draus macht.

[hoika]

Hallo,

die Query von alzaimar bringt es fast.

SQL-Code:

Select Booking.booking_id,
       Booking.project_id
  From (
       Booking
       Join Project On Project.project_id=Booking.project_id
       Join Pos On Pos.position_id=Booking.position_id
       )
       Left join except_projects on except_projects.project_id = Booking.projectID
where except_projects.project_id is null

OK das Booking.position_id hatte ich unterschlagen.

Ausführungszeit unter FB1.5 und FB2.0 ist OK,

1600 ms execution, 3000 ms fetch


Tja, mit einer SP bin ich bei 9 ms execution.

Soll ich nu ne SP nehmen oder besser das "Standard"-SQL lassen.


Was meint ihr ?


Heiko

[mkinzler]

Wenn die SP schon existiert, warum nicht nehmen?

[hoika]

Hallo,

die habe ja nur mal so schnell "getippert".
Das Problem ist, wenn ich mal ne andere DB unterstützen will,
muss ich wieder eine SP anpassen.


Heiko

[mkinzler]

Musst du so höchstwahrscheinlich auch. in einer SP kann man DBMS-spezifische Dinge oft besser "verstecken"

[hoika]

Hallo,

ja schon, aber ne SP muss man auch pflegen,
das geht bei Quellcode halt einfacher.


Heiko

[alzaimar]

hoika,

sehe ich das richtig, das die drei Tabellen jeweils nur 1000, 4000 und 160 Einträge haben? Dann darf das doch nicht so lange dauern. Das müsste doch im 100-500ms Bereich liegen, auf jeden Fall unter 1 Sekunde. FB ist doch nicht wirklich so lahm, oder? :shock:

[TBx]

versuch mal folgendes:

SQL-Code:

Select Booking.booking_id, 
       Booking.project_id 
  From Booking 
    Join Project On Project.project_id=Booking.project_id 
    Join Pos On Pos.position_id=Booking.position_id 
  where not exists (
                    select except_projects.project_id 
                      from except_projects
                      where except_projects.project_id = booking.project_id 
                   )

Das Konstrukt exists bzw. not exists ist oft deutlich schneller als das in /not in Konstrukt, da eine kleinere Ergebnismenge erstellt werden muss.

Gruß

onlinekater

[hoika]

Hallo

> alzaimar
Die booking Tabelle enthält 300.000 Einträge.

> onlinekater
Die Not Exists Variante dauert hier länger als das Subselect (700 ms vs. 1100 ms),
der Zugriffs-Plan ist aber der gleiche.

Die Lösung mit dem left join klappt auch unter FB2.
Puh, doch keine SP benutzen
(der Code wird erweitert, ich brauche also viell. demnächst mehr Parameter,
da ist eine Query einfacher als ne SP).


Heiko

[alzaimar]

Zitat:

Zitat von onlinekater

versuch mal folgendes:

So gut wie identisch mit der langsamen Lösung von hoika.

hoika: Ich habe mir angewöhnt, Standardqueries (die also zum Grundrepertoire der Anwendung gehören) in Views oder UDF zu packen. Man hat eine Abstraktionsschicht, die zudem noch eine Performancesteigerung bringt.

Du hast hier z.B. eine Sicht auf Positionen und Projekte, die nicht in der Except-Liste stehen. Das würde ich in eine View packen. WEnn FB hier schwächelt, dann eben in eine UDF oder SP. Nun sieht die Anwendung nur die View, SP oder UDF und erwartet einen bestimmten Output. Wie die Tabellen auf der DBMS-Seite konkret aussehen, ist egal und kann im Notfall auch nachträglich verändert werden.

[hoika]

Hallo,

schon klar, dass mit SP oder View, aber:

so muss ich nur das Programm warten,
bei einer SP (deren Parameter sich vielleicht auch noch ändern),
ist das sehr viel mehr Aufwand.

Bisher benutze ich SP's nur, wenn es unbedingt notwendig ist (Performance).


Heiko