>You mention 50000 records in the "header" table etc... do you mean by this
>that you want to drag all these records back to the client everytime they

do

>something?

No, of course not.

>I would really be looking at your design if I were you.

It seems like this, the problem is these delays are too rare. The
application runs fast 99% and on 1% makes these delays, so it seems to be
not problem as is works good most of the time and bad sometime. On server I
see that the disks is working hard and not the cpu. So I think that deleting
'unwanted' records we can have no delays.



I made a small test connection program, it runs a loop opening and close the
database and does some same queries. Checking the log file of this program,
the average time for each one loop was about 5 seconds. On 20 loops of 650
of total, the same test (the same queries) took >10 seconds.



Dennis



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alan McDonald
Sent: Wednesday, September 27, 2006 1:39 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] how to reduce the file size of the database
after large amount of deletes ref/eDN8072730811

> Well, the hole story is this.
>
> It is a trade application with sales with pos machines.
>
> When the database came bigger than 300mb, there are some lags during the
> day, in precise 5-10 seconds, 3 times per day, per pos machine. We can say
> that this is normal for a sql based application but too bad for pos
> machines, 5 seconds it is too much. It is about 50000 records on header
> table and 200000 on details table.
>
> For this reason I made 2 databases (files) in the same branch
> (shop). One is
> the 'main' of the branch and the other one database file 'pos' for pos
> machines, these 2 databases are synchronized successfully!
>
> Now, what I want is to delete the movements from pos database and reduce
> with someway the database file to actual filesize according the records
> where holds. So the pos machines will run better as the database is almost
> empty.
>
> What do you think?
> Regards
> Dennis

if your indexes are set up correctly, I would be surprised if you cannot
make the db perform as fast as an empty database with no indexes.
I tell my users that if they ever get the impression that my application is
slow, then it's my fault, not the fault of the amount of data they want to
keep.
You mention 50000 records in the "header" table etc... do you mean by this
that you want to drag all these records back to the client everytime they do
something? I would really be looking at your design if I were you. You're
right, a 5 second delay is intolerable but a reasonable design (both data
design and front-end design) should eliminate any sense of delay.
Alan





[Non-text portions of this message have been removed]