On 11/04/11 22:28, Eric Torstenson wrote: > I have a table with some data that can appear multiple times based on a > foreign key. I had a query where I was joining this table with another > table, then back to the first table using different foreign keys and > aliasing the duplicate table accordingly. > > In Firefox' sqlite Manager, this query ran in about 5 or 10 seconds. > When I ran it using my C++ program and soci, it took minutes to step > into the first iteration. It didn't matter if I used a rowset or a > prepared statement. When I pull the second join out and do the query > independently, the code runs without any delay. > > It seems odd that it would differ. I don't know what DB Backend sqlite > manager uses, but I would have assumed that ultimately the two use the > same libraries. > > Here is the query in case anyone sees anything suspect in it that would > be tripping up SOCI: > > SELECT > regions.gene_id, primary_name, chrom, eff.start, eff.end, orig.start, > orig.end, group_concat(alias) > FROM > region_bounds orig NATURAL JOIN > regions NATURAL JOIN > region_bounds eff LEFT JOIN region_alias ON > (regions.gene_id=region_alias.gene_id) > WHERE > orig.population_id=0 AND > eff.population_id=0 > GROUP BY regions.gene_id > > > The schema for region_bounds, regions and region_alias have columns > named the same except for foreign keys which should satisfy the NATURAL > JOIN functionality. > > If I leave off the second region_bounds, the query runs with no > hesitation. I can understand it taking less time to complete, but again, > the query runs very fast in the firefox plugin. > > Thanks, > > eric >
which version of sqlite are you using in the soci program? maybe its related to this: http://www.sqlite.org/src/info/13f033c865 Best Regards, Julian Taylor
signature.asc
Description: OpenPGP digital signature
------------------------------------------------------------------------------ Forrester Wave Report - Recovery time is now measured in hours and minutes not days. Key insights are discussed in the 2010 Forrester Wave Report as part of an in-depth evaluation of disaster recovery service providers. Forrester found the best-in-class provider in terms of services and vision. Read this report now! http://p.sf.net/sfu/ibm-webcastpromo
_______________________________________________ Soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
