Re: [sqlite] Multiprocess accessing SQLite connection

[Vijay Khurdiya]

On 6/17/2013 9:57 AM, Vijay Khurdiya wrote:
> Do we can use attach  & deattach option to read the other DB file data from 
> other process.?

Yes you can, but you'd have exactly the same concurrency-related
restrictions as when you open multiple connections to the same file
directly. Connecting via ATTACH doesn't magically eliminate those issues
- you still can't have two processes write to the same file simultaneously.

> Thanks..

>  What is tradeoff (Memory, Speed, Concurrency..) by creating multiple DB file 
> to one single file. (Definitely logically grouping of data will be taken care 
> before creating tables under DB file)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This e-mail and any files transmitted with it are for the sole use of the 
intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient, please contact the sender by reply 
e-mail and destroy all copies and the original message. Any unauthorized 
review, use, disclosure, dissemination, forwarding, printing or copying of this 
email or any action taken in reliance on this e-mail is strictly prohibited and 
may be unlawful. The recipient acknowledges that Secure Meters Limited or its 
subsidiaries and associated companies(collectively "Secure Meters Limited"),are 
unable to exercise control or ensure or guarantee the integrity of/over the 
contents of the information contained in e-mail transmissions and further 
acknowledges that any views expressed in this message are those of the 
individual sender and no binding nature of the message shall be implied or 
assumed unless the sender does so expressly with due authority of Secure Meters 
Limi
 ted. Before opening any attachments please check them for viruses and defects. 
In case you have any problem or issue with the E-mails from Secure Meters 
Limited Please do lets us know on netad...@securetogether.com Secure Meters 
Limited- Udaipur- Rajasthan -313001
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: add support for COMMENT statement

[Petite Abeille]

On Jun 17, 2013, at 6:14 PM, Roman Fleysher  
wrote:

> Dear SQLiters,

First thing first… don't hijack a thread… instead start a new one, with  a new 
subject.

> Can someone recommend an ORM?

No.

> What are the pros and cons of using them?

http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx

> If this list is inappropriate for such discussion, please also let me know 
> and I will refrain.

Inappropriate forum. Try perhaps your favorite programming language discussion 
list and take it from there.

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

Re: [sqlite] Multiprocess accessing SQLite connection

[Igor Tandetnik]

On 6/17/2013 9:57 AM, Vijay Khurdiya wrote:

Do we can use attach  & deattach option to read the other DB file data from 
other process.?


Yes you can, but you'd have exactly the same concurrency-related 
restrictions as when you open multiple connections to the same file 
directly. Connecting via ATTACH doesn't magically eliminate those issues 
- you still can't have two processes write to the same file simultaneously.

--
Igor Tandetnik

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

Re: [sqlite] GROUP BY syntax

[Dave Wellman]

", and also the value of field2 that corresponds to the maximum field3"
<<< now that is useful.

Many thanks.
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: 17 June 2013 17:08
To: General Discussion of SQLite Database
Subject: Re: [sqlite] GROUP BY syntax

On Mon, Jun 17, 2013 at 12:03 PM, Dave Wellman
wrote:

> Hi,
>
> Igor and Richard - thanks for your answers.
>
> Following up on the example below from Igor, what is the use case ?
>

SELECT field1, field2, max(field3) FROM table GROUP BY field1;

The above returns the maximum field3 for each distinct field1, and also the
value of field2 that corresponds to the maximum field3.

Another case:  SELECT field1, field2, field3 FROM table GROUP BY field1;

The above is the same as SELECT DISTINCT ON (field1), field1, field2,
field3 FROM table;  But SQLite does not support the DISTINCT ON syntax.



>
> select field1, field2, sum(field3) group by field1;
>
> If the answer set contains one row per field1 value and an arbitrary 
> value for field2 - what does this answer provide?
>

That doesn't really provide you with anything useful, does it?



