Re: [sqlite] Help speed up query

[Keith Medcalf]

Have you tried removing all the useless brackets that Micro$oft adds to the 
query?  I don't know if the parser strips them out automagically or if they are 
used as join order hints.  You are better off letting the optimizer optimize 
unless you really really want to force a specific order.  While some SQL 
databases ignore such tomfoolery, others do not.  I do not know off-hand 
whether SQLite ignores tomfoolery or not.  I just habitually remove them.

SELECT Objects.Object_ID, 
   Object_TextTables.Lower_Limit, 
   Object_TextTables.Upper_Limit, 
   Object_TextTables.TextTable_ID, 
   DTC_Statuses.DTC_Status_ID, 
   DTC_Statuses.Env_Data, 
   DTC_Statuses.Env_Offset, 
   DTC_Statuses.DTCs_Follow, 
   DTC_Statuses.Upper_Limit, 
   DTC_Statuses.Lower_Limit, 
   DTC_Statuses.Physical_Value, 
   Object_Transactions.Element_Offset, 
   Equation_Types.Equation_Type, 
   Equations.BitMask, 
   Equations.ByteSpecific, 
   Equations.ObjectSpecific, 
   Equations.InternalEncoder, 
   Equations.Equation, 
   Objects.Object_Size, 
   Objects.Signed, 
   Objects.Visible, 
   Object_Types.Object_Type, 
   DisplayFormats.DisplayFormat
  FROM Objects INNER JOIN DTC_Statuses ON 
Objects.Object_ID=DTC_Statuses.Object_ID 
   INNER JOIN Object_TextTables ON 
Objects.Object_ID=Object_TextTables.Object_ID 
   INNER JOIN Object_Transactions ON 
Objects.Object_ID=Object_Transactions.Object_ID 
   INNER JOIN Equations ON 
Object_Transactions.Equation_ID=Equations.Equation_ID 
   INNER JOIN Equation_Types ON 
Object_Transactions.Equation_Type_ID=Equation_Types.Equation_Type_ID 
   INNER JOIN Object_Types ON 
Objects.Object_Type_ID=Object_Types.Object_Type_ID
   INNER JOIN DisplayFormats ON 
Objects.DisplayFormat_ID=DisplayFormats.DisplayFormat_ID
 WHERE Object_Types.Object_Type='DTC';

I presume that the optimizer will then actually devolve this into the 
optimizable form.  Some SQL parsers do, and some do not.  Personally, except 
for extremely trivially joins (or where JOIN syntax must be used for OUTER 
joins) I always use the less semantically loaded form of speech thus, to ensure 
that the parsing order does not influence the optimizer.  Technically, if a 
different query plan is obtained when using the M$ bracketed form, the above 
form, and the below form, the optimizer is using the brackets and JOIN syntax 
ordering to provide hints -- which may not result in the optimum query plan 
unless you know what you are doing (and what the optimizer in question will do 
when parsing the input semantics)

SELECT Objects.Object_ID, 
   Object_TextTables.Lower_Limit, 
   Object_TextTables.Upper_Limit, 
   Object_TextTables.TextTable_ID, 
   DTC_Statuses.DTC_Status_ID, 
   DTC_Statuses.Env_Data, 
   DTC_Statuses.Env_Offset, 
   DTC_Statuses.DTCs_Follow, 
   DTC_Statuses.Upper_Limit, 
   DTC_Statuses.Lower_Limit, 
   DTC_Statuses.Physical_Value, 
   Object_Transactions.Element_Offset, 
   Equation_Types.Equation_Type, 
   Equations.BitMask, 
   Equations.ByteSpecific, 
   Equations.ObjectSpecific, 
   Equations.InternalEncoder, 
   Equations.Equation, 
   Objects.Object_Size, 
   Objects.Signed, 
   Objects.Visible, 
   Object_Types.Object_Type, 
   DisplayFormats.DisplayFormat
  FROM Objects, 
   DTC_Statuses, Object_TextTables, 
   Object_Transactions, 
   Equations, 
   Equation_Types, 
   Object_Types, 
   DisplayFormats, 
 WHERE Objects.Object_ID=DTC_Statuses.Object_ID 
   AND Objects.Object_ID=Object_TextTables.Object_ID 
   AND Objects.Object_ID=Object_Transactions.Object_ID 
   AND Object_Transactions.Equation_ID=Equations.Equation_ID 
   AND Object_Transactions.Equation_Type_ID=Equation_Types.Equation_Type_ID 
   AND Objects.Object_Type_ID=Object_Types.Object_Type_ID
   AND Objects.DisplayFormat_ID=DisplayFormats.DisplayFormat_ID
   AND Object_Types.Object_Type='DTC';

