Hey Samuel
Thanks for your ideas.
In fact we stumbled across the solution, and I am amazed we didnt
think of it earlier, and no-one suggested it. Basically our LEAVES
table doesn't have an Index !!
As soon as we added an index, the process sped up by 17000% :-)
However, I have some questions about this.
1) What happens when you add records to a table, since the index was built ?
2) Can we trigger a 're-index' without closing the database ?
3) In our tests we added the Index manually with an application to
modify the DB, however, in our own app, we can't get the index to be
added - any ideas:
------------------------
I am trying to add an index to my database, but the following code is
failing in CREATE INDEX with error 14 (unable to open Database file).
rc = sqlite3_open(OurDataBaseName, &db);
if (rc)
{
fprintf(stderr, "sqlite3_open returned %d\n",rc);
return -1;
}
rc = sqlite3_exec(db, "CREATE INDEX leaves_index on LEAVES (leafID)",
0, 0, &errmsg);
the OPEN worked fine, so why is CREATE INDEX returning this error ?
---------------------
Thanks again
Mark.
>
>Message: 2
>Date: Thu, 28 Feb 2008 21:25:35 -0500
>From: "Samuel Neff" <[EMAIL PROTECTED]>
>Subject: Re: [sqlite] Optimization Question for SQLite Experts
>To: "General Discussion of SQLite Database" <[email protected]>
>
>Here's two suggestions. First the simple suggestion is instead of this..
>
>for (z=0;z<numTwigs;z++) //there are 200
>{
> sqlite3_get_table("select * from LEAVES where leafID =
>theTwig[z].childLeafID")
>// we end up performing this query 200 times, once for each of the
>parent twigs.
>}
>
>try this
>
>select * from LEAVDES where leafID IN (.... list of leaves ...)
>
>and then sort out which records belong to which in code. That way you're
>executing far less individual queries. That's a simple optimization that
>should help a bit, but you still need some levels of recursion to identify
>all the twigs.
>
>Another option is to load all of the IDs and references into a structure in
>memory that allows you to perform efficient hierarchical searches. Use this
>structure for the recursive search to identify the id's of all the twigs and
>leaves, and then you can issue one sql statement to get all twigs and one to
>get all leaves. Much faster and with 15000 leaves should have plenty of
>space to hold the data in memory, especially if you're only storing ids and
>references.
>
>HTH,
>
>Sam
>
>
>On Thu, Feb 28, 2008 at 7:00 PM, Mark Gilbert <[EMAIL PROTECTED]> wrote:
>
>> Folks.
>>
>> Looking for some advice from hardened SQliters...
>>
>> ...
>
>For each twig we have to find all the leaves. The Leaves table has
>> maybe 15000 records and we have a query where we search the Leaves
>> table once for each twig we find. In our part of the tree there
>> might be 200 twigs, and so we end up searching the leaves DB 200
>> times, to build up the list of leaves attached to the twigs
>>
>> ...in pseudocode:
>> for (z=0;z<numTwigs;z++) //there are 200
>> {
>> sqlite3_get_table("select * from LEAVES where leafID =
>> theTwig[z].childLeafID")
>> // we end up performing this query 200 times, once for each of the
>> parent twigs.
>> }
>> ...
>>
> > Thanks for any experience you can share..
>>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