> - especially compared to  (say): select field1, sum(field3) group by 
> field1;
>
> Cheers,
> Dave
>
>
> Ward Analytics Ltd - information in motion
> Tel: +44 (0) 118 9740191
> Fax: +44 (0) 118 9740192
> www: http://www.ward-analytics.com
>
> Registered office address: The Oriel, Sydenham Road, Guildford, 
> Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 
> Registered in England and Wales.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: 17 June 2013 14:01
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] GROUP BY syntax
>
> On 6/17/2013 8:36 AM, Dave Wellman wrote:
> > So I think that what this is saying is that when you execute an 
> > aggregate query without a GROUP BY, the chosen non-aggregate values 
> > are
> random (i.e.
> > arbitrary).
>
> This is true with GROUP BY as well - consider:
>
> select field1, field2, sum(field3) group by field1;
>
> > Is the above syntax standard ANSI SQL?
>
> No, it's an extension implemented by SQLite. Most other DBMS produce 
> an error for such statements.
> --
> 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
>



--
D. Richard Hipp
d...@sqlite.org
___
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] Feature request: add support for COMMENT statement

[Roman Fleysher]

Dear SQLiters,

I can not add solutions, since I am a physicist designing database for the 
first time, but I would like to add questions...

Object-relational mapping (ORM) is a new and interesting concept for me that I 
learned. I will read about it more. However, I do not understand why new 
functionality of SQLite is needed. Why can't the mapper use a special table of 
three columns (I will use example from Alexey):

tableName | columnName  |  Rule

user   |  username  | TITLE
user   | login   |   KEY
user   |  roles  |  LIST:role


(This table describes all classes, "user" being one of them. Perhaps another 
table is needed to keep track of instances of the classes.)

My question is (an it intersects with another topic discussed recently: 
security) how safe it is to store SQL instructions to be executed in such 
tables, say in the rule column? Name of some scripts to be executed? Class  is 
a collection of data and methods to operate them. Database is clearly designed 
to store data, and relations. What about operations? Is it, in general, a good 
idea to store SQL statements and script names to be called even if security is 
not an issue?

Can someone recommend an ORM? What are the pros and cons of using them? If this 
list is inappropriate for such discussion, please also let me know and I will 
refrain.


Thank you,

Roman


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Alexey Pechnikov [pechni...@mobigroup.ru]
Sent: Friday, June 14, 2013 12:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Feature request: add support for COMMENT statement

Hello!

> Suppose you have it. What would you do with it? > What's the use case?

> --> Igor Tandetnik


The COMMENTs can be used to store any information for interchanging
with application and external systems. As example, ORM (object
relational mappers) may need some additional metainfo for all mapped
columns:  


My web-apps use metainfo like to:


user
{
  username TITLE
  loginKEY
  password PASSWORD
  rolesLIST:role
  note HIDDEN
  modified TIMESTAMP
  author   ID:user
  isactive ACTUAL
}


Where

user - table name

username, login,... - table fields

TITLE, KEY, PASSWORD - definition for rules to process tables fields
in web application and in console (import/export utils). The database
schema (including tables, indicies, FTS indicies tables, etc.) is
generated by this metainfo too. So I have the high-level domain
specific data definition language and low-level application file
format as SQLite database. Of course, it's usefull to have auto
generated schema with records versioning for all tables, fast search
for all key/title fields using FTS extension (my patches add snowball
tokenizers support), fast search for lists of identifiers (using FTS
extension too), import/export utilities, JSON routes, etc.

Unfortunately, now we need additional external [plain-text] file with
metainfo or additional table with non-trivial and non-standard mapping
between database objects and own metainfo table records.



> SQLite saves comments in table/view/index/trigger definitions:

>

> sqlite> create table t(x /* :-) */); > sqlite> .schema > CREATE TABLE t(x
/* :-) */);

>

> Regards, > Clemens


Bad idea. The schema definition can't be modified!

--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
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] GROUP BY syntax

[Richard Hipp]

On Mon, Jun 17, 2013 at 12:03 PM, Dave Wellman
wrote:

> Hi,
>
> Igor and Richard - thanks for your answers.
>
> Following up on the example below from Igor, what is the use case ?
>

SELECT field1, field2, max(field3) FROM table GROUP BY field1;

The above returns the maximum field3 for each distinct field1, and also the
value of field2 that corresponds to the maximum field3.

