SQLite query optimization (subquery and join) help

Question

I have a table for a Statistical project.

Structure is like this:

CREATE TABLE NewStatHistory (
    StatHistoryID uniqueidentifier PRIMARY KEY NOT NULL,
    DateEntered dateTime NOT NULL,
    DateApplies dateTime NOT NULL,
    WhoEnteredID uniqueIdentifier NOT NULL,
    PostingID uniqueIdentifier NULL,
    EnteredValue decimal(19,5) NOT NULL,
    StatID uniqueIdentifier NOT NULL,
    StatStatus int NOT NULL,
    Notes varchar(500) NULL,
    CampusID uniqueidentifier NOT NULL,
    IsTarget bit NOT NULL DEFAULT 0
)

I need to pull the most recently entered value for each "DateApplies".

This query runs almost instantly in SqlServer but in SQLite it just times out and I can't figure out how to optimize to get it to work.

SELECT NewStatHistory.* 
    FROM NewStatHistory
       INNER JOIN (
        SELECT MAX(DateEntered) entered, statID, DateApplies
        FROM NewStatHistory
        WHERE StatID = @statID 
         AND campusID = @campusID
         AND IsTarget = 0
        GROUP BY DateApplies, statID) summary ON summary.entered = newstathistory.dateEntered 
         AND summary.statID = newStatHistory.statID AND summary.DateApplies = newStatHistory.DateApplies
       WHERE NewStatHistory.StatID = @statID
        AND IsTarget = 0
        AND campusID = @campusID
       ORDER BY NewStatHistory.DateApplies DESC

Anybody have any ideas about how to get it to work. Otherwise, I'm going to have to find another embedded db to use. Other than this query, SQLite has done everything I have asked of it.

As far as indexes go, on SqlServer, I only have the primary key indexed. On the SQLite, I tried with only the primary key, but then added on several other columns to no avail.

The subquery executes in no time on SQLite also, so it seems to be the join that causes the slow.

Answer 1

Running EXPLAIN QUERY PLAN on your query suggests that it is doing 2 table scans for this query.

You may want to read The SQLite Query Optimizer Overview page for more information on how the optimizer works

something similar to this may help(untested, other than seeing it being used):

CREATE INDEX foo ON NewStatHistory(statID, campusID, IsTarget, DateApplies);

Answer 2

I found out it's actually specified in the documentation that this type of join isn't well supported. The way to get around it is to use another table instead of a sub-query. Then it's back to being blazingly fast.