And then properly handle transitive equalities so that it can generate the best 
query plan, assuming that every column mentioned as a conditional column is 
included in an appropriate index (appropriately set as unique, if it is 
unique), and that tables which require more than one column to be indexed have 
*all* the referenced columns included in the index.  If a table has two 
mentioned columns you should create both indexes (mutatis mutandis for more 
than two columns)).  Ensure unique columns are actually constrained unique in 
the table definition (or by a manually created unique index, which is the same 
thing).

Then do an analyze and look at the generated output from EXPLAIN QUERY PLAN 
which will tell you the optimum join order so you can eliminate unneeded 
indexes and extend the indexes used to be covering indexes if you really want 
the maximum performance.

Are you using a versi

Re: [sqlite] Help speed up query

[Dominique Devienne]

On Mon, Apr 15, 2013 at 11:02 PM, Simon Slavin  wrote:

> On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote:
> > My software was not locked up totally.  It did finally come back.  For
> some reason executing analyze slow other queries way down.
>
> I am surprised by anyone reporting that ANALYZE has substancially slowed
> down any operation.  Please excuse me because I have trouble understanding
> English sometimes.  Can you verify for us:
>
> A) The slow-down you're talking about.  Are you saying that other
> operations ran slowly while you were doing ANALYZE, or that the SELECTs ran
> more slowly after the ANALYZE than they did before it ?
>

Perhaps you can share the sqlite_stat1 and sqlite_stat3 tables generated by
the ANALYZE?
You could perhaps also manually decompose your slow query, adding one join
at a time.
You also never shared actual timings in SQLite or the equivalent timings in
MS ACCESS. Are we talking seconds or milliseconds?

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

[sqlite] Using from Windows8 background task

[Philipp Kursawe]

It seems when I call sqlite3_step from within a background task context it
does not execute the statement at all. Trying to step into the code it
terminates the background process host exe when setting the mutex.

This seems rather strange, since the same code works for normal Win8 apps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Beginning database question

[Carl Gross]

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects,
but I'm having trouble determining exactly how.  I'm hoping someone in this
list may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10
discrete weights,' and each height/weight combination corresponds to one of
two 'teams.'  All of this information is hardcoded into my program.  My
program asks a user to enter his own height and weight, and the program
will output the user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be
beneficial in terms of coding efficiency, processing speed, memory usage,
or any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to
tell me how to do something.  I'm looking for someone to assure me that
SQLite (rather than a simple spreadsheet) is right for me, and to hopefully
steer me towards some documentation that may be beneficial to me.

Thanks,

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

[sqlite] Minor typo in release notes

[Cedric Venet]

Hi,

In http://www.sqlite.org/changes.html , the last change is noted as 2012-04-12 
(3.7.16.2). It should probably be 2013 :)

Regards,
Cédric

Cédric VENET
Software Engineer - Schlumberger, MpTC
Email: cve...@slb.com
Tel: +33 4 30 63 85 18

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

Re: [sqlite] Beginning database question

[Michael Black]

I assume when you say "discrete" you actually mean "bracketed" as there are
lots more than 10 heights and weights.

I don't know what Excel has to do with this unless you programmed the whole
thing in Excel already?

