I am curious how many rows are in each of your test tables. Have you tried
this with a few million rows in each table?
Alexei Novakov <[EMAIL PROTECTED]>
03/10/2004 01:22 PM
To: [EMAIL PROTECTED]
cc: (bcc: Josh Ralph/IS/Expeditors)
Subject: Outer join performance hint.
Hi All,
I don't really know DB which is doing fine with
optimization of outer joins. MaxDB is not an
exception. I think there is room for optimization
though. Let's look at the following example:
1)
select t1.col1, t2.col1, t3.val
from table1 t1, table2.t2
left outer join table3 t3
on t3.col1 = t1.col1 and t3.col2 = t2.col1
where ...
This is not just slow, but it also uses a hard drive
very extencively. Query 1 can be substituted in most
of cases with the following:
2)
select t1.col1, t2.col1,
(select val from table3
where col1 = t1.col1
and col2 = t2.col1) as val
from table1 t1, table2 t2
where ...
This query looks scarier, but it works 3-5 times
faster and doesn't cause disk acivity as the first
one.
Generally speaking, query 1 for vast majority of
queries can be converted into form 2. On the other
hand query 1 provides much more ground for
optimization to the SQL processor.
Are there any plans to work on outer join
optimization?
Best regards.
Alexei.
__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you're looking for faster
http://search.yahoo.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
