Pervasive SQL 9.1
Windows 2000 SP 4 on Intel XEON 2ghz machine
I was having performance issues on this server on one particular query. I copied the entire database to a newer machine and had no problems. The query would run for up to 60 seconds on the older machine, in my web application it would of course time out. On the newer machine the query ran anywhere form instantly to 2 seconds.
The old machine only had 500 MB RAM so my first choice was to upgrade the RAM. So I added 2 GB to the old server.
Here is the problem. Now all of the queries run in about the same amount of time. 7-12 seconds. That is of course a big improvement on the one query I was having trouble with and that speed is almost acceptable. The bad thing is that the queries that ran instantly on the old RAM configuration now take as long as the slow one (7-12 seconds)
I monitored the database while accessing the website and the lag time is in the database or at least it is staying connected to the table while the wait time is happening.
My Processor load never goes over 57% during the queries. Some of these (now) slow queries are single row indexed selections
I have attached the configuration for performance tuning and memory usage. I have not adjusted them.