I would consider changing to something like:

WITH FIRST_AUFTRAG(AUFTRAGSNR) as
(SELECT FIRST 1 AUFTRAGSNR
FROM AA_AUFTRAG_2 WHERE STATUS = 'RQ' ORDER BY AUSL_ORT)

SELECT AA2.AUFTRAGSNR, COUNT(AA2.PID) AS AnzahlPaletten,
COUNT(DISTINCT AA2.AUFTRAG_POS) AS AnzahlAuftrPos
FROM AA_AUFTRAG_2 AA2
JOIN FIRST_AUFTRAG FA ON AA2.AUFTRAGSNR = FA.AUFTRAGSNR
WHERE AA2.STATUS = 'RQ'
GROUP BY AA2.AUFTRAGSNR

But, as others have said, I don't think this in itself is the cause of your problem and if it works OK, then it may not be worth changing. What surprises me, is your statistics. It seems as if this WAS an active database (almost 24 million transactions), but that no-one uses it anymore. Are you sure that you got these statistics whilst there was a problem and not after having stopped (or restarted) something? I suppose it could be that when your jbird driver gives you the error message, then a lot of transactions disappear. If so, I would expect the statistics to be more interesting before you get the error message (you say it take a week, maybe run statistics daily to see if a transaction gap is building up?).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 23. april 2009 12:08
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] Problem Firebird 2.1 Superserver


Hello,

is perhaps this query the problem?

SELECT AUFTRAGSNR, COUNT(PID) AS AnzahlPaletten,
COUNT(DISTINCT(AUFTRAG_POS)) AS AnzahlAuftrPos FROM AA_AUFTRAG_2 AA2 WHERE
STATUS = 'RQ' AND AA2.AUFTRAGSNR = (SELECT FIRST 1 AUFTRAGSNR FROM
AA_AUFTRAG_2 WHERE STATUS = 'RQ' ORDER BY AUSL_ORT) GROUP BY AUFTRAGSNR

Every 10 seconds the java applications runs it to show for new orders.

Thank you.

_________________________
I hope this helps.

Gabor

Olaf Kluge írta:

> Hello,
>
> we have a big problem with firebird 2.1.
>
> Here the gstat-statistics:
>
> Flags 0
> Checksum 12345
> Generation 23475579
> Page size 4096
> ODS version 11.1
> Oldest Trans. 23475559
> Oldest active 23475560
> Oldest snapsh. 23475560
> Next Transaktion 23475561
> Bumped Trans. 1
> Sequence No. 0
> Next attachm. ID 2063
> Implementation ID 16
> Shadow Count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Generation date Feb 5, 2009 15:03:40
> Attributes force write
>
> Sweep interval: 20000
>
> It looks fine, but the process fbserver takes hour to hour more RAM from
> the system. From yesterday to this day increases the size from 300 Megabyte
> to 500 Megabyte. The firebird-process-size increases minutely more and more.
>
> The pagefile increases too. The problem is, that firebird jbird driver
> returns after one week of running firebird an error message, that firebird
> cannot allocate more memory.
>
> The server:
>
> 2 Gigabyte RAM,
> P4 3000
> XP pro
>
> What can be the problem? How can I find and solve it?
>
> Thanks for helping.
>
> Best regards
>
> Olaf Kluge