I am having the database performance issues when accessing the database from a remote machine. I did some tests to check the performance and below are the results
If i am starting the database on my local machine and connecting through my machine using a remote connection (using -H and -S) then by running a query i can get the results back in 30 seconds. I start the database using the progress default configuration and no extra parameters excluding the -S and -H
When connecting to the same database running on my machine and accessing it from the other PC (Colleague System on same network) and running the same query its 3 times slower and takes around 1 min 40 seconds. I start the database using the progress default configuration and no extra parameters excluding the -S and -H
And when i run the query on my local machine and use the database hosted on the server that is on a different switch the query is more slow and takes 2 minutes and 50 sec. Here i have tuned the database and given the extra parameters like 24 GB of memory
I tried every thing but nothing seem to be improving the performance , I know its related to either virtual server or network but our infra team disagrees and need a proof so as to make any changes
The database size is around 250 GB and i have given 24GB as -B
Our CPU is Intel Xeon E5-2660 v3 @ 2.60 GHz 2.59 GHz ( 2 processors)
Server memory 64 GB
OS windows server 2008 R2 Enterprise
Progress 11.6 Enterprise
Thanks in Advance
Thank you all for your answers. I think I better be first concentrating over the query optimisation. Just in case you could point me to some white paper about the query optimisation would be really helpful and I would also investigate the Unicast messages send and received, but just wanted to know how can I use the information about these unicast messages to investigate the remote connection
Far more important than the number of packets sent back and forth is the number of network round trips.
I work full time from a place 7500km (~5000 miles) from the Bedford office. My ping to Bedford is 140-170ms.
In my extreme case OE client/server connections over vpn can be painfully slow.
The following benchmark demonstrates it (with -Mm 16384):
DEF VAR i AS INT NO-UNDO.
DEF VAR msg AS CHAR NO-UNDO FORMAT 'x(20)'.
i = ETIME(YES).
FOR EACH orderline NO-LOCK:
DISPLAY 'prefetch' @ msg ETIME(YES) @ i.
FOR EACH orderline FIELDS(ordernum linenum) NO-LOCK:
DISPLAY 'prefetch fields' @ msg ETIME(YES) @ i.
FOR EACH orderline NO-LOCK NO-PREFETCH:
DISPLAY 'no-prefetch' @ msg ETIME(YES) @ i WITH DOWN.
FOR EACH orderline FIELDS(ordernum linenum) NO-LOCK NO-PREFETCH:
DISPLAY 'no-prefetch fields' @ msg ETIME(YES) @ i WITH DOWN.
prefetch fields 4614
no-prefetch fields 2038000
The first is a simple FOR EACH orderline NO-LOCK, taking 7.8 seconds.
The second is a FOR EACH orderline NO-LOCK, taking 4.6 seconds, because 2 integer fields are sent.
NO-PREFETCH tells OE to send only one row at a time, waiting for the client to ask for the next one each time.
Notice that using FIELDS with NO-PREFETCH barely helps at all, cause what dominates is latency rather than the volume of information sent.
Notice in my extreme case it takes half an hour to read the orderline table without prefetch and still non trivial 7.8 seconds to read it with prefetch.
In your case you have a sub 1 ms latency, but let's assume your network latency is exactly 1 ms, if your processing is complex enough that it needs to wait for one million round trips, it would take a minimum of 1000 seconds to execute, because that time will be spent waiting on the network layer.
There are plenty of things you can do on the ABL that will need to wait for a round trip per record, the most common one is using locking. Any SHARE-LOCK or EXCLUSIVE-LOCK access will always imply NO-PREFETCH.
FIND statements always execute one row at a time.
Use the OpenEdge profiler to analyze execution times and find out which statement(s) are taking the longest and try to optimize those.
The -Mm parameter set to 16384 means OE can send up to 16KB at a time.
Sending a full 16KB as a single message will require 12 unicast packets each way, but will count as a single network round trip.
I suggest running this simple benchmark using your existing client/server machine pair and see how much different PREFETCH (default) vs NO-PREFETCH makes on your environment.
Sr Tech Support Engineer