Another case:  SELECT field1, field2, field3 FROM table GROUP BY field1;

The above is the same as SELECT DISTINCT ON (field1), field1, field2,
field3 FROM table;  But SQLite does not support the DISTINCT ON syntax.



>
> select field1, field2, sum(field3) group by field1;
>
> If the answer set contains one row per field1 value and an arbitrary value
> for field2 - what does this answer provide?
>

That doesn't really provide you with anything useful, does it?



> - especially compared to  (say): select field1, sum(field3) group by
> field1;
>
> Cheers,
> Dave
>
>
> Ward Analytics Ltd - information in motion
> Tel: +44 (0) 118 9740191
> Fax: +44 (0) 118 9740192
> www: http://www.ward-analytics.com
>
> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
> United Kingdom, GU1 3SR
> Registered company number: 3917021 Registered in England and Wales.
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: 17 June 2013 14:01
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] GROUP BY syntax
>
> On 6/17/2013 8:36 AM, Dave Wellman wrote:
> > So I think that what this is saying is that when you execute an
> > aggregate query without a GROUP BY, the chosen non-aggregate values are
> random (i.e.
> > arbitrary).
>
> This is true with GROUP BY as well - consider:
>
> select field1, field2, sum(field3) group by field1;
>
> > Is the above syntax standard ANSI SQL?
>
> No, it's an extension implemented by SQLite. Most other DBMS produce an
> error for such statements.
> --
> 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
>



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

Re: [sqlite] GROUP BY syntax

[Dave Wellman]

Hi,

Igor and Richard - thanks for your answers.

Following up on the example below from Igor, what is the use case ?

select field1, field2, sum(field3) group by field1;

If the answer set contains one row per field1 value and an arbitrary value
for field2 - what does this answer provide? 
- especially compared to  (say): select field1, sum(field3) group by field1;

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: 17 June 2013 14:01
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] GROUP BY syntax

On 6/17/2013 8:36 AM, Dave Wellman wrote:
> So I think that what this is saying is that when you execute an 
> aggregate query without a GROUP BY, the chosen non-aggregate values are
random (i.e.
> arbitrary).

This is true with GROUP BY as well - consider:

select field1, field2, sum(field3) group by field1;

> Is the above syntax standard ANSI SQL?

No, it's an extension implemented by SQLite. Most other DBMS produce an
error for such statements.
--
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] Multiprocess accessing SQLite connection

[Vijay Khurdiya]

Yes... but it should be clear that each process will have
only access to the data it has written and it won't have
Access to the data written by other processes.

> I am not sure writing from other process is possible, provided other process 
> opening the connection to that DB file.

> Do we can use attach  & deattach option to read the other DB file data from 
> other process.?

On Mon, Jun 17, 2013 at 3:08 PM, Igor Tandetnik  wrote:
> On 6/17/2013 1:01 AM, Vijay Khurdiya wrote:
>>
>> In that case can I have separate DB file associated with each process.
>
>
> Of course. Just pass different file names to sqlite3_open or similar.
> --

This e-mail and any files transmitted with it are for the sole use of the 
intended recipient(s) and may contain confidential and privileged information. 
If you are not the intended recipient, please contact the sender by reply 
e-mail and destroy all copies and the original message. Any unauthorized 
review, use, disclosure, dissemination, forwarding, printing or copying of this 
email or any action taken in reliance on this e-mail is strictly prohibited and 
may be unlawful. The recipient acknowledges that Secure Meters Limited or its 
subsidiaries and associated companies(collectively "Secure Meters Limited"),are 
unable to exercise control or ensure or guarantee the integrity of/over the 
contents of the information contained in e-mail transmissions and further 
acknowledges that any views expressed in this message are those of the 
individual sender and no binding nature of the message shall be implied or 
assumed unless the sender does so expressly with due authority of Secure Meters 
Limi
 ted. Before opening any attachments please check them for viruses and defects. 
In case you have any problem or issue with the E-mails from Secure Meters 
Limited Please do lets us know on netad...@securetogether.com Secure Meters 
Limited- Udaipur- Rajasthan -313001
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiprocess accessing SQLite connection

