You must upgrade to firebird 2,1

Firebird 1,5 is poor index selectivity with views which has aggregate
function.

I have tested it before


-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of PenWin
Sent: Thursday, August 09, 2007 1:39 PM
To: Firebird support
Subject: [firebird-support] View slower than a select

Hi!

I have just noticed something very strange. I have a SELECT which lists all
records from a master table and a sum from a detail table. Something like:

SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
FROM master m
LEFT JOIN detail d ON d.master_key=m.key
GROUP BY m.key, m.name, m.creation_date, m.note, m.group

Now in my application I will always select only those rows from master which

belong to a particular group. Something like:

SELECT m.key, m.name, m.creation_date, m.note, m.group, SUM(d.amount)
FROM master m
LEFT JOIN detail d ON d.master_key=m.key
WHERE m.group=1234
GROUP BY m.key, m.name, m.creation_date, m.note, m.group

This select performs in about 0.0017 seconds on my system, in other words,
it is fast enough. To make my code easier to read I wanted to wrap the
original select in a view and then select from the view:

CREATE VIEW my_view (key, name, creation_date, note, group, total_amount) AS

[the_select_at_the_top]

SELECT key, name, creation_date, note, group, total_amount
FROM my_view
WHERE group=1234

Unfortunatelly, this takes some six seconds to process (about 600x as long
as the select). It seems likely Firebird first counts the amounts over the
whole table and then applies the group=1234 condition. My question is, is
there anything I can do to keep the simplicity of a view but improve the
speed? The plans look pretty much the same to me:

PLAN SORT (JOIN (MASTER NATURAL,DETAIL INDEX (DETAIL_KEY)))
PLAN SORT (JOIN (MY_VIEW MASTER NATURAL,MY_VIEW DETAIL INDEX (DETAIL_KEY)))

All this on WinXP, Firebird 1.5 running in SuperServer mode.

And a somewhgat unrelated minor question: Do I really have to list all
selected fields in the GROUP BY clause? This is one thing I rather like
about MySQL, that I can simply group by the primary key (m.key) and not type

the other fields which obviously depend on that primary key...

Thanks,

Pepak



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

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

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

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

Yahoo! Groups Links




__________ NOD32 2446 (20070809) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com