Problem with TCP connection and it works on shared memory

[Emiliano]

Hello everyone and thank you for your attention.

My company has a new customer and they have a database in progress. Client has progress 11.3 and 13 files por area 10 and 3 files por Indexes 20.

The client complains that heaviest queries take a long time when multiple heavy queris launched at the same time ( Bottleneck)

Analyze machine performance and everyhing is correct. Inside promon I didn't see anything that could be causing problems when the user launches those queries.
Change the database connection protocol from TCP to shared memory.

NCcoto101as=-db coto101as -ld cotoas -N TCP -H 10.178.1.1 -S atpcoto101as -->NCcoto101as=-db E:\bases\coto101as

The improvement was unbelievable heavy queries launched at the same from 2 hours to 20 minutos.

We tried another server with diferrent hardware ( less memory and CPU), operating system, antivirus, different network segment, but the same database and version of progress. The result was te same, so ruled out problems in the hardware server and Network card.

Message Buffer Size (-Mm) was increase, it wasn't solved the problem with TCP.
-prefetchFactor and -PrefecthNumRecs changed in promon but it not recored with started database only the test whitout restart database.


Soon it will change the architecture and everytyhing will not be on the same server and the shared memory configuration will not be valid.
I am not an expert in daba base or progress, please ¿Where could the problem be? ot ¿ What other test I can do?

Thanks a lot.

 

[TomBascom]

It would be helpful if you shared the code for a sample query as well as the indexes that are available for the tables which are involved.

-Mm and the -prefetch* parameters will not help unless your queries are properly coded. In particular they MUST be NO-LOCK queries. There are other coding options and paradigms that also have an impact - so seeing the code that you are using may suggest other improvements.

The effectiveness of the WHERE clause may also have a lot to do with the performance experience.

Beyond that there _may_ be database or client tuning options but you really need to get the query properly formed before those are likely to help much.

 

[TomBascom]

OpenEdge 11.3 is not doing you any favors either. 11.7.10 is the current OE 11 release and OE 12.2 is the most up to date long-term-support release.