[Paolo Bolzoni]

Yes... but it should be clear that each process will have
only access to the data it has written and it won't have
access to the data written by other processes.

On Mon, Jun 17, 2013 at 3:08 PM, Igor Tandetnik  wrote:
> On 6/17/2013 1:01 AM, Vijay Khurdiya wrote:
>>
>> In that case can I have separate DB file associated with each process.
>
>
> Of course. Just pass different file names to sqlite3_open or similar.
> --
> 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] Simple Group By slowing queries by 6x

[Keith Medcalf]

create index ddate_hits_a_idadvertiser_site_hostname_bench on 
a_idadvertiser_site_hostname_bench (ddate, hits);

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Iván de Prado
> Sent: Monday, 17 June, 2013 05:30
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Simple Group By slowing queries by 6x
> 
> I have query where adding a simple "group by" by date is slowing the query
> too much, from my point of view.
> 
> The following query:
> 
> SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where
> ddate
> < '2013-08-01';
> 
> runs in 12 seconds. Approximately 423,327 rows scanned per second. 1 row
> returned. The query plan is:
> 
> SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
> 
> But adding the clause "group by ddate" slows too much the query:
> 
> SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where
> ddate
> < '2013-08-01' group by ddate;
> 
> runs in 67 seconds. Approximately 75,819 rows scanned per second. 63 rows
> returned.
> 
> The query plan is now:
> 
> SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
> USE TEMP B-TREE FOR GROUP BY
> 
> That means that this query is running almost 6 times slower than the one
> without the group by. How could that be? The number of group by buckets is
> very small (just 63 different dates), so it would be enough to do a scan
> and keep a hash table in memory with partial results.
> 
> Any ideas about what's the problem and how could we solve it?
> 
> The Sqlite version we are using is 3.7.14.1.
> 
> Regards!
> ___
> 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] Multiprocess accessing SQLite connection

[Igor Tandetnik]

On 6/17/2013 1:01 AM, Vijay Khurdiya wrote:

In that case can I have separate DB file associated with each process.


Of course. Just pass different file names to sqlite3_open or similar.
--
Igor Tandetnik

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

Re: [sqlite] GROUP BY syntax

[Igor Tandetnik]

On 6/17/2013 8:36 AM, Dave Wellman wrote:

So I think that what this is saying is that when you execute an aggregate
query without a GROUP BY, the chosen non-aggregate values are random (i.e.
arbitrary).


This is true with GROUP BY as well - consider:

select field1, field2, sum(field3) group by field1;


Is the above syntax standard ANSI SQL?


No, it's an extension implemented by SQLite. Most other DBMS produce an 
error for such statements.

--
Igor Tandetnik

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

Re: [sqlite] GROUP BY syntax

[Richard Hipp]

On Mon, Jun 17, 2013 at 8:36 AM, Dave Wellman
wrote:

>
>
> So I think that what this is saying is that when you execute an aggregate
> query without a GROUP BY, the chosen non-aggregate values are random (i.e.
> arbitrary).
>
>
If there is exactly one aggregate function which is either MIN() or MAX(),
then the non-aggregated column values correspond to the row that contained
the minimum or maximum value.  If two or more rows contain the same minimum
or maximum, then the specific one chosen is arbitrary.  If there are two or
more aggregate functions or the aggregate function is not MIN() or MAX()
then the non-aggregate columns are selected arbitrarily.



>
>
> Is the above syntax standard ANSI SQL?
>
>
No.  Most other SQL database engines call this a syntax error.


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

[sqlite] GROUP BY syntax

[Dave Wellman]

Hi,

 

The following sql was in a recent post which was complaining about
performance and it looks like a solution has been provided for that.

 

However, looking at the original SQL I would have expected an error message
to be generated for it because there is no "GROUP BY" clause.

 

SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate
< '2013-08-01';

 

I then looked at the syntax documentation and found the following section,
which I think explains the lack of error message and the reason for this
result (and my results on testing something similar).

