Re: [sqlite] Slow select from a single table when using a view

[Simon Slavin]

On 11 Jun 2009, at 10:19am, Antti Nietosvaara wrote:

> On Thursday 11 June 2009 11:50:56 Simon Slavin wrote:
>> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
>>> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
>>> where (C=1 OR C=2) will reflect the permissions of the user.
>>>
>>> There is also an index for C:
>>> CREATE INDEX idx_C ON T(C);
>>>
>>> I have a problem with performance when using the view for simple
>>> selects.
>>> The following query returns the result immediately:
>>> SELECT min(C) from T;
>>>
>>> However the same query on the view takes a very long time:
>>> SELECT min(C) from T_view;
>>
>> You didn't create an index on T_view ?
>
> No, it seems you cannot create an index on views. Trying to do so  
> resulted:
> "SQL error: views may not be indexed"

I apologise: of course you can't create an index on views.  You're  
quite right.  I was thinking you were reading from a temporary table,  
not a temporary view.

So we now wonder if a view is really necessary.  You can create a  
temporary table and read the records from the view into that.  Or even  
ignore that stage entirely and include your restriction on 'C' in your  
SELECT command.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Jim Wilcoxson]

If you only have a handful of values for C and are already going to
the trouble of creating separate views for each C, you could partition
your data into separate tables for each value of C and maybe create
another table containing the list of values of C and maybe the number
of items in each C table (if that would be useful), or other
attributes common to each value of C.

Whether that makes sense depends on what else you're doing with the
views.  You might have to repeat queries across a set of tables and
then do some work in your application to collate the results.  That
could be a big deal or not, depending on what you're doing.

For this min example, you'd only have to query the master table.

Jim

On 6/11/09, Antti Nietosvaara  wrote:
> On Thursday 11 June 2009 16:30:12 Igor Tandetnik wrote:
>> > Ah, this would indeed explain the slowdown. I was hoping views would
>> > translate into the "where" part of the query, like:
>> > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2);
>>
>> I predict this last query wouldn't run any faster. Try it.
>
> You are correct. It is way too slow.
>
> --
> Antti Nietosvaara
> Turun Turvatekniikka Oy
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Antti Nietosvaara]

On Thursday 11 June 2009 16:30:12 Igor Tandetnik wrote:
> > Ah, this would indeed explain the slowdown. I was hoping views would
> > translate into the "where" part of the query, like:
> > SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2);
>
> I predict this last query wouldn't run any faster. Try it.

You are correct. It is way too slow.

-- 
Antti Nietosvaara
Turun Turvatekniikka Oy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Igor Tandetnik]

Antti Nietosvaara wrote:
> On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote:
>> Because this query is no longer a simple select. It is translated
>> internally into
>>
>> select min(C) from
>> (SELECT * FROM T WHERE (C=1 OR C=2));
>
> Ah, this would indeed explain the slowdown. I was hoping views would
> translate into the "where" part of the query, like:
> SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2);

I predict this last query wouldn't run any faster. Try it.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Antti Nietosvaara]

On Thursday 11 June 2009 14:54:04 Igor Tandetnik wrote:
> Because this query is no longer a simple select. It is translated
> internally into
>
> select min(C) from
> (SELECT * FROM T WHERE (C=1 OR C=2));

Ah, this would indeed explain the slowdown. I was hoping views would translate 
into the "where" part of the query, like:
SELECT min(C) FROM T_view; -> SELECT min(C) FROM T WHERE (C=1 OR C=2);

I may need to find out another way to filter the rows from users.

-- 
Antti Nietosvaara
Turun Turvatekniikka Oy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Igor Tandetnik]

Antti Nietosvaara wrote:
> I have a table T with a few million rows. It has a column C with only
> a handful of distinct values for grouping the data. When a user wants
> to access the data my application reads it from a temporary view:
> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
> where (C=1 OR C=2) will reflect the permissions of the user.
>
> There is also an index for C:
> CREATE INDEX idx_C ON T(C);

You may find that an index on a column with only a few distinct values 
actually hurts performance. Something as simple as

select * from T where C=1;

may take longer with an index. You see, it takes O(N) time to run this 
query without an index, and O(M (logN)^2) time to run it with index, 
where N is the total number of records in the table, and M is the number 
of records satisfying the condition. So the index has a clear benefit 
when M is much smaller than N, but is obviously detrimental when M is 
close to N. The break-even point occurs approximately at M equal to 10% 
of N.

> I have a problem with performance when using the view for simple
> selects. The following query returns the result immediately:
> SELECT min(C) from T;
>
> However the same query on the view takes a very long time:
> SELECT min(C) from T_view;

Because this query is no longer a simple select. It is translated 
internally into

