Speed of Access with 2000i SP3

[proacct]

I am working on a PSQL DB that has 10 users and many transations processed throughout the day. The database size is around 3 Gig total. This is an accounting system, and many tables are at 100 meg in size.

I have reviewed the configuration, and we are using recommended configurations. Are there server and workstation parameters that affect the speed of processing more than others?

Thanks,

ProAcct

 

[Vancouverite]

The main Server parameter that I have seen affect processing speed is the cache size.

Under Performance Tuning on the server configuration, the Cache Allocation Size controls how much memory Pervasive reserves for an internal cache. Especially if the server runs for multiple days between reboots, this can speed up your access... the more memory allocated, the better the access.

You should allocate between 25% and 40% of the server's physical memory as cache if possible. NOTE: If your server is an NT/2000 box, an interaction between PSQL and the OS limits the amount of memory that can be successfully allocated to about 640Mb.

If your cache can be >= total file size, you get your best speed increase. However, every bit of memory that you can allocate can help. Our DB is > 19Gb, and we can only allocate about 800Mb of memory on our server, but the difference between that allocation and our original 100Mb allocation is very clear.

Other speedups: Use TCP/IP if possible. Only specify the communication protocol(s) that you actually use, both on the server and on the clients. Ensure that you have sufficient communications threads pre-allocated (it's more efficient) -- with 10 users, you should only need 2 or 3 threads allocated ahead of time.

In hardware, use SCSI drives, not IDE, if possible. Increase your server RAM. Make sure that your network connections are clean.

In DB Design, ensure that you actually have the indexes on the tables you need. If you do a lot of lookups on some combination of data that is not indexed (e.g. ClientID and Last Date Billed), add an index for that combination.

Hope this helps.