If the SELECT statement is an aggregate query without a GROUP BY clause,
then each aggregate expression in the result-set is evaluated once across
the entire dataset. Each non-aggregate expression in the result-set is
evaluated once for an arbitrarily selected row of the dataset. The same
arbitrarily selected row is used for each non-aggregate expression.

 

So I think that what this is saying is that when you execute an aggregate
query without a GROUP BY, the chosen non-aggregate values are random (i.e.
arbitrary).

 

Is the above syntax standard ANSI SQL?

 

Certainly on a Teradata system the above syntax would fail.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

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

Re: [sqlite] Simple Group By slowing queries by 6x

[Clemens Ladisch]

Iván de Prado wrote:
> SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate < 
> '2013-08-01' group by ddate;
>
> SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
> USE TEMP B-TREE FOR GROUP BY
>
> [...] means that this query is running almost 6 times slower than the one
> without the group by. How could that be?
>
> Any ideas about what's the problem

SQLite sorts the entire table before aggregating it.

> how could we solve it?

Create an index on the ddate column.


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

Re: [sqlite] Simple Group By slowing queries by 6x

[Iván de Prado]

Seems that using pragma temp_store=2 improves the speed. It now tooks 20
seconds. So now the penalty time is 9 seconds. It would possible to improve
it?.

Regards.
Iván


2013/6/17 Iván de Prado 

> I have query where adding a simple "group by" by date is slowing the query
> too much, from my point of view.
>
> The following query:
>
> SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where
> ddate < '2013-08-01';
>
> runs in 12 seconds. Approximately 423,327 rows scanned per second. 1 row
> returned. The query plan is:
>
> SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
>
> But adding the clause "group by ddate" slows too much the query:
>
> SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where
> ddate < '2013-08-01' group by ddate;
>
> runs in 67 seconds. Approximately 75,819 rows scanned per second. 63 rows
> returned.
>
> The query plan is now:
>
> SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
> USE TEMP B-TREE FOR GROUP BY
>
> That means that this query is running almost 6 times slower than the one
> without the group by. How could that be? The number of group by buckets is
> very small (just 63 different dates), so it would be enough to do a scan
> and keep a hash table in memory with partial results.
>
> Any ideas about what's the problem and how could we solve it?
>
> The Sqlite version we are using is 3.7.14.1.
>
> Regards!
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simple Group By slowing queries by 6x

[Iván de Prado]

I have query where adding a simple "group by" by date is slowing the query
too much, from my point of view.

The following query:

SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate
< '2013-08-01';

runs in 12 seconds. Approximately 423,327 rows scanned per second. 1 row
returned. The query plan is:

SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)

But adding the clause "group by ddate" slows too much the query:

SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where ddate
< '2013-08-01' group by ddate;

runs in 67 seconds. Approximately 75,819 rows scanned per second. 63 rows
returned.

The query plan is now:

SCAN TABLE a_idadvertiser_site_hostname_bench (~33 rows)
USE TEMP B-TREE FOR GROUP BY

That means that this query is running almost 6 times slower than the one
without the group by. How could that be? The number of group by buckets is
very small (just 63 different dates), so it would be enough to do a scan
and keep a hash table in memory with partial results.

Any ideas about what's the problem and how could we solve it?

The Sqlite version we are using is 3.7.14.1.

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

[sqlite] optimization for outer join with most simple views

[E.Pasma]

Hello,

Suppose one has an expression on the columns of a single table, say x 
+y, and that this expression occurs in multiple queries. Then it is  
attractive to define it at a single place, using a view:


create view v as select *, x+y as a from t;

I had hoped that substituting such a view in queries instead of the  
original table would make performance-wise no difference. This hope is  
mostly fulfilled but not completely. If the view appears after an  
OUTER JOIN, there is a difference in the query execution plan. The  
output is printed below. With the outer join the view is evaluated  
into a transient table instead of the underlying table being searched  
directly. This can cause a significant slow down.


I understand that a view is treated by the optimizer as a subquery and  
that the different execution plan is a matter of Subquery flattening:


http://www.sqlite.org/optoverview.html#flattening

Apparently the optimizer can not produse a flat query here and that is  
exactly explained in condition 3:


