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

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

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

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,

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] impro

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

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