Is speed really a concern?  This does sound like a fairly trivial
programming effort in most any language.

The database approach would be nice if you have bracketed weights as a
simple SQL query like this should work:

Create table teams(heightlow,heighthigh,weightlow,weighthigh,team);
Insert into hw values(100,110,60,62,1);  (100-110lbs 60-62", Team#1)

Select team from teams where h > = heightlow and h < heighthigh and w >=
weightlow and w < weighthigh;

Do you want a GUI around this too?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carl Gross
Sent: Tuesday, April 16, 2013 3:28 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Beginning database question

Hi All,

I'm an amateur programmer who has never worked with databases before.  I
*think* that getting started with SQLite may help with some of my projects,
but I'm having trouble determining exactly how.  I'm hoping someone in this
list may be able to point me in the right direction.

My simplified situation is this.  There are 10 discrete heights' and 10
discrete weights,' and each height/weight combination corresponds to one of
two 'teams.'  All of this information is hardcoded into my program.  My
program asks a user to enter his own height and weight, and the program
will output the user's corresponding team based on the hardcoded data.

My SQLite question is:  Can using SQLite instead of Microsoft Excel be
beneficial in terms of coding efficiency, processing speed, memory usage,
or any other aspect?  If so, how?

Sorry for the long, broad, vague question.  I'm not looking for someone to
tell me how to do something.  I'm looking for someone to assure me that
SQLite (rather than a simple spreadsheet) is right for me, and to hopefully
steer me towards some documentation that may be beneficial to me.

Thanks,

Carl
___
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] Help speed up query

[veneff]

 removing the parenthesises (what is the plural?) made no difference to the 
query plan which is:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX 
sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
0|1|1|SCAN TABLE DTC_Statuses (~100 rows)
0|2|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|3|7|SEARCH TABLE DisplayFormats USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|4|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index 
(Object_ID=?) (~10 rows)
0|5|3|SEARCH TABLE Object_Transactions USING INDEX Object_Transactions_2_index 
(Object_ID=?) (~10 rows)
0|6|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|7|5|SEARCH TABLE Equation_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

which changed after analyze to:
0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX 
sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
0|1|7|SCAN TABLE DisplayFormats (~7 rows)
0|2|5|SCAN TABLE Equation_Types (~10 rows)
0|3|3|SEARCH TABLE Object_Transactions USING AUTOMATIC COVERING INDEX 
(Equation_Type_ID=?) (~5 rows)
0|4|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|5|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|6|1|SEARCH TABLE DTC_Statuses USING INDEX DTC_Statuses_1_index (Object_ID=?) 
(~1 rows)
0|7|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index 
(Object_ID=?) (~4 rows)
which seems much better.  But the query now takes longer.

It was about 380 ms.  Now it is taking about 610 ms.
These times include the prepare and stepping through the results, but not 
retrieving any field contents.
Both the prepare and stepping takes longer after analyze is executed.

The time for the same query via DAO/MS Access takes about 150 ms.

