[Progress Communities] [Progress OpenEdge ABL] Forum Post: FOR EACH NO-LOCK performance using client-server connection (impacted by available indexes)

[dbeavon]

I am trying to adjust my habits and begin writing client-server code that will perform well. I have always made "self-service" connections to the database in the past, and it seems I picked up bad habits that make for poor performance when ABL is using a client-server connection. One of the things I gathered from reading the following link... Progress KB - How to improve Client Server Performance ... is that there are optimizations that take place for special variations of FOR EACH NO-LOCK. These optimizations are realized the most when -Mm is configured to send higher number of records at once. I was starting with the simplest variations of "FOR EACH" that I could come up with, but even in the simple cases I'm not making sense of my observations. Consider the two loops below that are the same except for the where condition. /* ****************************************************************** */ /* Loop and fetch data */ /* ****************************************************************** */ FOR EACH prd_desc WHERE prd_desc.branch = p_BranchCode NO-LOCK: DEFINE VARIABLE v_Test AS CHARACTER NO-UNDO. v_Test = prd_desc.code-value. END. /* ****************************************************************** */ /* Loop and fetch data */ /* ****************************************************************** */ FOR EACH prd_desc WHERE prd_desc.job-type = p_JobType NO-LOCK: DEFINE VARIABLE v_Test AS CHARACTER NO-UNDO. v_Test = prd_desc.code-value. END. As it turns out, the second variation packs a *lot* more records into each round-trip than the first. I examined the table definition, and find that there is an index on job-type but not on branch. The table is relatively small (maybe 50,000 small rows in total.) And the number of results are about the same for both of the FOR EACH examples. My preference would be that the client-server "FOR EACH NO-LOCK optimization" should happen the same for both of these loops. They both should send over the max number of records per round-trip and perform equally well. Unfortunately this is not the case and the quantity of the network round-trips seems to be somehow dependent on the available indexes (only a theory). This index-dependent behavior was not stated explicitly in the KB article I mentioned above so I wanted to check and see if this theory makes sense to anyone else. In short, the second loop, (an index is available for the where condition) seems to pack a large number of records into its round-trips (it's only limited by the -Mm configuration). The first loop, however, seems to be make a lot more round-trips with fewer records in each. The number of records per round-trip is limited in some other mysterious way that does not seem to be based simply on the -Mm. Any feedback would be appreciated. If this is not clear then I can try to recreate in the sports database.

Continue reading...