[RDBO] Suppress (or FOREIGN KEY is NULL) in queries
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.phpp=sourceforgeCID=DEVDEV
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Suppress (or FOREIGN KEY is NULL) in queries
On 12/5/06 9:50 AM, Kurt Hansen wrote:
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. [...]
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))
Do you really want a LEFT OUTER JOIN, or is a simple join sufficient? In
other words, are you trying to get only the t1s that have corresponding
t2s, t3s, t4s, and t5s. If so, use require_objects instead of
with_objects in your Manager query.
If you do want t1s even if they don't have one or more related rows in
t[2-5]s, then using with_objects is correct. The redundant JOIN conditions
are added for MySQL only. They're there to persuade MySQL to use its
indexes. (In some cases, MySQL was ignoring relevant indexes when those
clauses were omitted.) The IS NULL parts have to be there in order to
preserve the semantics of the query (i.e., we still want t1 rows even if one
or more corresponding t[2-5] rows do not exist).
It's not that the actual FK columns in the database will ever be null. It's
that such a row may not exist at all for a given t1 row, but we still want
to include that t1 row in the query, in which case all the result rows for
that other tN table will be null.
Anyway, to disable this feature, add this parameter to the Manager call:
redundant_join_conditions = 0
Is anyone else running into performance problems with this? If so, let me
know and I'll change the default to be off instead of on for MySQL. If
you'd like to do this yourself right now, you can override the
likes_redundant_join_conditions() method in the Rose::DB driver class:
use Rose::DB::MySQL;
no warnings 'redefine';
sub Rose::DB::MySQL::likes_redundant_join_conditions { 0 }
RDBO version: 0.75
You also might want to consider updating RDBO since many bugs and leaks have
been fixed since 0.75.
-John
-
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.phpp=sourceforgeCID=DEVDEV
___
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Suppress (or FOREIGN KEY is NULL) in queries
Hi Kurt could be added to the documentation of Manager or QueryBuilder or to an FAQ, e.g. What to look for if your MySQL query is afully slow. :-) Another database vendor? (I tried to resist, truely). -- Ron Savage [EMAIL PROTECTED] http://savage.net.au/index.html - 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.phpp=sourceforgeCID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
