Re: [sqlite] Improving query performance

2009-04-02 Thread John Elrick
D. Richard Hipp wrote: > On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > >> explain query plan >> select DISTINCT RESPONSES.RESPONSE_OID >> from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS >> where >> SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and >> DATA_ELEMENTS.DATA_EL

Re: [sqlite] Improving query performance

2009-04-01 Thread D. Richard Hipp
On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > > explain query plan > select DISTINCT RESPONSES.RESPONSE_OID > from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS > where > SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and > DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name a

[sqlite] Improving query performance

2009-04-01 Thread John Elrick
Sqlite 3.6.10 Background I have the following abbreviated case: CREATE TABLE sequence_elements ( sequence_element_oid integer primary key autoincrement, sequence_element_name varchar, definition_parent varchar, instance_parent varchar, soft_deleted_char varchar default 'F' ) C

Re: [sqlite] improving query performance

2006-03-31 Thread Dennis Cote
On 3/30/06, Dennis Cote <[EMAIL PROTECTED]> wrote: > > > Another approach is to remove your primary key. If you don't need it to > enforce uniqueness constraints on your data then you could eliminate the > primary key, and change the EntryId column into an integer primary key > column. This primary

Re: [sqlite] improving query performance

2006-03-30 Thread Jay Sprenkle
On 3/30/06, Andy Spencer <[EMAIL PROTECTED]> wrote: > > Yes. I tried adding an index to the table, after the data had been > imported and prior to fetching the entry property values, and the > construction of the index took longer than it had taken previously to > fetch all of the property values.

Re: [sqlite] improving query performance

2006-03-30 Thread Andy Spencer
On Thu, 30 Mar 2006, Christian Smith wrote: > On Wed, 29 Mar 2006, Andy Spencer wrote: > > >I have a sqlite database with about 3 GB of data, most of which is stored > >in a data table with about 75 million records, having three columns > >(EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and

Re: [sqlite] improving query performance

2006-03-30 Thread Dennis Cote
Christian Smith wrote: On Wed, 29 Mar 2006, Andy Spencer wrote: I have a sqlite database with about 3 GB of data, most of which is stored in a data table with about 75 million records, having three columns (EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and PRIMARY KEY(EntryId, PropertyI

RE: [sqlite] improving query performance

2006-03-30 Thread Dan Petitt
d and only insert if it didn't exist, than to rely on insert/ignore failure. Hope this helps. -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 30 March 2006 15:54 To: sqlite-users@sqlite.org Cc: Subhash Mangipudi; Herc Silverstein Subject: Re: [sqlite]

Re: [sqlite] improving query performance

2006-03-30 Thread Christian Smith
On Wed, 29 Mar 2006, Andy Spencer wrote: >I have a sqlite database with about 3 GB of data, most of which is stored >in a data table with about 75 million records, having three columns >(EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and >PRIMARY KEY(EntryId, PropertyId). > >This table is not

Re: [sqlite] improving query performance

2006-03-30 Thread Jay Sprenkle
On 3/29/06, Andy Spencer <[EMAIL PROTECTED]> wrote: > I have a sqlite database with about 3 GB of data, most of which is stored > in a data table with about 75 million records, having three columns > (EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and > PRIMARY KEY(EntryId, PropertyId). > > Th

[sqlite] improving query performance

2006-03-29 Thread Andy Spencer
I have a sqlite database with about 3 GB of data, most of which is stored in a data table with about 75 million records, having three columns (EntryId INTEGER, PropertyId INTEGER, Value NUMERIC) and PRIMARY KEY(EntryId, PropertyId). This table is not indexed, to allow faster updates. The problem