Here is the sqlite_stat1 table after analyze:
Objects|Objects_5_index|5495 1
Objects|Objects_4_index|5495 393
Objects|Objects_3_index|5495 1374
Objects|Objects_2_index|5495 5495
Objects|Objects_1_index|5495 41
Objects|sqlite_autoindex_Objects_1|5495 1
Dimensions||1
Header_Stream_Values|Header_Stream_Values_2_index|3 1
Header_Stream_Values|Header_Stream_Values_1_index|3 3
Category_Types||8
Object_TextTables|Object_TextTables_2_index|15718 1965
Object_TextTables|Object_TextTables_1_index|15718 4
Addresses||256
Object_Types|sqlite_autoindex_Object_Types_1|5 1
SwitchKeys|SwitchKeys_1_index|1 1
Equation_Types||10
Graph_Types||4
Equations||43
Header||1
Object_Equations|Object_Equations_3_index|2 2
Object_Equations|Object_Equations_2_index|2 1
Object_Equations|Object_Equations_1_index|2 2
Nodes|Nodes_2_index|1 1
Node_Headers|Node_Headers_2_index|4 4
Node_Headers|Node_Headers_1_index|4 1
DTC_Statuses|DTC_Statuses_1_index|5234 1
Header_Stream|sqlite_autoindex_Header_Stream_1|3 1
TextTables|TextTables_1_index|21 1
Byte_Types||4
Transaction_Types|sqlite_autoindex_Transaction_Types_1|8 1
Request_Parameters||5
Limits||14
Object_Categories|Object_Categories_2_index|1 1
Object_Categories|Object_Categories_1_index|1 1
DisplayFormats||7
Message_Filters||5
Object_Transactions|Object_Transactions_3_index|5747 40
Object_Transactions|Object_Transactions_2_index|5747 2
Object_Transactions|Object_Transactions_1_index|5747 160
Units|sqlite_autoindex_Units_1|45 1
SConfig|sqlite_autoindex_SConfig_1|2 1
Service_Types|sqlite_autoindex_Service_Types_1|5 1
Config||1
Services|Services_3_index|12 6
Services|Services_2_index|12 2
Services|Services_1_index|12 12
Services|sqlite_autoindex_Services_1|12 1
Categories|Categories_3_index|2 2
Categories|Categories_2_index|2 1
Strings|sqlite_autoindex_Strings_1|5465 1
Transactions|Transactions_3_index|268 54
Transactions|Transactions_2_index|268 39
Transactions|Transactions_1_index|268 2
Transactions|sqlite_autoindex_Transactions_1|268 1

I'm not sure how breaking down the joins would give us good info since I'm not 
likely to do it in the same order as SQLite would.

Vance




on Apr 16, 2013, Keith Medcalf  wrote:
>
>
>Have you tried removing all the useless brackets that Micro$oft adds to the 
>query?
> I don't know if the parser strips them out automagically or if they are used 
> as
>join order hints.  You are better off letting the optimizer optimize unless 
>you really
>really want to force a specific order.  While some SQL databases ignore such 
>tomfoolery,
>others do not.  I do not know off-hand whether SQLite ignores tomfoolery or 
>not.
> I just habitually remove them.
>
>SELECT Objects.Object_ID, 
>   Object_TextTables.Lower_Limit, 
>   Object_TextTables.Upper_Limit, 
>   Object_TextTables.TextTable_ID, 
>   DTC_Statuses.DTC_Status_ID, 
>   DTC_Statuses.Env_Data, 
>   DTC_Statuses.Env_Offset, 
>   DTC_Statuses.DTCs_Follow, 
>   DTC_Statuses.Upper_Limit, 
>   DTC_Statuses.Lower_Limit, 
>   DTC_Statuses.Physical_Value, 
>   Object_Transactions.Element_Offset, 
>   Equation_Types.Equation_Type, 
>   Equations.BitMask, 
>   Equations.ByteSpecific, 
>   Equations.Obje

Re: [sqlite] Help speed up query

[Simon Slavin]

On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:

> It was about 380 ms.  Now it is taking about 610 ms.

Can I check with you that the database file is stored on a disk of the computer 
which is executing the SQLite commands ?  In other words, that this is a local 
hard disk and not one shared over a network.

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

Re: [sqlite] Help speed up query

[Keith Medcalf]

Interesting ...

The query is simply descending a tree and I should think it would be fast.

Object_Types
 Objects
  DTC_Statuses
  Object_TextTables
  DisplayFormats
  Object_Transactions
   Equations
   Equation_Types

Which would need the following indexes for traversal.  For column retrieval, 
you can cover the data items required by appending them to the end of each 
index if required to completely remove the necessity of accessing the base 
table b-tree ...

Object_Types(Object_Type, Object_Type_ID)
Objects(Object_Type_ID, Object_ID, DisplayFormat_ID)
DTC_Statuses(Object_ID)
Object_TextTables(Object_ID)
DisplayFormats(DisplayFormat_ID)
Object_Transactions(Object_ID, Equation_ID, Equation_Type_ID)
Equations(Equation_ID)
Equation_Types(Equation_Type_ID)

