Optimize SQL statement for android sqlite

Question

I'm developing an application that tracks the user's current position and stores it into a SQLite database. Everything works fine, but now I have the problem when querying the database for a track with more than 1000 records it takes nearly 1.5 minutes. On my Desktop it just takes 1 second.

I know it's a query with many subselects but I wasn't able to get the right result another way. In my opinion this belongs to the aggregate functions like avg() and sum().

Here's my query:

Cursor c = readableDB
    .rawQuery(
            "SELECT DISTINCT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description, "
            + "round((SELECT sum(distToPrev)/1000 FROM positions p WHERE p.trackid=t._id) , 2) as distance , " 
            + "(SELECT count(latitude) FROM positions p WHERE p.trackid=t._id) as waypoints, "
            + "(SELECT (avg(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as avgspeed, "
            + "(SELECT (max(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as maxspeed, "
            + "(SELECT sum(altitudeUP) FROM positions p WHERE p.trackid=t._id) as climb , "
            + "(SELECT avg(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as avgheartbeat , "
            + "(SELECT max(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as maxheartbeat , "
            + "(SELECT avg(cadence) FROM heartbeats h WHERE h.trackid=t._id) as avgcadence "
            + "FROM tracks t LEFT JOIN heartbeats h ON t._id = h.trackid WHERE t._id = ?",
            new String[]{String.valueOf(trackId)}); 
    c.moveToFirst();

How can I optimize this query? I tried it already this way, but then the result was wrong and it took the same amount of time.

SELECT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description,
sum(distToPrev)/1000 as distance ,
count(latitude) as waypoints, 
(avg(speed)*3.6) as avgspeed, 
(max(speed)*3.6) as maxspeed, 
sum(altitudeUP) as climb , 
avg(heartbeat)  as avgheartbeat ,
max(heartbeat)  as maxheartbeat ,
avg(cadence) as avgcadence 
FROM tracks t 
LEFT JOIN heartbeats h ON t._id = h.trackid 
INNER JOIN positions p ON t._id = p.trackid 
     WHERE t._id = ?

Since 2 hours I'm looking for a solution and I don't know what I'm doing wrong. Maybe I have to take a break.

EDIT:

Here my create statements:

CREATE TABLE heartbeats(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, heartbeat INTEGER NOT NULL, cadence INTEGER, timestamp TIMESTAMP);


CREATE TABLE positions(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, longitude REAL NOT NULL, latitude REAL NOT NULL, altitude REAL, altitudeUP REAL, speed REAL, accuracy REAL, distToPrev REAL, timestamp TIMESTAMP);


CREATE TABLE tracks(_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT, starttime DATETIME NOT NULL, endtime DATETIME);

Answer 1

Something like this might get you close. Notice that I've included the parameter multiple times in the query:

SELECT
    T._id,
    T.title,
    STRFTIME('%Y-%m-%d' , T.starttime , 'unixepoch' , 'localtime') as date,
    T.description,
    P_SQ.distance,
    P_SQ.waypoints,
    P_SQ.avg_speed,
    P_SQ.max_speed,
    P_SQ.climb
FROM
    Tracks T
LEFT OUTER JOIN
(
    SELECT
        P.trackid,
        SUM(P.distToPrev) AS distance,
        COUNT(P.latitude) AS waypoints,
        AVG(P.speed) * 3.6 AS avg_speed,
        MAX(P.speed) * 3.6 AS max_speed,
        SUM(altitudeUp) AS climb
    FROM
        Positions P
    WHERE
        P.trackid = ?
    GROUP BY
        P.trackid
) P_SQ ON P_SQ.trackid = T._id
LEFT OUTER JOIN
(
    SELECT
        H.trackid,
        AVG(heartbeat) AS avg_heartbeat,
        MAX(heartbeat) AS max_heartbeat,
        AVG(cadence) AS avg_cadence
    FROM
        Heartbeats
    WHERE
        H.trackid = ?
    GROUP BY
        H.trackid
) H_SQ ON H_SQ.trackid = T._id
WHERE
    T._id = ?

Answer 2

Not to oversimplify, but I think you could shave a ton of time off your queries by simply indexing the fields which you are scanning during the query.

By indexing the fields, you will alleviate all of the table scanning that slows down queries as the number of records grow.

The downside of Indexes is the space requirement, but in my experience on Android, it's rarely a factor.

Answer 3

This will do the job:

SELECT tid, date, waypoints, avg(heartbeat) AS avgheartbeat 
FROM (SELECT t._id AS tid, strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') AS date, count(latitude) AS waypoints
         FROM tracks t LEFT JOIN positions p ON t._id = p.trackid  
         WHERE t._id = ?
         GROUP BY t._id , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime'))
    LEFT JOIN heartbeats h ON tid = h.trackid
GROUP BY tid, date, waypoints

Add fields as required.