CODEWITHSUNDEEP
androidsqlite
Miguel Dutra
Miguel Dutra

Reputation: 86

SQLite query taking too long to complete

I have a query taking 4 seconds to complete:

SELECT MAX(Date), Bond_Id, Sell_Price FROM Quotes GROUP BY Bond_Id;

The table has about 31K records:

CREATE TABLE Quotes (
    _id         INTEGER PRIMARY KEY AUTOINCREMENT, 
    Bond_Id     INTEGER NOT NULL, 
    Date        TEXT NOT NULL, 
    Buy_Yield   REAL NOT NULL, 
    Sell_Yield  REAL NOT NULL, 
    Buy_Price   REAL NOT NULL, 
    Sell_Price  REAL NOT NULL, 
    Base_Price  REAL NOT NULL, 
    FOREIGN KEY (Bond_Id) REFERENCES Bonds(_id));

CREATE INDEX QuotesNdx ON Quotes(Bond_Id);

I managed to reduce query time from 7s to 4s, which is still unacceptable. I tried ANALYZE, additional composite indexes and to remove the foreign key.

EXPLAIN output:

0 Trace 0 0 0 explain select max(date),bond_id,sell_price from quotes group by bond_id; 00 
1 Noop 0 0 0  00 
2 Integer 0 6 0  00 
3 Integer 0 5 0  00 
4 Goto 0 20 0  00 
5 Integer 1 6 0  00 
6 Return 0 0 0  00 
7 IfPos 5 9 0  00 
8 Return 0 0 0  00 
9 AggFinal 1 1 0 max(1) 00 
10 SCopy 1 9 0  00 
11 SCopy 2 10 0  00 
12 SCopy 3 11 0  00 
13 ResultRow 9 3 0  00 
14 Return 0 0 0  00 
15 Null 0 2 0  00 
16 Null 0 3 0  00 
17 Null 0 4 0  00 
18 Null 0 1 0  00 
19 Return 0 0 0  00 
20 Gosub 0 15 0  00 
21 Goto 0 48 0  00 
22 SetNumColumns 0 7 0  00 
23 OpenRead 0 6 0  00 
24 SetNumColumns 0 2 0  00 
25 OpenRead 2 7 0 keyinfo(1,BINARY) 00 
26 Rewind 2 44 13 0 00 
27 Noop 2 -7 13 0 01 
28 IdxRowid 2 16 0  00 
29 MoveGe 0 0 16  00 
30 Column 2 0 8  00 
31 Eq 7 36 8 collseq(BINARY) 10 
32 Move 8 7 0  00 
33 Gosub 0 7 0  00 
34 IfPos 6 47 0  00 
35 Gosub 0 15 0  00 
36 Column 0 2 17  00 
37 CollSeq 0 0 0 collseq(BINARY) 00 
38 AggStep 0 17 1 max(1) 01 
39 SCopy 7 2 0  00 
40 Column 0 6 3  00 
41 RealAffinity 3 0 0  00 
42 Integer 1 5 0  00 
43 Next 2 27 0  00 
44 Close 0 0 0  00 
45 Close 2 0 0  00 
46 Gosub 0 7 0  00 
47 Halt 0 0 0  00 
48 Transaction 0 0 0  00 
49 VerifyCookie 0 9 0  00 
50 TableLock 0 6 0 Quotes 00 
51 Goto 0 22 0  00

Upvotes: 1

Views: 2936

Answers (3)

Miguel Dutra
Miguel Dutra

Reputation: 86

Thank you all for your answers. Actually, the offender in my query was the "GROUP BY". I managed to find a solution by reading this particular passage on SQLite's SELECT (http://sqlite.org/lang_select.html) documentation:

"If the SELECT statement is an aggregate query with a GROUP BY clause, then each of the expressions specified as part of the GROUP BY clause is evaluated for each row of the dataset. Each row is then assigned to a "group" based on the results; rows for which the results of evaluating the GROUP BY expressions are the same are assigned to the same group. For the purposes of grouping rows, NULL values are considered equal. The usual rules for selecting a collation sequence with which to compare text values apply when evaluating expressions in a GROUP BY clause. The expressions in the GROUP BY clause do not have to be expressions that appear in the result. The expressions in a GROUP BY clause may not be aggregate expressions.

So, the solution was to create a composite index containing (Date, Bond_Id) and replace my query by something like:

SELECT Date, Bond_Id, Sell_Price FROM Quotes
   WHERE Bond_Id=Bonds._id
   AND Date=(SELECT MAX(Date) FROM Quotes);

Now this query is taking less than 1 second to complete, which is great!

Upvotes: 0

CL.
CL.

Reputation: 180070

This particular query can be optimized by creating a covering index; the columns must be in the order in which they are used for lookups:

CREATE INDEX whatever ON Quotes(Bond_ID, Date, Sell_Price);

Upvotes: 4

Dmitriy Lozenko
Dmitriy Lozenko

Reputation: 555

Your query:

SELECT MAX(Date), Bond_Id, Sell_Price FROM Quotes GROUP BY Bond_Id;

FIRST: Your query is incorrect. You should not use reserved words as fields names. In your case is a field "Date"

If you use any field in GROUP BY, you also should use all others fields inside SELECT with any grouping function(MIN/MAX/COUNT/etc..)

Corrent query should be:

SELECT MAX(Date), Bond_Id FROM Quotes GROUP BY Bond_Id;

or

SELECT Bond_Id, MAX(Sell_Price) FROM Quotes GROUP BY Bond_Id HAVING "Date" = MAX("Date");

SECOND:

You need to create indexes for every field used in MIN/MAX/... and in GROUP BY

Upvotes: -1

Related Questions