Hi, Alex

Thanks much!. The "inner join CONTACTS a on a.ID = c.CONTACT_ID + 0" does the trick. Here is my questions:

1)How to update indices statistics, I'm using DBW, IBExpert.

2)Is there any Docs/Articles/tips regarding how to optimize SQLs out there?

Best Regards,

William

Alexandre Benson Smith <iblist@...> wrote: william_yuwei wrote:

> Hi, All
>
> I have the following SQL:
>
> select i.INVOICE_DATE, i.INVOICE_NO, i.SHIPPING_CHARGE,
> i.DIVISION_ID, a.ACCOUNT_NO, a.NAME, d.NAME,
> i.KIND, i.SHIPPING_GST + i.GST, i.NETSALES, cd.PAYMENT_DISCOUNT
> from INVOICE i
> inner join CLIENTS c on c.ID = i.CLIENT_ID
> inner join CONTACTS a on a.ID = c.CONTACT_ID and
> (:clientgroupid <= 0 or a.CLIENTGROUP_ID = :clientgroupid)
> inner join DIVISION d on d.ID = i.DIVISION_ID
> inner join CLIENT_DIVISION cd on cd.CLIENT_ID = i.CLIENT_ID and
> cd.DIVISION_ID = i.DIVISION_ID
> where i.INVOICE_DATE between :startdate and :enddate and
> i.POSTED = 'Y' and i.REVERSEID is null
> order by i.DIVISION_ID, i.INVOICE_NO
>
> The Plan for above SQL: A NATURAL, C FK_CONTACT_ID, why is that? I
> suppose that A PK_CONTACT, and C PK_CLIENT
>
> If I changed the above 'inner join CLIENTS c on c.ID = i.CLIENT_ID' to
> 'left join CLIENTS c on c.ID = i.CLIENT_ID'
> then suddenly the PLAN changed to C PK_CLIENT, A PK_CONTACT, why?
>
> it looks to me that Plan for left join is better than inner join, how
> does that happen?
>
> Thanks
>
> William
>

William,

I am not an optimizer guru, so take my comments with care.

left join force the tables before the LEFT JOIN to be treated first in
the join order, when you have only INNER JOINS the order could be
changed acording to the optimizer guesses.

I'd expect INVOICE to be the first table because it has a filter clause
on it, but optimzer could use CONTACTS first if it is a small table. If
it is not the case, perhaps you should update your indices statistics.
you could change your join clause to:

inner join CONTACTS a on a.ID = c.CONTACT_ID + 0 and

this could help to avoid that index.

using LEFT JOIN is a trick to help optimizer choose a better plan in
some cases, as adding +0 or || '' to the fields

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




SPONSORED LINKS
Technical support Computer technical support Compaq computer technical support Compaq technical support Hewlett packard technical support Microsoft technical support

---------------------------------
YAHOO! GROUPS LINKS


Visit your group "firebird-support" on the web.

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


---------------------------------





William, Yu


---------------------------------
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.

[Non-text portions of this message have been removed]