CODEWITHSUNDEEP
iphoneperformancesqlite
Tikkes
Tikkes

Reputation: 4689

JOIN versus subquery - performance sqlite3

So I got 3 tables:

Locations (LocationID [PK])

Track_Locations (TrackID [FK], LocationID [FK])

Tracks (TrackID [PK])

I'm building an app for iPhone and I was told that a JOIN would probably work faster then a subquery would. Is this true? I have searched around but can't find a straight answer to this and would like to know what is best to use in this case.

My queries in both cases would look like this:

Subquery:

SELECT LocationID, TimestampGPS, Longitude, Latitude, ... 
FROM locations 
WHERE LocationID 
IN (SELECT LocationID FROM track_locations WHERE TrackID = ?)

Using JOIN:

SELECT l.LocationID, l.TimestampGPS, l.Longitude, l.Latitude, ... 
FROM locations l
JOIN track_locations tl
ON (l.LocationID = tl.LocationID)
JOIN tracks t
ON (t.TrackID = tl.TrackID)
WHERE t.TrackID = ?

Upvotes: 2

Views: 1200

Answers (1)

theory
theory

Reputation: 9887

A JOIN is almost always faster than a subquery, because it results in a single query, whereas a subquery is two queries. Some databases, however, know how to optimize such simple subqueries into JOINs, though I doubt SQLite does.

That said, sometimes, given the structure of one's data, it is faster to do a subquery. The only real way to find out is to load your database with a likely collection of sample data from the wild and benchmark both approaches.

My suspicion is that it will be a wash, however. Most iPhone apps don't have enough rows in the database to make much difference.

Upvotes: 3

Related Questions