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..
>
> Mark
> <http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>
--
-----------------------------------------------------------------
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer.
Position is in the Washington D.C. metro area. Contact
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
