D
dbeavon
Guest
George, thanks for the pointer to the VST's for _ActServer and _Connect. Using those numbers should be easier and more accurate than using wireshark. My first demonstration uses sports2000 on HP-UX with OE 11.6.3. I will also repeat the same test against a Windows server running OE 11.6.3. I will post the Windows results at another time. These tests use the orderline table; of which about 4000 records match the condition: "WHERE orderline.linenum = 1". For setting up the database to take my baseline measurements I do as follows: prodb mysports sports2000 -newinstance proserve mysports -Mm 32000 -B 3000 -L 50000 -d mdy -T /temp -S 5205 logs from startup: ============================= [2017/12/11@10:31:06.076-0500] P-20374 T-1 I BROKER 0: (16955) Delay first prefetch message (-prefetchDelay): Not Enabled [2017/12/11@10:31:06.076-0500] P-20374 T-1 I BROKER 0: (16956) Prefetch message fill percentage (-prefetchFactor): 0 [2017/12/11@10:31:06.076-0500] P-20374 T-1 I BROKER 0: (16957) Minimum records in prefetch msg (-prefetchNumRecs): 16 [2017/12/11@10:31:06.076-0500] P-20374 T-1 I BROKER 0: (16958) Suspension queue poll priority (-prefetchPriority): 0 ... [2017/12/11@10:31:06.076-0500] P-20374 T-1 I BROKER 0: (4239) Number of Database Buffers (-B): 3000 ... [2017/12/11@10:31:06.077-0500] P-20374 T-1 I BROKER 0: (12818) Message Buffer Size (-Mm): 32000 ============ Code: See George's test harness above. I use the same thing but insert the following ABL loop. Note that initially there should be no "perfect" index to assist with the filtering condition on linenum. However, the table should fit entirely into buffered memory after the first execution or two, and data should be retrieved very quickly from the database, especially if a self-service connection were to be used instead. FOR EACH orderline WHERE orderline.linenum = 1 NO-LOCK: END. Running this code a number of times will cause the data to be buffered into the memory of the database server. The network statistics from _ActServer will settle at the following values (between 210-240 messages in both directions). The number of records should always be the same (ie. 3953 which is based on the WHERE filter): │Messages receive: 238 │ │ Messages sent: 237 │ │ Bytes received: 25880 │ │ Bytes sent: 241661 │ │Records received: 0 │ │ Records sent: 3953 │ │Queries received: 237 │ │ Time slices: 3913 │ Now I add an index, use "proshut" to stop and then restart the database using the exact same configuration as before. And I recompile my program. This time the program will use the index on orderline.linenum. Nothing else should change other than the presence of this new index, and the compiler's preference to use it. Below is the index and the new results that are generated while running the program. CREATE INDEX "mylinenum" ON PUB."orderline" ("linenum" ASC ) PRO_DESCRIPTION '' PRO_ACTIVE 'y'; │Messages receive: 84 │ │ Messages sent: 83 │ │ Bytes received: 9232 │ │ Bytes sent: 226712 │ │Records received: 0 │ │ Records sent: 3953 │ │Queries received: 83 │ │ Time slices: 3953 │ Its pretty troubling to me that, depending on the indexes, there are three times the number of round-trips on the network. This is despite the fact that my code has not changed in any way. I had hoped that the "FOR EACH NO-LOCK" optimization would, in some way, batch my entire FOR EACH as a query that would be sent to the server and would receive consistent behavior from the database server no matter its internal implementation (especially if the whole operation can be performed using data that is buffered in memory). However, the behavior of the server doesn't appear to be consistent at all; and it depends on a variety of startup parameters, and unexpected factors such as the available indexes. My fear is that we will have yet *another* reason to keep expanding the list of indexes that we create on our tables. Many of the indexes we have on our tables are already highly questionable. It would be unfortunate to find ourselves adding even more indexes for no other reason than to influence the number of round-trips made to resolve the "FOR EACH NO-LOCK" queries. In the worst possible case, every FOR EACH loop on a given table may need to be evaluated to determine whether a special index would greatly decrease the network traffic. This client-"server" stuff could get out of hand...
Continue reading...
Continue reading...
