Hello,
I must be missing something. When Manager creates a JOIN, it adds a "OR
(tN.FK IS NULL)" to the where clause even though FK is defined as
not_null => 1 in tN. Adding this clause really extends the query time on
some queries. I just tested one with and without the OR clause; with
took 9 minutes while without took 30 seconds.
So, it's critical that I fix this but I don't know where to change
QueryBuilder's behavior.
Here are the offending FROM and WHERE clauses:
FROM
transactions t1
LEFT OUTER JOIN persons t2 ON(t1.id = t2.transaction_id)
LEFT OUTER JOIN customs t3 ON(t1.id = t3.transaction_id)
LEFT OUTER JOIN donorccs t4 ON(t1.id = t4.transaction_id)
LEFT OUTER JOIN baskets t5 ON(t1.id = t5.transaction_id)
WHERE
t1.Result = 0 AND
NOT(t5.Sustainer = 'Yes') AND
((t1.id = t2.transaction_id) OR (t2.transaction_id IS NULL)) AND
((t1.id = t3.transaction_id) OR (t3.transaction_id IS NULL)) AND
((t1.id = t4.transaction_id) OR (t4.transaction_id IS NULL)) AND
((t1.id = t5.transaction_id) OR (t5.transaction_id IS NULL))
RDBO version: 0.75
Database is MySQL 4.1
transaction_id is defined this way in each of the child tables:
transaction_id => { type => 'integer', default => '0', not_null => 1 },
Here is the query that was 10 times faster:
FROM
transactions t1
LEFT OUTER JOIN persons t2 ON(t1.id = t2.transaction_id)
LEFT OUTER JOIN customs t3 ON(t1.id = t3.transaction_id)
LEFT OUTER JOIN donorccs t4 ON(t1.id = t4.transaction_id)
LEFT OUTER JOIN baskets t5 ON(t1.id = t5.transaction_id)
WHERE
t1.Result = 0 AND
NOT(t5.Sustainer = 'Yes') AND
((t1.id = t2.transaction_id) ) AND
((t1.id = t3.transaction_id) ) AND
((t1.id = t4.transaction_id) ) AND
((t1.id = t5.transaction_id) )
Thanks!
Kurt Hansen
[EMAIL PROTECTED]
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
