Hello, Sudheer Palaparambil,

PUT SALE AFTER FROM

SELECT P.product_nm, S.bill_no, S.entry_date, SD.quantity,
SUM( ( SD.quantity * SD.rate ) - SD.discount_a ), 'S'
FROM sale S
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 between '1.4.04' AND '30.4.04' )
GROUP BY S.entry_date, S.bill_no, P.product_nm, SD.quantity

You can try LEFT JOIN

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 )
LEFT 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 between '1.4.04' AND '30.4.04' )
GROUP BY S.entry_date, S.bill_no, P.product_nm, SD.quantity

or

SELECT P.product_nm, S.bill_no, S.entry_date, SD.quantity,
SUM( ( SD.quantity * SD.rate ) - SD.discount_a ), 'S'
FROM sale S
JOIN sale_details SD ON (SD.company_id = S.company_id )
AND (SD.parent_id = S.id )
LEFT 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 between '1.4.04' AND '30.4.04' )
GROUP BY S.entry_date, S.bill_no, P.product_nm, SD.quantity


======= At 2004-07-05, 04:15:00 you wrote: =======

>Hi,
>
> 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 ?
>
> I am on W2k Server (standalone), celeron 333 with
>256 MB Ram and Firebird 1.5.
>
> TIA.
>
>Sudheer Palaprambil
>
>
>
>
>
>___________________________________________________________ALL-NEW Yahoo! Messenger - sooooo many all-new ways to express yourself http://uk.messenger.yahoo.com
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>.

= = = = = = = = = = = = = = = = = = = =


Best regards.
tom
tom@...
2004-07-07