Sqlite: Komplexe Abfrage

[Uhu U. (uhu)]

Ich habe eine Datenbank mit mehreren Tabellen, u.a.

  beobachtungen: Titel, PosID
  locations:     PosID, Lat, Lng

Die Abfrage (1)

  select PosId from locations where Lat > 49.123 and Lat < 49.124 and Lng > 8.390 and Lng < 8.391;

spuckt eine Liste mit 40 Locations aus.

Nun will ich eine Liste aller Einträge aus beobachtungen, deren PosID in 
der Liste der Abfrage (1) vorkommt.

Die Abfrage lautet (2):

  select * from beobachtungen where Titel="Frosch" and PosID=(
    select PosID from locations where Lat > 49.123 and Lat < 49.124 and
     Lng > 8.390 and Lng < 8.391);

Nun wundere ich mich, warum die Ergebnistabelle nur 1 Zeile¹ enthält, 
obwohl  die Abfrage (3)

  select * from beobachtungen where Titel="Frosch";

302 Zeilen liefert und alle Zeilen mit den PosIDs aus (1) enthalten 
sind.

Wo ist der Fehler?

---

¹) Es ist die erste Zeile aus (3), die die erste PosID aus (1) enthält.

[Uhu U. (uhu)]

Des Rätsels Lösung:

Die Abfrage (2) muss lauten:

  select * from beobachtungen where Titel="Frosch" and PosID in (
    select PosID from locations where Lat > 49.123 and Lat < 49.124 and
     Lng > 8.390 and Lng < 8.391);

[Xeraniad X. (xeraniad)]

... oder mit join und table alias names (hier 'b', 'l'):
SELECT *
FROM   beobachtungen b, locations l
WHERE  b.PosID = l.PosID
  AND "Frosch" = b.Titel
  AND   49.123 < l.Lat AND l.Lat < 49.124
  AND    8.390 < l.Lng AND l.Lng <  8.391;

[Uhu U. (uhu)]

Die beiden Versionen unterscheiden sich in der Ausführungszeit um etwas 
mehr, als einen Faktor 2.

Deine Version erzeugt natürlich erst mal wesentlich mehr Spalten in der 
Ausgabetabelle. Dann könnte auch die zweistufige Ausführung mit dem in 
noch Zeit sparen.

[Xeraniad X. (xeraniad)]

Ja, z. B. PosID erscheint 2 mal, wegen nur Stern nach SELECT. Aber das 
lässt sich mit einer
select list anstelle Stern lösen.
Welche Varinante ist schneller?
Normalerweise verwendet man bei relationalen Datenbanken in solchen 
Fällen joins und nicht IN.
Performance lässt sich erst mit Kenntnis von indices und optimizer 
beurteilen.

[Uhu U. (uhu)]

Xeraniad X. schrieb:
> Welche Varinante ist schneller?

Meine.

Wenn man bei deiner Version eine Liste mit den Spalten angibt, die meine 
Version mit * ausgibt, sind die Zeiten ungefähr gleich.

> Performance lässt sich erst mit Kenntnis von indices und optimizer
> beurteilen.

Was ist das?

[c-hater (Gast)]

Xeraniad X. schrieb:

> Normalerweise verwendet man bei relationalen Datenbanken in solchen
> Fällen joins und nicht IN.

Das kommt doch sehr stark darauf an.

Das ist nur das Ziel, ein Ideal. Es ist nicht immer zu erreichen, bzw. 
es wäre in manchen Fällen sogar kontraproduktiv, es erreichen zu wollen.

> Performance lässt sich erst mit Kenntnis von indices und optimizer
> beurteilen.

Genau das sind die zwei Knackpunkte:
- die Struktur der Daten bezüglich der Abfrage
- die Aktualität des Kenntnisstands des Optimizers bezüglich des 
aktuellen
  Datenbestands

Ersteres könnte man leicht ändern, würde aber eventuell wiederum das 
Ergebnis wichtigerer Abfragen verschlechtern.
Zweiteres könnte man ebenfalls leicht ändern (sprich: verbessern), das 
würde aber ebenfalls eventuell das Ergebnis wichtigerer Abfragen 
verschlechtern.

Das ist der eigentliche Knackpunkt: auf eine einzelne Abfrage kann man 
jede relationale DB sehr leicht optimieren, wenn man erstmal das Prinzip 
relationaler DBs grundlegend verstanden hat. Hier gibt es nach der 
Optimierung der Struktur nur noch einen TradeOff: Abfragen vs. 
Aktualisierung für den Optimizer.

Sobald es aber mehrere konkurrierende Abfragen gibt, wird es schnell 
richtig kompliziert, denn kann man sich vor der Fülle widerstreitender 
TradeOffs kaum noch retten. Das zu überblicken, zu analysieren und zu 
optimieren, ist Profi-Werk.

[Clemens L. (c_l)]

Xeraniad X. schrieb:
> Normalerweise verwendet man bei relationalen Datenbanken in solchen
> Fällen joins und nicht IN.

Vor vielen Jahren hat der Oracle-Optimizer Joins bevorzugt. Heutzutage 
ist das anders, und war bei SQLite noch nie so.

Aber wenn es wirklich darauf ankommt, muss man zumindest EXPLAIN QUERY 
PLAN anschauen, besser selber messen.

> Welche Varinante ist schneller?

Wenn der einzige Index auf dem Titel ist, der Join. Ansonsten IN, 
insbesondere so:

CREATE VIRTUAL TABLE Locations USING RTree(PosID, Lat, Lng);

(siehe https://www.sqlite.org/rtree.html)