Re: [sqlite] Help speed up query

On Tue, Apr 16, 2013 at 8:52 PM, wrote: > 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. > At this point, sounds like the only way to possibly

Re: [sqlite] Help speed up query

096 > >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:

Re: [sqlite] Help speed up query

] 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

Re: [sqlite] Help speed up query

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

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,

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

uchmi.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

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

s very slow] -- a SEARCH >resulting >in (~1 row) means that the optimizer expects to be able to use an index to >select >one row from the table (lightening fast) -- (~100 rows) means that there >is no >information to determine how many rows will be selected and this step mi

Re: [sqlite] Help speed up query

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

Re: [sqlite] Help speed up query

/\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of ven...@intouchmi.com > Sent: Monday, 15 April, 2013 15:20 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Help speed up query > >

Re: [sqlite] Help speed up query

SQLite was much slower on many queries AFTER analyze was executed. here are the referenced tables, # of rows, primary key, and additional indexes: Objects = 5495 rows, Primary key=Object_ID, other indexes on Address_ID, Dimension_ID, DisplayFormat_ID, Limit_ID, and Object_ID; DTC_Statuses = 5234

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:47pm, ven...@intouchmi.com wrote: > The integrity check came back with OK. Good. > 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

Re: [sqlite] Help speed up query

I corrected the "DTC" to 'DTC' and undid the analyze since that seemed to be slowing things down quite a bit. Unfortunately, the speed did not improve. Here is the latest explain query plan: 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX sqlite_autoindex_Object_Types_1 (Object_Type=?)

Re: [sqlite] Help speed up query

The integrity check came back with OK. My software was not locked up totally. It did finally come back. For some reason executing analyze slow other queries way down. Here is the new explain query plan: 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX sqlite_autoindex_Object_Types_1

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:40pm, Igor Tandetnik wrote: > On 4/15/2013 4:31 PM, Simon Slavin wrote: >> I also note this: >> >>> WHERE ((([Object_Types].[Object_Type])="DTC")) >> >> This is technically incorrect. It should use single quotes around DTC, not >> double quotes. > >

Re: [sqlite] Help speed up query

On 4/15/2013 4:31 PM, Simon Slavin wrote: I also note this: WHERE ((([Object_Types].[Object_Type])="DTC")) This is technically incorrect. It should use single quotes around DTC, not double quotes. Actually - does any of the tables participating in the statement have a column named DTC,

Re: [sqlite] Help speed up query

On 15 Apr 2013, at 9:22pm, ven...@intouchmi.com wrote: > This is the create index statement: > CREATE INDEX IF NOT EXISTS > "DTC_Statuses_1_index" ON > "DTC_Statuses" ("Object_ID"); > > For some reason, when I execute analyze, my software locks up somewhere > when running its test queries.

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 10:04 PM, Dominique Devienne wrote: > On Mon, Apr 15, 2013 at 9:45 PM, wrote: > >> Igor, >> >> As a follow up, I added the other two indexes with no real inprovement. >> Here is the new explain query plan: >> 0|0|6|SEARCH TABLE

Re: [sqlite] Help speed up query

This is the create index statement: CREATE INDEX IF NOT EXISTS "DTC_Statuses_1_index" ON "DTC_Statuses" ("Object_ID"); For some reason, when I execute analyze, my software locks up somewhere when running its test queries. I'll delve further into this. Vance on Apr 15, 2013, Igor Tandetnik

Re: [sqlite] Help speed up query

Are you sure? The query plan looks like Objects(Object_Type_ID) was added but DTC_Statuses(Object_ID) was not. Igor Tandetnik On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote: DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik

Re: [sqlite] Help speed up query

On Mon, Apr 15, 2013 at 9:45 PM, wrote: > Igor, > > As a follow up, I added the other two indexes with no real inprovement. > Here is the new explain query plan: > 0|0|6|SEARCH TABLE Object_Types USING COVERING INDEX > sqlite_autoindex_Object_Types_1 (Object_Type=?) (~1

Re: [sqlite] Help speed up query

DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik wrote: > >On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: >> As a follow up, I added the other two indexes with no real inprovement. > >Which two indexes? >-- >Igor Tandetnik >

Re: [sqlite] Help speed up query

On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Help speed up query

On 4/15/2013 3:27 PM, ven...@intouchmi.com wrote: Here is the result of the explain query plan: 0|0|1|SCAN TABLE DTC_Statuses (~100 rows) 0|1|0|SEARCH TABLE Objects USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) 0|2|6|SEARCH TABLE Object_Types USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

Re: [sqlite] Help speed up query

Igor, As a follow up, I added the other two indexes with no real inprovement. Here is the new explain query plan: 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

Re: [sqlite] Help speed up query

Thanks Igor! I did create indexes for the SQLite DB version based on the indexes in the MS Access DB. Object_Texttables does have an index on Object_ID and Object_Transactions also has an index on - Object_ID. Just to double check, my index declarations are: CREATE INDEX IF NOT EXISTS

Re: [sqlite] Help speed up query

...@intouchmi.com Sent: Monday, April 15, 2013 12:08 PM To: sqlite-users@sqlite.org Subject: [sqlite] Help speed up query Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends

Re: [sqlite] Help speed up query

On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Help speed up query

On 4/15/2013 2:12 PM, ven...@intouchmi.com wrote: That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234,

Re: [sqlite] Help speed up query

here is the output from explain: 0|Trace|0|0|0||00| 1|String8|0|1|0|DTC|00| 2|Goto|0|80|0||00| 3|OpenRead|1|17|0|8|00| 4|OpenRead|0|1027|0|10|00| 5|OpenRead|6|1025|0|2|00| 6|OpenRead|7|16|0|2|00| 7|OpenRead|2|170|0|4|00| 8|OpenRead|8|171|0|keyinfo(1,BINARY)|00| 9|OpenRead|3|731|0|6|00|

Re: [sqlite] Help speed up query

That's odd, I had listed the tables and keys in the original message. Here is that section again: which generates about 16000 rows. The row counts and primary keys for the referenced tables are: Objects = 5495, Object_ID; DTC_Statuses = 5234, DTC_Status_ID; Object_TextTables = 15718,

Re: [sqlite] Help speed up query

On 4/15/2013 1:07 PM, ven...@intouchmi.com wrote: I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I

[sqlite] Help speed up query

Hi, I've been trying to convince my boss that SQLite should be faster than MS Access. I wrote a conversion program that reads a Access DB and generates an SQL statement files and then sends the table creation and insert statements to SQLite. I also wrote a C++ wrapper that I can call from VB6