Hi !

Em 2/4/2011 22:20, Gustavo escreveu:

> Hello:
>
> I have an application in Delphi installed in a network with the following data:
>
> Server:
> Athlon - 1.29GHz - 1GB RAM
> Windows Server 2003
> Firebird 2.1.3.18185 SuperServer
>
> Clients (10):
> Windows XP
>
> Databases: One of 1.6GB and two smaller than 10MB
>
> They use the application since many years ago without problems. But since the last week they have problems when they connect to some databases or when they make some querys. When they have these problems, I can see that the process fbserver.exe is consuming a lot of memory.
>
> I found in firebird.log (in the server) messages like the following:
>
> Allocated 1729 page buffers of 2048 requested
>
> unable to allocate memory from operating system
>
> Investigating in internet and in this forum, I found this may be related with large querys using blob fields. I am almost sure this is the problem because a few days ago they began to use an option which makes a query like the following:
>
> SELECT TIPCLASNRO
> ,FECHA
> ,SUBSTRING(COALESCE(NOTASCOM,'') FROM 1 FOR 100) AS NOTASC
> FROM VPRESUP
>
> The field NOTASCOM is a blob field and table VPRESUP have more than 20000 records.
>
> They never need the whole field. They always use SUBSTRING. So I thougt to do the following:
>
> SELECT TIPCLASNRO
> ,FECHA
> ,SUBSTRING(COALESCE(CAST(NOTASCOM AS CHAR(32000)),'') FROM 1 FOR 100) AS NOTASC
> FROM VPRESUP
>
> This works good because I saw in task manager that the first query uses a lot of memory and the second one practically does not use memory.
>
> But (there is always a but!), this is perfect only if I am sure there is no register with more than 32000 bytes in NOTASCOM. If this is not true, Fireberd returns an error message about string truncation and this does not work.
>
> Does anyone have any idea to suggest?
>
> Thanks in advance

I've faced some similar problem recently...

I use a query like:

select list(FieldA), FieldB from TableA group by Field B

It uses a LOT of memory that is freed uppon transaction finish If my
memory serves me right...

It's a known issue and IIRC corrected on 2.1.4, I found it on the tracker.

see you !