C
cverbiest
Guest
Hi , at a customer site we have the strange issue that a client-server query is much slower (factor 50) for a particular value. I'm reading the first 100 records for a value. That value is the first field of the table's primary index. To exclude buffering issues (db buffering is at 99%) I repeat the same query 3 times (loop value). for value 5 this takes about 1.5 seconds for all other values it takes less than 50 milliseconds. numeric decimal point is comma (,) in the tables below value loop milliseconds count size size / time 1 1 42,00 100 60.285,00 1.435,36 1 2 38,00 100 60.285,00 1.586,45 1 3 39,00 100 60.285,00 1.545,77 3 1 42,00 100 73.777,00 1.756,60 3 2 39,00 100 73.777,00 1.891,72 3 3 39,00 100 73.777,00 1.891,72 4 1 37,00 100 56.757,00 1.533,97 4 2 37,00 100 56.757,00 1.533,97 4 3 28,00 100 56.757,00 2.027,04 5 1 1.436,00 100 89.057,00 62,02 5 2 1.408,00 100 89.057,00 63,25 5 3 1.394,00 100 89.057,00 63,89 6 1 31,00 100 69.513,00 2.242,35 6 2 32,00 100 69.513,00 2.172,28 6 3 34,00 100 69.513,00 2.044,50 9 1 143,00 100 80.258,00 561,24 9 2 132,00 100 80.258,00 608,02 9 3 129,00 100 80.258,00 622,16 If I change the index to another index that starts with value I don't have a big difference between the values 1 1 30,00 100 61.489,00 2.049,63 1 2 33,00 100 61.489,00 1.863,30 1 3 32,00 100 61.489,00 1.921,53 3 1 37,00 100 67.944,00 1.836,32 3 2 33,00 100 67.944,00 2.058,91 3 3 31,00 100 67.944,00 2.191,74 4 1 42,00 100 56.761,00 1.351,45 4 2 40,00 100 56.761,00 1.419,03 4 3 40,00 100 56.761,00 1.419,03 5 1 40,00 100 61.852,00 1.546,30 5 2 34,00 100 61.852,00 1.819,18 5 3 29,00 100 61.852,00 2.132,83 6 1 37,00 100 66.280,00 1.791,35 6 2 32,00 100 66.280,00 2.071,25 6 3 37,00 100 66.280,00 1.791,35 9 1 53,00 100 64.801,00 1.222,66 9 2 41,00 100 64.801,00 1.580,51 9 3 46,00 100 64.801,00 1.408,72 If I run my test procedure on a shared memory connection I don't see a big difference either. /* test code */ define query qart for artikel. define variable lstart as datetime no-undo. define variable lcount as integer no-undo. define variable lsize as decimal no-undo. define variable ltime as decimal no-undo. define variable llimit as integer no-undo init 100. define variable ap-artikel as integer no-undo init 1. define variable lloop as integer no-undo. def var rawrecord as raw no-undo. &if defined(winheightchars) = 0 &then &global-define winheightchars 30 &endif &if defined(winwidthchars) = 0 &then &global-define winwidthchars 160 &endif &if defined(winfont) = 0 &then &global-define winfont 8 &endif current-window = default-window. assign default-window:row = 1 default-window:column = 1 default-window:width-chars = {&winwidthchars} default-window:height-chars = {&winheightchars} default-window:font = {&winfont} . form with frame f-down down width-chars {&winwidthchars} font {&winfont}. output to "clipboard". view frame f-down. aploop: do ap-artikel = 1 to 9: do lloop = 1 to 3: lstart = now. lcount = 0. lsize = 0. open query qart for each artikel where artikel.ap-nr = ap-artikel use-index artikel1. get first qart . do while avail artikel and lcount = llimit THEN do: ltime = interval(now, lstart, "milliseconds"). disp ap-artikel lloop ltime label "milleseconds" lcount lsize (lsize / ltime) with frame f-down. down with frame f-down. END. else next aploop. end. end.
Continue reading...
Continue reading...