Tim,
I'm not sure what you really get out of this but my comments may assist (or
maybe not)
1. I always group by but never order by (used not to make a difference -
group by did it and order by was ignored) maybe 1.5 allows it now.
2. I always select the fields in the same order as the group clause. In this
case your SUM should be last in the select.
3. I always keep by group selects as simple as possible and if I need other
fields joined to them, I put the group select in a view and then select a
join to the view rather than select it all and join it all. Makes for easier
reading and above all..
I think it helps the optimiser decipher what you are really trying to do.
If you still need to do what you are doing (e.g. for dynamic SQL building
reasons) then try my suggestion and see what the plan is. It could come up
with a very fast one for you.

Alan

> -----Original Message-----
> From: Tim Ledgerwood [mailto:tim@...]
> Sent: Wednesday, 2 July 2003 9:04 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Query optimization
>
>
> Anyone got any ideas on how to optimize this query? It runs for three or
> four minutes, and there are only a few thousand records :
>
> FOR SELECT
> l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> SUM(l.MQuantity) AS MQuantity, L.INV_TYPE, L.INV_PREFIX,
> c.SurName, s.Sub_Name, s.Card_Code, s.RegNum
> FROM
> log L JOIN client C
> ON
> C.code = L.acc_Num
> JOIN clnt_sub S
> ON
> S.code = L.acc_num
> AND S.sub_code = L.sub_code
> WHERE
> L.POS_ID = :POSID
> AND L.SHIFTNUM = :SHIFTNUM
> GROUP BY l.Receipt, l.Receipt2, l.Acc_Num, l.Sub_Code,
> l.inv_type, l.inv_prefix, c.SurName, s.Sub_Name, s.Card_Code,
> s.RegNum
> ORDER BY c.Surname, l.Acc_Num, l.Sub_Code
>
>
> The query plan :
>
> PLAN SORT (SORT (MERGE (SORT (S NATURAL),SORT (JOIN (C NATURAL,L INDEX
> (IDX_POSTYPE))))))
>
> And the indices : (No indices on subsidiary tables)
>
> CREATE INDEX "IDX_DRYSTOCKSALES" ON "LOG"("TRAN_TYPE", "MISC_TRANS",
> "STOK_CODE", "SAL_REF", "POS_ID");
> CREATE INDEX "IDX_LOGSTOCK" ON "LOG"("STOK_CODE");
> CREATE INDEX "IDX_OPT" ON "LOG"("PAY_CODE", "PUMP");
> CREATE INDEX "IDX_POSID" ON "LOG"("POS_ID");
> CREATE INDEX "IDX_POSTYPE" ON "LOG"("POS_ID", "TRAN_TYPE");
> CREATE INDEX "IDX_TAX" ON "LOG"("POS_ID", "TAX_INDEX");
> CREATE INDEX "IDX_TRANDATA" ON "LOG"("TRAN_TYPE", "INV_TYPE", "POS_ID");
> CREATE INDEX "IDX_TRANTYPE" ON "LOG"("TRAN_TYPE");
> CREATE INDEX "IDX_UPDATELOG" ON "LOG"("RECEIPT", "RECEIPT2", "DESCRIP",
> "MQUANTITY", "POS_ID");
>
>
> [Non-text portions of this message have been removed]
>
>
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>