Hi Arno,

Thank u very much for your timely help. Now this
query is taking only 2 seconds.

Actually I was porting my Oracle application to
Firebird 1.5 and I tried the same syntax in FB also,
but the response was poor. But with the changes u
proposed the response time is almost similar to
Oracle.

Thank U.

Sudheer Palaparambil


--- Arno Brinkman <firebird@...> wrote: > Hi,

>
> > 2. sale (rows 61608)
> > --------------------
> > company_id smallint
> > id integer
> > entry_date date
> > (other columns.....)
> > (pk - company_id, id)
> > (index - company_id, entry_date)
>
> > Now a query like this
>
> SELECT
> P.product_nm, S.bill_no, S.entry_date,
> SD.quantity,
> SUM( ( SD.quantity * SD.rate ) - SD.discount_a ),
> 'S'
> FROM
> sale_details SD
> JOIN sale S ON (SD.company_id = S.company_id ) AND
> (SD.parent_id = S.id)
> JOIN products P ON (SD.company_id = P.company_id )
> AND (SD.product_id =
> P.id)
> WHERE
> ((S.company_id = 1 ) AND
> (S.entry_date >= '1.4.04' ) AND (S.entry_date <=
> '30.4.04' ))
> GROUP BY
> S.entry_date, S.bill_no, P.product_nm, SD.quantity
>
> > takes around 42 seconds to execute and using the
> > plan
> > PLAN SORT (JOIN (SD INDEX
> > (FK_SALE_DETAILS_TO_SALE),S INDEX (PK_SALE),P
> INDEX
> > (PK_PRODUCTS))).
> >
> > Is there any other way to speed up this query ?
> If
> > an index is required,
> > on what colunm and which table ?
>
> The index (company_id, entry_date) you have on table
> sale isn't selected
> although it's a interesting one for this query. The
> problem is that the
> optimizer gives higher priority on foreign-keys for
> determing the join
> ORDER.
>
> a workaround which probably will do it :
>
> SELECT
> p.product_nm, s.bill_no, s.entry_date,
> sd.quantity,
> SUM((sd.quantity * sd.rate) - sd.discount_a),
> 'S'
> FROM
> sale s
> LEFT JOIN sale_details sd ON (sd.company_id =
> s.company_id and
> sd.parent_id = s.id)
> JOIN products p ON (sd.company_id = p.company_id
> and sd.product_id = p.id)
> WHERE
> ((s.company_id = 1) and
> (s.entry_date >= '1.4.04') and (s.entry_date <=
> '30.4.04'))
> GROUP BY
> s.entry_date, s.bill_no, p.product_nm, sd.quantity
>
> With the LEFT JOIN i force the JOIN ORDER and the
> index (company_id,
> entry_date) should be chosen.
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Firebird open source database (based on IB-OE) with
> many SQL-99 features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
> Support list for Interbase and Firebird users :
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep :
> news://newsgroups.firebirdsql.info
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Yahoo! Domains - Claim yours for only $14.70
>

http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/67folB/TM

>

--------------------------------------------------------------------~->

>
>
>
> Yahoo! Groups Links
>
>
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
>

___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com