It should be fast and the optimizer has little choice on how to perform the 
query because you only have a single non-join constraint and it would be placed 
at the top of the tree (in the outermost loop).

---
()  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 ven...@intouchmi.com
> Sent: Tuesday, 16 April, 2013 07:33
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Help speed up query
> 
> 
>  removing the parenthesises (what is the plural?) made no difference to
> the query plan which is:
> 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
> sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
> 0|1|1|SCAN TABLE DTC_Statuses (~100 rows)
> 0|2|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0|3|7|SEARCH TABLE DisplayFormats USING INTEGER PRIMARY KEY (rowid=?) (~1
> rows)
> 0|4|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index
> (Object_ID=?) (~10 rows)
> 0|5|3|SEARCH TABLE Object_Transactions USING INDEX
> Object_Transactions_2_index (Object_ID=?) (~10 rows)
> 0|6|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0|7|5|SEARCH TABLE Equation_Types USING INTEGER PRIMARY KEY (rowid=?) (~1
> rows)
> 
> which changed after analyze to:
> 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX
> sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1 rows)
> 0|1|7|SCAN TABLE DisplayFormats (~7 rows)
> 0|2|5|SCAN TABLE Equation_Types (~10 rows)
> 0|3|3|SEARCH TABLE Object_Transactions USING AUTOMATIC COVERING INDEX
> (Equation_Type_ID=?) (~5 rows)
> 0|4|4|SEARCH TABLE Equations USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0|5|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0|6|1|SEARCH TABLE DTC_Statuses USING INDEX DTC_Statuses_1_index
> (Object_ID=?) (~1 rows)
> 0|7|2|SEARCH TABLE Object_TextTables USING INDEX Object_TextTables_1_index
> (Object_ID=?) (~4 rows)
> which seems much better.  But the query now takes longer.
> 
> It was about 380 ms.  Now it is taking about 610 ms.
> These times include the prepare and stepping through the results, but not
> retrieving any field contents.
> Both the prepare and stepping takes longer after analyze is executed.
> 
> The time for the same query via DAO/MS Access takes about 150 ms.
> 
> Here is the sqlite_stat1 table after analyze:
> Objects|Objects_5_index|5495 1
> Objects|Objects_4_index|5495 393
> Objects|Objects_3_index|5495 1374
> Objects|Objects_2_index|5495 5495
> Objects|Objects_1_index|5495 41
> Objects|sqlite_autoindex_Objects_1|5495 1
> Dimensions||1
> Header_Stream_Values|Header_Stream_Values_2_index|3 1
> Header_Stream_Values|Header_Stream_Values_1_index|3 3
> Category_Types||8
> Object_TextTables|Object_TextTables_2_index|15718 1965
> Object_TextTables|Object_TextTables_1_index|15718 4
> Addresses||256
> Object_Types|sqlite_autoindex_Object_Types_1|5 1
> SwitchKeys|SwitchKeys_1_index|1 1
> Equation_Types||10
> Graph_Types||4
> Equations||43
> Header||1
> Object_Equations|Object_Equations_3_index|2 2
> Object_Equations|Object_Equations_2_index|2 1
> Object_Equations|Object_Equations_1_index|2 2
> Nodes|Nodes_2_index|1 1
> Node_Headers|Node_Headers_2_index|4 4
> Node_Headers|Node_Headers_1_index|4 1
> DTC_Statuses|DTC_Statuses_1_index|5234 1
> Header_Stream|sqlite_autoindex_Header_Stream_1|3 1
> TextTables|TextTables_1_index|21 1
> Byte_Types||4
> Transaction_Types|sqlite_autoindex_Transaction_Types_1|8 1
> Request_Parameters||5
> Limits||14
> Object_Categories|Object_Categories_2_index|1 1
> Object_Categories|Object_Categories_1_index|1 1
> DisplayFormats||7
> Message_Filters||5
> Object_Transactions|Object_Transactions_3_index|5747 40
> Object_Transactions|Object_Transactions_2_index|5747 2
> Object_Transactions|Object_Transactions_1_index|5747 160
> Units|sqlite_autoindex_Units_1|45 1
> SConfig|sqlite_autoindex_SConfig_1|2 1
> Service_Types|sqlite_autoindex_Service_Types_1|5 1
> Config||1
> Services|Services_3_index|12 6
> Services|Services_2_index|12 2
> Services|Services_1_index|12 12
> Services|sqlite_autoindex_Services_1|12 1
> Categories|Categories_

