Why takes the second SELECT three times as much time?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Why takes the second SELECT three times as much time?

Cecil Westerhof-5
I have defined the following table:
    CREATE TABLE messages (
        date    TEXT NOT NULL DEFAULT CURRENT_DATE,
        time    TEXT NOT NULL DEFAULT CURRENT_TIME,
        type    TEXT NOT NULL,
        message    TEXT NOT NULL,

        PRIMARY KEY (date, time, type)
    );
    CREATE INDEX messages_date ON messages(date);
    CREATE INDEX messages_time ON messages(time);
    CREATE INDEX messages_type ON messages(type);

And the following view:
    CREATE VIEW temperatureStatistics AS
    SELECT   date         AS Date
    ,        MIN(message) AS Minimum
    ,        MAX(message) AS Maximum
    ,        AVG(message) AS Average
    ,        COUNT(*)     AS Count
    FROM     messages
    WHERE    type = 'cpu-temp'
    GROUP BY date

I also created the folowing two queries:
    SELECT   date         AS Date
    ,        MIN(message) AS Minimum
    ,        MAX(message) AS Maximum
    ,        AVG(message) AS Average
    ,        COUNT(*)     AS Count
    FROM     messages
    WHERE    type = 'cpu-temp'
         AND date BETWEEN (SELECT date('now', '-7 day'))
                      AND (SELECT date('now', '-1 day'))
    GROUP BY date
    ORDER BY date DESC

and:
    SELECT   *
    FROM     temperatureStatistics
    WHERE    date BETWEEN (SELECT date('now', '-7 day'))
                      AND (SELECT date('now', '-1 day'))
    ORDER BY date DESC

But the first one is about three times as fast as the second one. What
am I doing wrong here?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why takes the second SELECT three times as much time?

Luuk


On 16-10-16 12:00, Cecil Westerhof wrote:

> I have defined the following table:
>      CREATE TABLE messages (
>          date    TEXT NOT NULL DEFAULT CURRENT_DATE,
>          time    TEXT NOT NULL DEFAULT CURRENT_TIME,
>          type    TEXT NOT NULL,
>          message    TEXT NOT NULL,
>
>          PRIMARY KEY (date, time, type)
>      );
>      CREATE INDEX messages_date ON messages(date);
>      CREATE INDEX messages_time ON messages(time);
>      CREATE INDEX messages_type ON messages(type);
>
> And the following view:
>      CREATE VIEW temperatureStatistics AS
>      SELECT   date         AS Date
>      ,        MIN(message) AS Minimum
>      ,        MAX(message) AS Maximum
>      ,        AVG(message) AS Average
>      ,        COUNT(*)     AS Count
>      FROM     messages
>      WHERE    type = 'cpu-temp'
>      GROUP BY date
>
> I also created the folowing two queries:
>      SELECT   date         AS Date
>      ,        MIN(message) AS Minimum
>      ,        MAX(message) AS Maximum
>      ,        AVG(message) AS Average
>      ,        COUNT(*)     AS Count
>      FROM     messages
>      WHERE    type = 'cpu-temp'
>           AND date BETWEEN (SELECT date('now', '-7 day'))
>                        AND (SELECT date('now', '-1 day'))
>      GROUP BY date
>      ORDER BY date DESC
>
> and:
>      SELECT   *
>      FROM     temperatureStatistics
>      WHERE    date BETWEEN (SELECT date('now', '-7 day'))
>                        AND (SELECT date('now', '-1 day'))
>      ORDER BY date DESC
>
> But the first one is about three times as fast as the second one. What
> am I doing wrong here?
>

Because your second query has to build the complete view before it can
decide if a result is between the selected dates?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why takes the second SELECT three times as much time?

Jens Alfke-2

> On Oct 16, 2016, at 4:49 AM, Luuk <[hidden email]> wrote:
>
> Because your second query has to build the complete view before it can decide if a result is between the selected dates?

I didn’t think a view had a physical manifestation that had to be built; I thought it was just a shortcut/macro for a nested SELECT statement.
Or is the query optimizer not able to convert the nested SELECT into the same form as the first query?

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why takes the second SELECT three times as much time?

Simon Slavin-3

On 16 Oct 2016, at 7:17pm, Jens Alfke <[hidden email]> wrote:

> I didn’t think a view had a physical manifestation that had to be built; I thought it was just a shortcut/macro for a nested SELECT statement.
> Or is the query optimizer not able to convert the nested SELECT into the same form as the first query?

The query optimizer cannot flatten all sub-SELECTs.  It always understands JOIN but the analysis needed for every possible SELECT combination is too much.

To figure out what it's doing use EXPLAIN QUERY PLAN.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why takes the second SELECT three times as much time?

Keith Medcalf
In reply to this post by Jens Alfke-2

See https://www.sqlite.org/optoverview.html
under section 10.0 Query Flattening

Your query is:

SELECT * FROM <view> WHERE <condition>

which could be treated as

SELECT *
  FROM (view select statement)
 WHERE condition

and then flattened.  Note however that the query WILL NOT be flattened because of rule #2, the subselect in the FROM clause contains an aggregate ...

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jens Alfke
> Sent: Sunday, 16 October, 2016 12:17
> To: SQLite mailing list
> Subject: Re: [sqlite] Why takes the second SELECT three times as much
> time?
>
>
> > On Oct 16, 2016, at 4:49 AM, Luuk <[hidden email]> wrote:
> >
> > Because your second query has to build the complete view before it can
> decide if a result is between the selected dates?
>
> I didn’t think a view had a physical manifestation that had to be built; I
> thought it was just a shortcut/macro for a nested SELECT statement.
> Or is the query optimizer not able to convert the nested SELECT into the
> same form as the first query?
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why takes the second SELECT three times as much time?

Cecil Westerhof-5
2016-10-16 21:05 GMT+02:00 Keith Medcalf <[hidden email]>:

>
> See https://www.sqlite.org/optoverview.html
> under section 10.0 Query Flattening
>
> Your query is:
>
> SELECT * FROM <view> WHERE <condition>
>
> which could be treated as
>
> SELECT *
>   FROM (view select statement)
>  WHERE condition
>
> and then flattened.  Note however that the query WILL NOT be flattened because of rule #2, the subselect in the FROM clause contains an aggregate ...

OK, I understand. The query is mostly run in a cron job. So I think I
go for the ‘expensive’ one, because that is more clear.


>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]]
>> On Behalf Of Jens Alfke
>> Sent: Sunday, 16 October, 2016 12:17
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Why takes the second SELECT three times as much
>> time?
>>
>>
>> > On Oct 16, 2016, at 4:49 AM, Luuk <[hidden email]> wrote:
>> >
>> > Because your second query has to build the complete view before it can
>> decide if a result is between the selected dates?
>>
>> I didn’t think a view had a physical manifestation that had to be built; I
>> thought it was just a shortcut/macro for a nested SELECT statement.
>> Or is the query optimizer not able to convert the nested SELECT into the
>> same form as the first query?
>>
>> —Jens
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users