Hi,
We are converting our application to run against an Oracle database and are having some performance issues. These are largely related to 'find first' statements, and I know that using a 'for first' would speed up our application, but we are hoping to not have to rewrite huge amounts of the code.
The issue we have can best be demonstrated with 3 query statements - a 'find first' on our Product table, a 'for first' on Product using identical fields, and a 'find first' on a different table in the db. All the statements are retrieving a single record using the primary unique index on the table. Using the etime function we have timed the statements and the first find first statement takes about 0.2 seconds, while the other two statements take no more than a millisecond. We have used an Oracle tool to analyse what Oracle is doing and all three queries are using the correct index, and are only returning a single record. According to Oracle all three queries are also returing the record in less than a millisecond. However, from the Oracle analysis tool after the first find first statement for some reason Oracle is having to wait 0.2 seconds (i.e the entire time that Progress thinks the query is taking) for a reply from the Progress dataserver. We are totally confused about the difference between the two tables using 'find first' statements as we can't really see why there would be any difference. The tables are similar in size, have a similar number of records in the db (about 1000). The Product table has more indexes, but I tried removing all indexes except the primary unique one and that made no difference.
We are running all this with the Oracle db and Progress Oracle dataserver on one machine, and me running the Progress queries on a different networked pc linking directly to the dataserver. If we use Appserver, and put the Appserver on the same machine as the Oracle db then the first find first statement no longer takes 0.2 seconds, and instead returns the data with the same sort of time frame as the other queries. Our customer's setup is that they have one machine with the Oracle db, then a second server with the Progress dataserver and app-server on, and the client machines then connect to the app-server. This setup returns similar results to our setup in the office of not using app-server - i.e. a find first on Product takes 0.2 seconds.
Anyone got any ideas how we can improve performance without rewriting vast chunks of code?
Thanks,
Andrew.
We are converting our application to run against an Oracle database and are having some performance issues. These are largely related to 'find first' statements, and I know that using a 'for first' would speed up our application, but we are hoping to not have to rewrite huge amounts of the code.
The issue we have can best be demonstrated with 3 query statements - a 'find first' on our Product table, a 'for first' on Product using identical fields, and a 'find first' on a different table in the db. All the statements are retrieving a single record using the primary unique index on the table. Using the etime function we have timed the statements and the first find first statement takes about 0.2 seconds, while the other two statements take no more than a millisecond. We have used an Oracle tool to analyse what Oracle is doing and all three queries are using the correct index, and are only returning a single record. According to Oracle all three queries are also returing the record in less than a millisecond. However, from the Oracle analysis tool after the first find first statement for some reason Oracle is having to wait 0.2 seconds (i.e the entire time that Progress thinks the query is taking) for a reply from the Progress dataserver. We are totally confused about the difference between the two tables using 'find first' statements as we can't really see why there would be any difference. The tables are similar in size, have a similar number of records in the db (about 1000). The Product table has more indexes, but I tried removing all indexes except the primary unique one and that made no difference.
We are running all this with the Oracle db and Progress Oracle dataserver on one machine, and me running the Progress queries on a different networked pc linking directly to the dataserver. If we use Appserver, and put the Appserver on the same machine as the Oracle db then the first find first statement no longer takes 0.2 seconds, and instead returns the data with the same sort of time frame as the other queries. Our customer's setup is that they have one machine with the Oracle db, then a second server with the Progress dataserver and app-server on, and the client machines then connect to the app-server. This setup returns similar results to our setup in the office of not using app-server - i.e. a find first on Product takes 0.2 seconds.
Anyone got any ideas how we can improve performance without rewriting vast chunks of code?
Thanks,
Andrew.