Re: [sqlite] Help speed up query

[veneff]

Yes, the DBs are on my local disk.  The quoted times are after the first run so 
mostly in cache.  It takes about twice the time the first run for both SQLite 
and MS Access.

Vance

on Apr 16, 2013, Simon Slavin  wrote:
>
>
>On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:
>
>> It was about 380 ms.  Now it is taking about 610 ms.
>
>Can I check with you that the database file is stored on a disk of the 
>computer which
>is executing the SQLite commands ?  In other words, that this is a local hard 
>disk
>and not one shared over a network.
>
>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

[sqlite] RTree Documentation Error?

[Mohit Sindhwani]

Hi, I was looking at the RTree documentation page with one of my 
colleagues - http://www.sqlite.org/rtree.html


We tried the example with the schema in 3.1, populated the data from 3.2 
and then queried it using the query of 3.3 - we got no results while the 
documentation says that "the query would very quickly locate the id of 1 
even if the R*Tree contained millions of entries".


Is the line "AND minY>=35.00  AND maxY<=35.44;" supposed to be "AND 
minY>=33.00  AND maxY<=35.44;" (33 instead of 35.00)?


Best Regards,
Mohit.
17/4/2013 | 1:15 AM.



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

Re: [sqlite] Help speed up query

[veneff]

I tried transfering the database to an in memory copy and running the queries.  
It is actually a little slower but not much.  I'm sure there are tables that 
are not being accessed and so loading them into memory would add to the time.

Vance

on Apr 16, 2013, ven...@intouchmi.com wrote:
>
>Yes, the DBs are on my local disk.  The quoted times are after the first run 
>so mostly
>in cache.  It takes about twice the time the first run for both SQLite and MS 
>Access.
>
>Vance
>
>on Apr 16, 2013, Simon Slavin  wrote:
>>
>>
>>On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:
>>
>>> It was about 380 ms.  Now it is taking about 610 ms.
>>
>>Can I check with you that the database file is stored on a disk of the 
>>computer which
>>is executing the SQLite commands ?  In other words, that this is a local hard 
>>disk
>>and not one shared over a network.
>>
>>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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help speed up query

[Simon Slavin]

On 16 Apr 2013, at 7:10pm, ven...@intouchmi.com wrote:

> I tried transfering the database to an in memory copy and running the 
> queries.  It is actually a little slower but not much.  I'm sure there are 
> tables that are not being accessed and so loading them into memory would add 
> to the time.

I'm sorry I'm not responding to your posts any more, but I don't think I can 
help.  I'm not familiar with anything that would cause ANALYZE to make a query 
slower, and I'm hoping an expert will see this and help you.

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

Re: [sqlite] Help speed up query

[Michael Black]

Have you tried increase sqlite cache size and page size?

pragma cache_size;
pragma page_size;

Try making page_size=4096

Then make cache_size*page_size as big as your database file.




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com
Sent: Tuesday, April 16, 2013 1:10 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Help speed up query

I tried transfering the database to an in memory copy and running the
queries.  It is actually a little slower but not much.  I'm sure there are
tables that are not being accessed and so loading them into memory would add
to the time.

Vance

