VO DataServer for PostgreSQL using X# COM module

[wriedmann]

Hello,
since I'm working on a VO DataServer that works on PostgreSQL through an X# COM module (registration-free, so there is nothing to install on the client machines): is there any interest in this?
If yes, I can share it including the (XIDE) sources when it is finished and working.
I have started working on it because since the X# team has announced that XDS will be based on SQL backends and I need a something also in my own VO applications.
Wolfgang

[ic2]

Hello Wolfgang,

We are using SQL more and more over ADS but given the developments, including Robert's plan to support a native DBF-SQL kind of interface, your work may be very interesting too.

If you really don't mind sharing, I would be interested to at least do some tests with it!

Dick

[wriedmann]

Hi Dick,
I never mind share my generalized code, so when I have finished something I will post it here.
This server class is based on the arrayserver (when you read data, it is completely stored in the arrayserver), so it may behave sligthly different than the DBServer (in fact, it is inherited from the DataServer class and not completely compatible to the DBServer).
I'm thinking to build the sample app using the new Windows Forms based GUI classes....
Wolfgang

[ic2]

Hello Wolfgang,

Would that work with large databases, the arrayserver?

A customer recently told me that creating a balance (with 1 tab with all detail bookings for every GL account) took very long and lately eventually crashed. I found that we earlier defined a variable nMax to 50.000, the maximum number of element to add to an array to be transfered to Excel. I thought that this was not enough for their large amount of lines. But eventually I found that we defined this variable to move every nMax lines to Excel. So instead of increasing it, we had to bring it back to 1000. Now her balance worked and faster than before, because after 10.000's of added array elements the program turned out to come to a virtual stop and with 1000 a time it did not.

Dick

[wriedmann]

Hi Dick,
the approach for SQL servers is totally different from the approach of DBF files: for SQL you have to make a selection before accessing any data, whereas for DBF you can open the table, set an order and show some data.
To not make the the pgDataServer class subject to wrong user selections, the number of records is limited to a settable number of maximal records.
Unfortunately the array handling in VO suffers when the arrays become too large, and the same is true for string concatenation.
Wolfgang
P.S. regarding string concatenation: I had the following code to copy the ListView contents to the clipboard for Excel paste:

Code: Select all

for nI := nStart upto nLen
  oLVI := aItems[nI]
  for nColumn := 1 upto nColumns
    cValue := oLVI:GetText( aColumns[nColumn] )
    if IsDigitString( cValue )
      cValue := StrTran( cValue, cThousandSep, "" ) 
    endif
    if nColumn == 1
      cText := cText + CRLF + cValue
    else
      cText := cText + _chr( 9 ) + cValue
    endif
  next
next

Since it was becoming really slow (and CPU using a complete CPU Core), I had to change it:

Code: Select all

for nI := nStart upto nLen
  oLVI := aItems[nI]
  cLine := ""
  for nColumn := 1 upto nColumns
    cValue := oLVI:GetText( aColumns[nColumn] )
    if IsDigitString( cValue )
      cValue := StrTran( cValue, cThousandSep, "" ) 
    endif
    if nColumn == 1
      cLine := cLine + CRLF + cValue
    else
      cLine := cLine + _chr( 9 ) + cValue
    endif
  next
  cText := cText + cLine
next

[Meinhard]

Hi Wolfgang!

> cText := cText + cLine
I very often saw this type of construction in VO programs (even in former SDKs for example in CFtp). Be warned, this leads to an exponential consumption of dynamic memory ! At least you should regularly call the GC to clean up the memory, but this, of course, costs some time. In X#/C# one would use the StringBuilder class to collect string data. I remember solving this by build my own kind of string builder, allocating a buffer und using pointer stuff to add strings to the buffer. ANd if exceeding the allocated size, reallocate using more memory and copy the old content over to the new buffer ....

Regards
Meinhard

[wriedmann]

Hi Meinhard,
yes, you are completely right.
I had done some tests with a VO based stringbuilder class, but without using pointers it was slower than a normal string concatenation.
Maybe I have to spend some time on it, but since I hope to have more and more X# code (using the StringBuilder class obviously), I had decided to stop working on this class.
Wolfgang

[ic2]

Hello Wolfgang, Meinhard,

Thanks for the extra info. Often a programmer isn't aware of the execution speed of a program until it runs with lots of data for just 1 or 2 clients. Then it could be difficult to determine which changes could improve performance. I didn't know about the string builder class in X# (I can't find it in the help either, if you search you'll find "and the StringBuilder class section later in this topic." except there is no later in the topic). Where can I find more info? Knowing that, we can prevent using it, if it is not improving speed.

Bottom line is that large string & array operations are hard to optimize, I learn from this discussion.

Dick

[Meinhard]

Hi Dick,

its not an X# class it is inherited from the framework, take this as a starting point: https://docs.microsoft.com/en-us/dotnet ... mework-4.8

> Bottom line is that large string & array operations are hard to optimize, I learn from this discussion.

Won't say so, but ... the major problem here is, that most of the programmers don't care up front. They are, most of the time, only interested in making something work. And often they are lacking knowledge of how things are implemented. For a couple of years I stated during my conference sessions, that one of the problem with CLIPPER/VO/VULCAN/X# is, that things like string manipulation is to easy, so nobody thinks about the consequences of their code (most of the time: laughter). This would never happen to a C++ programmer, because memory management and performance issues are part of their DNA

Regards
Meinhard

[ic2]

Hello Meinhard,

I absolutely agree with you and I must admit that is what I do too, often. Until I learn e.g. that creating a balance which takes me 20 seconds takes 45 minutes for a client, partly due to the system slowing down with too many array elements. I think most programmers only then start thinking of how they could make it more efficient!

Dick