Hi

i've a Firebird DB server under Fedora core 1
I use Firebird classic Server updated to last version.
The server is a IBM xseries 345 with Raid5 and 2 CPU Xeon with
HyperThreading Enabled (4 virtual CPU) and 2048MB Ram

the server don't have problem if DB are smaller than 150MB.

when it size go upper 150 Mb the server performance go to wrong

I've disabled the sweep with the gfix utility and everytime the DB go
to
large i do a backup-restore procedure.

My only solution is to delete record from DB and reduce it to small
size,
but i can't do this solution everytime.

This is the first stored procedure that are executed by client after
log-in


CREATE PROCEDURE DLP$_SP_GET_NEW_RECORD
RETURNS (ID_CUSTOMER INTEGER)
AS
declare variable cust INTEGER;
declare variable cust_lock INTEGER;
BEGIN
select first 1 customer_data.id_customer
from customer_data
left join phone_calls on customer_data.id_customer =
phone_calls.id_customer
where (phone_calls.id_customer is null and
customer_data.vlock = 0 and
customer_data.stato = 'A' and
) or
(customer_data.vlock = 0 and
customer_data.stato = 'A' and
(customer_data.resc_timestamp < current_timestamp or
customer_data.resc_timestamp is null
)
) or
(customer_data.vlock = 1 and
(customer_data.lock_timestamp + (cast (2 as float) / 24)) <
current_timestamp
)
into :cust;

if (:cust > 0 or not(:cust is null)) then
begin
select id_customer
from customer_data
where id_customer = :cust
for update with lock
into cust_lock;

update customer_data
set vlock = 1,
lock_timestamp = current_timestamp
where id_customer = :cust_lock;

id_customer = :cust_lock;
suspend;
end
else
begin
id_customer = 0;
suspend;
end
END

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

And this are the tables and index

/* Tabella: CUSTOMER_DATA */

CREATE TABLE CUSTOMER_DATA (
ID_CUSTOMER INTEGER NOT NULL,
PROVINCIA VARCHAR (3) CHARACTER SET NONE COLLATE NONE,
LOCALITA VARCHAR (100) CHARACTER SET NONE COLLATE NONE,
INDIRIZZO VARCHAR (100) CHARACTER SET NONE COLLATE NONE,
CIVICO VARCHAR (50) CHARACTER SET NONE COLLATE NONE,
CAP VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
COGNOME VARCHAR (100) CHARACTER SET NONE COLLATE NONE,
NOME VARCHAR (100) CHARACTER SET NONE COLLATE NONE,
TITOLO VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
PREFISSO VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
TELEFONO VARCHAR (15) CHARACTER SET NONE COLLATE NONE,
PREFISSO2 VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
TELEFONO2 VARCHAR (15) CHARACTER SET NONE COLLATE NONE,
IDCHIAMATA VARCHAR (50) CHARACTER SET NONE COLLATE NONE,
IDCONTATTO VARCHAR (50) CHARACTER SET NONE COLLATE NONE,
IDCAMPAGNA INTEGER,
CODICEFISCALE VARCHAR (16) CHARACTER SET NONE COLLATE NONE,
EMAIL VARCHAR (50) CHARACTER SET NONE COLLATE NONE,
SOCIETA VARCHAR (100) CHARACTER SET NONE COLLATE NONE,
PARTITAIVA VARCHAR (11) CHARACTER SET NONE COLLATE NONE,
CITTA VARCHAR (100) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
CODICELINEA VARCHAR (50) CHARACTER SET ISO8859_1 COLLATE
ISO8859_1,
ANNONASCITA VARCHAR (10) CHARACTER SET ISO8859_1 COLLATE
ISO8859_1,
SOURCE CHAR (1) CHARACTER SET ISO8859_1 DEFAULT 'F' COLLATE
ISO8859_1,
VLOCK SMALLINT DEFAULT 0,
STATO CHAR (1) CHARACTER SET ISO8859_1 DEFAULT 'A' COLLATE
ISO8859_1,
RESC_TIMESTAMP TIMESTAMP,
LOCK_TIMESTAMP TIMESTAMP);



/* Primary keys definition */

ALTER TABLE CUSTOMER_DATA ADD CONSTRAINT PK_CUSTOMER_DATA PRIMARY KEY
(ID_CUSTOMER);


/* Indices definition */

CREATE INDEX CUSTOMER_DATA_IDX1 ON CUSTOMER_DATA (VLOCK);
CREATE INDEX CUSTOMER_DATA_IDX2 ON CUSTOMER_DATA (STATO);
CREATE INDEX CUSTOMER_DATA_IDX5 ON CUSTOMER_DATA (RESC_TIMESTAMP);

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

/* Tabella: PHONE_CALLS */

CREATE TABLE PHONE_CALLS (
ID_CALL INTEGER NOT NULL,
ID_CUSTOMER INTEGER,
CALL_DATE DATE,
CALL_TIME_START TIME,
CALL_TIME_END TIME,
ATTEMPTS SMALLINT,
CALL_RESULT SMALLINT,
RESCHEDULE_DATE DATE,
RESCHEDULE_TIME TIME,
GSW_PHONE VARCHAR (30) CHARACTER SET NONE COLLATE NONE,
IDOP INTEGER,
NO_PROGRESS SMALLINT DEFAULT 0,
NOTE BLOB sub_type 1 segment size 100,
PERSONAL SMALLINT DEFAULT 0,
SEND_MAIL SMALLINT DEFAULT 0,
SEND_MI VARCHAR (1) CHARACTER SET NONE COLLATE NONE,
MAIL_ADDRESS VARCHAR (50) CHARACTER SET ISO8859_1 COLLATE
ISO8859_1,
QUEST SMALLINT DEFAULT 0,
FIGLI_TEL VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
INTERNET VARCHAR (10) CHARACTER SET NONE COLLATE NONE,
FASCIA_ORARIA VARCHAR (20) CHARACTER SET NONE COLLATE NONE);



/* Primary keys definition */

ALTER TABLE PHONE_CALLS ADD CONSTRAINT PK_PHONE_CALLS PRIMARY KEY
(ID_CALL);


/* Indices definition */

CREATE INDEX PHONE_CALLS_IDX1 ON PHONE_CALLS (ID_CUSTOMER);
CREATE INDEX PHONE_CALLS_IDX2 ON PHONE_CALLS (CALL_RESULT);
CREATE INDEX PHONE_CALLS_IDX3 ON PHONE_CALLS (ATTEMPTS);
CREATE INDEX PHONE_CALLS_IDX4 ON PHONE_CALLS (CALL_DATE);
CREATE DESCENDING INDEX PHONE_CALLS_IDX5 ON PHONE_CALLS (ID_CALL);
CREATE DESCENDING INDEX PHONE_CALLS_IDX6 ON PHONE_CALLS (ATTEMPTS);

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


any suggest ?

p.s. the DB is used by a Delphi Application developed on delphi and it
commit the query after the execution
p.s.2. the number of client that use db server are 130+