Re: [sqlite] Views and Performance

[Igor Tandetnik]

On 3/2/2012 11:38 AM, Duquette, William H (318K) wrote:

On 3/2/12 8:29 AM, "Igor Tandetnik"  wrote:

On 3/2/2012 11:29 AM, Pavel Ivanov wrote:

If I am querying data just from t1, is there a performance penalty
for using myview in the query?  Or will the query planner generate
approximately the same bytecode as it would if I'd simply queried
t1?


Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.


I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


You may know there is, but SQLite doesn't, and has to plan for the worst 
case.

--
Igor Tandetnik

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

Re: [sqlite] Views and Performance

[Duquette, William H (318K)]

On 3/2/12 8:31 AM, "Simon Davies"  wrote:


>On 2 March 2012 16:23, Duquette, William H (318K)
> wrote:
>> Howdy!
>>
>> Suppose I have two related tables, t1 and t2, and I write a view like
>>this:
>>
>>CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>>
>> If I am querying data just from t1, is there a performance penalty for
>>using myview in the query?  Or will the query planner generate
>>approximately the same bytecode as it would if I'd simply queried t1?
>>
>> --
>> Will Duquette -- william.h.duque...@jpl.nasa.gov
>
>SQLite version 3.6.11
>Enter ".help" for instructions
>sqlite> create table t1( id integer primary key, data text );
>sqlite> create table t2( id integer primary key, data text );
>sqlite>
>sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
>as d2 from t1 join t2 on t1.id=t2.id;
>sqlite>
>sqlite>
>sqlite> explain query plan select data from t1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>sqlite> explain query plan select d1 from v1 where id>10;
>0|0|TABLE t1 USING PRIMARY KEY
>1|1|TABLE t2 USING PRIMARY KEY
>sqlite>
>
>It seems not for v 3.6.11

OK, so it's going to look up the id in both tables whether it needs to or
not...because, given that it's an inner join, you don't get the record
from t1 unless there's a matching record in t2.  Got it!

Thanks!




>
>Regards,
>Simon
>___
>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] Views and Performance

[Duquette, William H (318K)]

On 3/2/12 8:29 AM, "Igor Tandetnik"  wrote:


>On 3/2/2012 11:29 AM, Pavel Ivanov wrote:
>>> If I am querying data just from t1, is there a performance penalty
>>> for using myview in the query?  Or will the query planner generate
>>> approximately the same bytecode as it would if I'd simply queried
>>> t1?
>>
>> Yes, there is performance penalty and no it can't generate the same
>> bytecode. If you ask why the answer is because result set from the
>> view can be different than from the table alone - several rows in the
>> view can contain information from the same row of t1.
>
>... while some other rows from t1 may not appear at all.

I was assuming that there's a strict many-to-one relationship between t1
and t2. (Should have said.)


>-- 
>Igor Tandetnik
>
>___
>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] Views and Performance

[Pavel Ivanov]

> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?

INNER is default.


Pavel


On Fri, Mar 2, 2012 at 11:37 AM, Rob Richardson
<rdrichard...@rad-con.com> wrote:
> What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not 
> specified?
>
> RobR
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Duquette, William H 
> (318K)
> Sent: Friday, March 02, 2012 11:23 AM
> To: Discussion of SQLite Database
> Subject: [sqlite] Views and Performance
>
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
> ___
> 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] Views and Performance

[Rob Richardson]

What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? 
 

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Duquette, William H (318K)
Sent: Friday, March 02, 2012 11:23 AM
To: Discussion of SQLite Database
Subject: [sqlite] Views and Performance

Howdy!

Suppose I have two related tables, t1 and t2, and I write a view like this:

CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);

If I am querying data just from t1, is there a performance penalty for using 
myview in the query?  Or will the query planner generate approximately the same 
bytecode as it would if I'd simply queried t1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Igor Tandetnik]

On 3/2/2012 11:29 AM, Pavel Ivanov wrote:

If I am querying data just from t1, is there a performance penalty
for using myview in the query?  Or will the query planner generate
approximately the same bytecode as it would if I'd simply queried
t1?


Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.


... while some other rows from t1 may not appear at all.
--
Igor Tandetnik

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

Re: [sqlite] Views and Performance

[Simon Davies]

On 2 March 2012 16:23, Duquette, William H (318K)
 wrote:
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov

SQLite version 3.6.11
Enter ".help" for instructions
sqlite> create table t1( id integer primary key, data text );
sqlite> create table t2( id integer primary key, data text );
sqlite>
sqlite> create view v1 as select t1.id as id, t1.data as d1, t2.data
as d2 from t1 join t2 on t1.id=t2.id;
sqlite>
sqlite>
sqlite> explain query plan select data from t1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
sqlite> explain query plan select d1 from v1 where id>10;
0|0|TABLE t1 USING PRIMARY KEY
1|1|TABLE t2 USING PRIMARY KEY
sqlite>

It seems not for v 3.6.11

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

Re: [sqlite] Views and Performance

[Pavel Ivanov]

> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?

Yes, there is performance penalty and no it can't generate the same
bytecode. If you ask why the answer is because result set from the
view can be different than from the table alone - several rows in the
view can contain information from the same row of t1.

Pavel


On Fri, Mar 2, 2012 at 11:23 AM, Duquette, William H (318K)
 wrote:
> Howdy!
>
> Suppose I have two related tables, t1 and t2, and I write a view like this:
>
>    CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);
>
> If I am querying data just from t1, is there a performance penalty for using 
> myview in the query?  Or will the query planner generate approximately the 
> same bytecode as it would if I'd simply queried t1?
>
>
> --
> Will Duquette -- william.h.duque...@jpl.nasa.gov
> Athena Development Lead -- Jet Propulsion Laboratory
> "It's amazing what you can do with the right tools."
>
> ___
> 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

[sqlite] Views and Performance

[Duquette, William H (318K)]

Howdy!

Suppose I have two related tables, t1 and t2, and I write a view like this:

CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);

If I am querying data just from t1, is there a performance penalty for using 
myview in the query?  Or will the query planner generate approximately the same 
bytecode as it would if I'd simply queried t1?


--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

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