3. The subquery is not the right operand of a left outer join.

As a casual reader I would think this only needs to apply if the  
subquery is not a join. But grasping the complexity of query  
optimization, there are likely many other bewares, like aggregation,  
union, sub-subqueries, ordering, limits and where clauses. In my case  
only the most simple subquery, like the example view, need to be delt  
with. Is there any chance that just this is recognized by the optimizer?


Desired change in terms of the list of conditions:
3. The subquery is not the right operand of a left outer join
   or the subquery:
   - is not a join
   - does not use aggregates
   - is not DISTINCT
   - does not use LIMIT
   - does not have a WHERE clause

Does anyone else have the same wish or has this been discussed before?
I would like to know.

Thanks,

Edzard Pasma

Output:
create table t (t integer primary key, x, y);
create table t0 (t0 integer primary key, t, z);
explain query plan
select t.x+t.y from t0 left outer join t using (t) where t0.t0 =  
100;

0|0|0|SEARCH TABLE t0 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|1|SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

create view v as select *, x+y as a from t;
explain query plan
select v.a from t0 left outer join v using (t) where t0.t0 = 100;
1|0|0|SCAN TABLE t (~100 rows)
0|0|0|SEARCH TABLE t0 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|1|1|SCAN SUBQUERY 1 (~10 rows)

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

Re: [sqlite] Pager bugs(?)

[Jan Slodicka]

> statement is only doing pointer arithmetic

Apparently it is so. But it is not obvious. This code would be cleaner and
faster:

...
if( pPage ){
  pcache1PinPage(pPage);
  goto fetch_out;
}

if( createFlag==0 ){
  return NULL;
...

Have a good day.

With best regards,
   Jan Slodicka
   Resco



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

[sqlite] use sqlite to count macd value

[YAN HONG YE]

DIFF : EMA(CLOSE,SHORT) - EMA(CLOSE,LONG);
DEA  : EMA(DIFF,M);
MACD : 2*(DIFF-DEA), COLORSTICK;

I wanna use sqlite to count macd value, have any idea?
thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[HAUBOURG]

Thanks Stephan for your feedback. Bad news for me. 
I see two solutions:
- hire a dev for this. I'm not following enough sqlite lists to estimate if 
this is feasible. Any opinion?
- hire a QGIS dev to hack a autodetection of type using content of fields. 
Ugly, probably slow and prone to errors.. But feasable since I have some devs. 

Cheers, 
Régis

 
Cordialement,
Régis Haubourg

Régis Haubourg

Administrateur de données Géographiques
Département des Systèmes d'Information (DCSI)
Agence de l'eau Adour Garonne 
90 rue du Férétra,
31078 Toulouse Cedex4
Tél: 05 61 36 82 58
Mail: regis.haubo...@eau-adour-garonne.fr
 http://www.eau-adour-garonne.fr

Accédez aux données sur l'eau  :
http://adour-garonne.eaufrance.fr/ 


> -Message d'origine-
> De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] De la part de Stefan Keller
> Envoyé : samedi 15 juin 2013 23:09
> À : General Discussion of SQLite Database
> Objet : Re: [sqlite] Computed columns in VIEWs return NULL but should be
> able to be typed! Any ideas?
> 
> Hi Régis
> 
> I'd wish to give you a solution but I'm sorry to have new news about that
> issue.
> 
> I'd be happy if there are any SQlite devs around to give you a solution in
> order to make this database more usable (an more SQL compatible).
> 
> Yours, Stefan
> 
> 
> 2013/6/11 regish :
> > Hi all,
> > I'm starting to use SQLITE in GIS use cases. I'm facing this view
> > typing column issue, which prevent my favourite client from
> > interpreting correctly numeric data types.  I'm using QGIS, so I won't
> > be able to map numeric values (they fall back as text values).
> > Is there anything new since 2010?  If not, should I suggest Qgis Devs
> > to hack the field type detection for views?
> > Régis
> >
> >
> >
> > --
> > View this message in context:
> > http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-
> N
> > ULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html
> > Sent from the SQLite mailing list archive at Nabble.com.
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users