select min(C) from
(SELECT * FROM T WHERE (C=1 OR C=2));

Index is used to satisfy the WHERE condition - but that's still a 
substantial portion of all records in T, which then have to be scanned 
linearly.

> Is there a way to speed up the queries

Well, if you are talking of this particular query, you can do something 
like this:

select (case when exists (select 1 from T where C=1) then 1
when exists (select 1 from T where C=2) then 2
else null end);

This should run fast. However, I kind of doubt that "SELECT min(C) from 
T_view;" is a typical query in your application.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Pavel Ivanov]

I think you should try to rewrite condition to exclude OR like this:
WHERE C >= 1 AND C <= 2. You can even do like this: WHERE C >= 1 AND C
<= 3 AND (C = 1 OR C = 3). I think it will be faster than just ORed
conditions alone.

Pavel

On Thu, Jun 11, 2009 at 5:19 AM, Antti Nietosvaara wrote:
> On Thursday 11 June 2009 11:50:56 Simon Slavin wrote:
>> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
>> > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
>> > where (C=1 OR C=2) will reflect the permissions of the user.
>> >
>> > There is also an index for C:
>> > CREATE INDEX idx_C ON T(C);
>> >
>> > I have a problem with performance when using the view for simple
>> > selects.
>> > The following query returns the result immediately:
>> > SELECT min(C) from T;
>> >
>> > However the same query on the view takes a very long time:
>> > SELECT min(C) from T_view;
>>
>> You didn't create an index on T_view ?
>
> No, it seems you cannot create an index on views. Trying to do so resulted:
> "SQL error: views may not be indexed"
>
> I just noticed that if there I use only one C filter, for example:
> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1;
> the queries will be just as fast as with using T directly. Also, explain query
> plan returns the string ending with ORDER BY, just like it does when selecting
> from T.
> With this single filter my query took about 8 ms. With two filters (C=1 OR 
> C=2)
> the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds.
>
> --
> Antti Nietosvaara
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Antti Nietosvaara]

On Thursday 11 June 2009 11:50:56 Simon Slavin wrote:
> On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:
> > CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
> > where (C=1 OR C=2) will reflect the permissions of the user.
> >
> > There is also an index for C:
> > CREATE INDEX idx_C ON T(C);
> >
> > I have a problem with performance when using the view for simple
> > selects.
> > The following query returns the result immediately:
> > SELECT min(C) from T;
> >
> > However the same query on the view takes a very long time:
> > SELECT min(C) from T_view;
>
> You didn't create an index on T_view ?

No, it seems you cannot create an index on views. Trying to do so resulted: 
"SQL error: views may not be indexed"

I just noticed that if there I use only one C filter, for example:
CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE C=1;
the queries will be just as fast as with using T directly. Also, explain query 
plan returns the string ending with ORDER BY, just like it does when selecting 
from T.
With this single filter my query took about 8 ms. With two filters (C=1 OR C=2) 
the time went to over 6 seconds. With (C=1 OR C=2 OR C=3) about 13 seconds.

-- 
Antti Nietosvaara

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow select from a single table when using a view

[Simon Slavin]

On 11 Jun 2009, at 8:24am, Antti Nietosvaara wrote:

> CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
> where (C=1 OR C=2) will reflect the permissions of the user.
>
> There is also an index for C:
> CREATE INDEX idx_C ON T(C);
>
> I have a problem with performance when using the view for simple  
> selects.
> The following query returns the result immediately:
> SELECT min(C) from T;
>
> However the same query on the view takes a very long time:
> SELECT min(C) from T_view;

You didn't create an index on T_view ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slow select from a single table when using a view

[Antti Nietosvaara]

Hi all,

I have a table T with a few million rows. It has a column C with only a 
handful of distinct values for grouping the data. When a user wants to access 
the data my application reads it from a temporary view:
CREATE TEMPORARY VIEW T_view AS SELECT * FROM T WHERE (C=1 OR C=2);
where (C=1 OR C=2) will reflect the permissions of the user.

There is also an index for C:
CREATE INDEX idx_C ON T(C);

I have a problem with performance when using the view for simple selects.
The following query returns the result immediately:
SELECT min(C) from T;

However the same query on the view takes a very long time:
SELECT min(C) from T_view;


It would seem that the index is used but there are some differences:

EXPLAIN QUERY PLAN SELECT min(C) from T;
returns
0|0|TABLE T WITH INDEX idx_C ORDER BY

but query
EXPLAIN QUERY PLAN SELECT min(c) from T_view;
returns 
0|0|TABLE T WITH INDEX idx_C


Is there a way to speed up the queries or maybe an alternative method of 
filtering results based on C?

-- 
Antti Nietosvaara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users