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