on Apr 16, 2013, ven...@intouchmi.com wrote:
>
>Yes, the DBs are on my local disk.  The quoted times are after the first
run so mostly
>in cache.  It takes about twice the time the first run for both SQLite and
MS Access.
>
>Vance
>
>on Apr 16, 2013, Simon Slavin  wrote:
>>
>>
>>On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:
>>
>>> It was about 380 ms.  Now it is taking about 610 ms.
>>
>>Can I check with you that the database file is stored on a disk of the
computer which
>>is executing the SQLite commands ?  In other words, that this is a local
hard disk
>>and not one shared over a network.
>>
>>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
>
___
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] Help speed up query

[veneff]

Simon and everyone who has thrown ideas into the mix,
I very much appreciate the effort that you folks have put into this!
If nothing else, I am learning from this exorcise.

I set the page_size to 4096 and cache_size to 1024.
(I picked the next binary size above the 2976 KB size of the DB under test.)
No difference in speed. (I ran both in memory and off of disk versions).

Vance

on Apr 16, 2013, Michael Black  wrote:
>
>Have you tried increase sqlite cache size and page size?
>
>pragma cache_size;
>pragma page_size;
>
>Try making page_size=4096
>
>Then make cache_size*page_size as big as your database file.
>
>
>
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org
>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of ven...@intouchmi.com
>Sent: Tuesday, April 16, 2013 1:10 PM
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Help speed up query
>
>I tried transfering the database to an in memory copy and running the
>queries.  It is actually a little slower but not much.  I'm sure there are
>tables that are not being accessed and so loading them into memory would add
>to the time.
>
>Vance
>
>on Apr 16, 2013, ven...@intouchmi.com wrote:
>>
>>Yes, the DBs are on my local disk.  The quoted times are after the first
>run so mostly
>>in cache.  It takes about twice the time the first run for both SQLite and
>MS Access.
>>
>>Vance
>>
>>on Apr 16, 2013, Simon Slavin  wrote:
>>>
>>>
>>>On 16 Apr 2013, at 2:32pm, ven...@intouchmi.com wrote:
>>>
 It was about 380 ms.  Now it is taking about 610 ms.
>>>
>>>Can I check with you that the database file is stored on a disk of the
>computer which
>>>is executing the SQLite commands ?  In other words, that this is a local
>hard disk
>>>and not one shared over a network.
>>>
>>>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
>>
>___
>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

[sqlite] Need Help

[Vijay Mekala]

Hi,
I am new to sqlite and my objective is to run the sqlite tests on build
where sqlite got integrated and have to use my own ZFS for my test.

I have downloaded the source where tests are available but need help on how
to take them and run against sqlite which is integrated in our code.

Can some one can help on how to do it ?

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

Re: [sqlite] update record in contentless FTS4

[Scott Hess]

On Fri, Apr 12, 2013 at 9:28 AM, Lukas Gebauer  wrote:

> I have contentless FTS4 index for searching some my external data
> paired by docid.
>
> What I can do, when my existing previously indexed document was
> changed? I need to update existing FTS4 index too. But documentation
> says: "UPDATE and DELETE is not supported".
>
> Can I do new INSERT with existing docid again? Or what I can do else?
>

I am not familiar with how the contentless fts indices work, but in general
when fts2/3/4 delete a row, the index terms are removed by re-parsing the
original content, then inserting deletion tokens, which later "catch up"
with the originals and overwrite them.  Update is delete+insert, so has the
same restrictions.  In the best case, inserting again would insert all of
the new terms without removing the now-missing terms.  There are other ways
it could have been coded, but that's the way it was coded.

If this functionality is very important in general, you could perhaps have
an auxiliary table which records logical presence of rows, and only match
rows which exist in both tables.  Then you can delete from that table to
prevent rows from the fts index from matching.  Updates would effectively
be "delete marker row from aux table, insert the document as a new row". If
you need a stable identifier, the aux table can provide that bridge, too.

With such a solution, if you have lots of churn you may wish to
periodically re-create the entire index.

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