[Firebird-devel] [ firebird-Field-test-1432465 ] having clause dramatically slows down the select execution

[SourceForge.net <no...@so...>]

Field-test item #1432465, was opened at 2006-02-15 22:07
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=593943&aid=1432465&group_id=9028

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: SQL
Group: As Designed/Pitfall
Status: Open
Resolution: None
Priority: 5
Submitted By: centros (centros)
Assigned to: Nobody/Anonymous (nobody)
Summary: having clause dramatically slows down the select execution

Initial Comment:
Using "having" clause in select statement leads to a
very slow execution - high CPU usage. I am testing on
FB 2.0 Beta 2, it is the same problem also on FB 1.5
Stable. The same select on IB 6.0 or IB 7.5 runs
without any problem, nearly as fast as without "having"
clause.

Here is the problematic select with "having" clause:
select
EIDSKLADKARTA,SKLPOHTYP,sum(SKLPOHDETAILMNOZSTVI),sum(SKLPOHDETAILCELKEMCENANAKUP)
from SKLPOH
left outer join SKLPOHDETAIL on SKLPOHDETAIL.IDSKLPOH =
SKLPOH.IDSKLPOH
where SKLPOH.RECORDACTIVE = '1' and SKLPOHDATZADAL <=
:DATUM and SKLPOHSTORNOVANO = '0' and
SKLPOHDETAIL.RECORDACTIVE = '1'
group by EIDSKLADKARTA,SKLPOHTYP
having EIDSKLADKARTA > 0
order by EIDSKLADKARTA,SKLPOHTYP

Both tables SKLPOH and SKLPOHDETAIL have in about
200.000 rows, whole database file is about 300 MB big.

Select without having clause executes in about 10
seconds, while the same select with having clause takes
about 100 times more!!!

Compared to IB 6.0 I noticed, that FB 2.0 needs about
20 times more memory buffer reads than IB for select
with having clause. Without having clause the number of
reads with FB and IB is equal.

If someone is interested, I can offer my database file
for testing.

Martin


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

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=593943&aid=1432465&group_id=9028