performance problem

[forumuser7]

Hi,

We have a progress 9.1d application running on a Unix server. This server is mainly used for development and tests.
The application was running just fine fine for the past year or so..
We recently updated the programs and the database (we restored a backup from another server - production machine) and we noticed that it takes much longer now to browse a particular table.

The initial time for loading is much longer than before. (it takes 1 minute or so...) Any consecutive attempt to browse the same table takes only 1-2 seconds...
Even if we quit the application and start it again the time to load the table is 1-2 seconds...

We noticed that if we restart the database brokers then again the initial table browse would take longer than normal...

I know that:
1. the production server is a better (faster) machine than the development machine
2. The database has grown for the past year or so...is a little bit bigger now...

Is there a way to find out what is the problem?
Where is the bottleneck ?

Any ideas what could e the problem?
Thank you for the help in advance!

 

[BruceD Lindberg]

The index didn't change for that particular table during the update did it? Did they become Inactive?

 

[forumuser7]

The index didn't change for that particular table during the update did it? Did they become Inactive?

I don't know...
I'm not a progress programmer.
What can I do to verify if the index has changed or become inactive?

 

[Casper]

Hi,

The initial time for loading is much longer than before. (it takes 1 minute or so...) Any consecutive attempt to browse the same table takes only 1-2 seconds...

After the initial load, the table probably resides in shared memory. Maybe disk io problem?
What does sar say if you retrieve the records?

How many records are in that particular table?
If there are a lot ( say more then 500000) then do:

proutil <databasename> -C dbanalys and look for the scatter factor of that table. Checkalso for indexes in the dbanalys.

Is that particular table on a disk which is (heavily) used by other processes?

Only problems with this particular table? Does this table has it's own storage area?

Regards,

Casper.

 

[BruceD Lindberg]

for checking indexes

In the DBA area of the Progress Editor, in the drop down list for FILE is a report option, among those options is a report for indexes of the tables. A listing of a particular table will show the indexes and will say if they are active or not by the coding of letters on the left hand side. If an index is Inactive the code can't use that index.

to check if the elements of the index changed you have to compared the new listing to the old listing and see if there are any differences. If there are differences, that can effect the efficency of retrieving records.

 

[forumuser7]

Hello

Thank you fro the reply Casper!
To some of your questions:
-The problem exist for at least 2 different tables and only on the developement server.
I think that maybe it exists for all tables although I haven't verified that.

- I'd like to mention again that this problem doesn't exist on the other server
which is running the exactly the same SCO Unix version, the same version of progress and the same programs and database files.

-The server has only one disk, one partion and this disk is not being havily used by any other processes....
In fact I do not see the hard drive activity LED flashing during the initial load of the table. Seems like there is no HDD activity at all at that time!!!!

I have to verify what sar says, what is the size of the table(s) (the number of records, the index) and I'll post the info later on...

Thank you for the help Casper!

 

[forumuser7]

Bruce
I'll checkout the index friday and I'll post the info.

Thank you for your help!