Sqlite: Komplexe Abfrage

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);

... 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;

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.

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.

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?

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:

1

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

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