fbpx
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC: Speed consideration VO & codeblocks vs X# and IF's

Speed consideration VO & codeblocks vs X# and IF's 10 months 1 week ago #5842

  ic2's Avatar Topic Author ic2 Offline Posts: 499
For some of our clients creating a balance takes 5 minutes because they have a lot of journal entries. This is done in VO, creates an array to be exported to Excel, using DBFCDX (via ADS) and it checks some criteria with codeblocks like this:

lBlok := {||Eval(lBlok1).AND.Eval(lBlok2).AND.Eval(lBlok3).AND.Eval(lBlok4)}

I wonder if I can get this done faster (read:considerably faster) by:

1 replacing the codeblocks by series of IF statements
2 running the same DO..WHILE to build up that Excel file via an X# program.

If 5 minutes get 4:50 then it's not worth the effort.

Any thoughts?

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 10 months 1 week ago #5843

  PaulB's Avatar PaulB Offline Posts: 15
Some thoughts:

1. Make sure the calling order of the evals are optimized to give the best performance.
2. Reorganize some of the evals() to execute as ADS optimized filters.
3. Reorganize the balance code to run as ADS Sql Queries. Most or all could be done by the Server.

Cheers,

Paul Bartlett

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 10 months 1 week ago #5845

  ic2's Avatar Topic Author ic2 Offline Posts: 499
Hello Paul,

Thanks for the considerations. I'll give that a try. Especially the SQL queries may speed things up considerably compared to a DO..WHILE.

I'll let you know the result in due time.

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 10 months 1 week ago #5848

  wriedmann's Avatar wriedmann Away Posts: 1573
Hi Dick,

by changing a VO serial read to an ADS select statement I was able to speed up something that took several minutes to read. After the change to the ADS select it takes a few seconds.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 10 months 1 week ago #5856

  Terry's Avatar Terry Offline Posts: 127
Hi Dick
My advice would be do it.

On the face of it, it looks to me, as it obviously does to you, that it could be beneficial. But you can’t be sure unless you do it and actually measure the relative timings. But what to measure may not be as obvious as it seems. The complexities of what is going on “behind the scenes” and the exact details of your program make it impossible to predict exactly how much time you would be shaving off the process.

Gut-feel is probably right, but may be wrong. It is the only thing you have to go on.

So, my advice to “just do it” is not based on probabilities.
It is based on Roslyn.
Roslyn (about 10 years in the making) gives us a re-imagined compilation process. A process that itself, can be interrupted and programmed.
The fact that the compilation process itself is wholly deterministic, means that it can be interrupted by 3rd party tooling (or your own), without reference to program code, in order to stream line underlying program operation.
Taking this a bit further, it also means that, however complex, convoluted, or whatever, its basic concept of operation can be changed automatically under tight computer control.

In that way a Roslyn based .Net program is effectively made FUTURE PROOF.

This future-proofing, obviously applies to XSharp. But the mechanisms outlined can only apply to those elements of your program that are actually written in X#.
Clearly there is no realistic way of applying these mechanisms to any bits of your program written in VO, Vulcan, Clipper etc.
So IMO, bringing as much of you code into X# is guaranteed to help in future-proofing your program and therefore worth the time and effort.

Terry

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5947

  ic2's Avatar Topic Author ic2 Offline Posts: 499
Thanks everyone for the replies.

From Wolfgang's reply I expect the highest speed gain from the ADS SQL statements. So an X# conversion is only a 2nd option, although if it speeds up a factor twice (instead of the factor 100 of the ADS SQL query) it would be a big gain already.

It seems however that ADS has trouble with some of the SQL syntax. This works and sums all journal entries (field:#amt), + or - based on debit or credit (field #debcred), very quickly:

select jpost.account as Account,sum(CASE WHEN jpost.debcred='D'THEN jpost.amt ELSE jpost.amt*-1 END) as total from jpost left join desc on jpost.account=desc.ledger where year(jpost.datein)=2018 group by jpost.account

I added a join to a table (desc.dbf) from which I want to include the GL description. This works fine with a statement like this:

select jpost.account as Account,desc.description from jpost left join desc on jpost.account=desc.account

But as soon as I am going to use GROUP or SUM, adding the very same field from the joined table as above:

,desc.description

leads to the following error in ADS:

poQuery: Error 7200: AQE Error: State = HY000; NativeError = 2196; [SAP][Advantage SQL Engine]Column not found in GROUP BY clause: description SELECT or HAVING clause.

I've tried many combinations and orders of the statements but no luck; it accepts the join statement but doesn't let me include/select any fields of desc.dbf

Any idea why?

Second problem is that the sum total should start with a begin balance total from another database on the same field #Account. I found a construction like this:

SELECT (SELECT SUM(amt FROM JPOST) + (SELECT SUM(amt) FROM beginbal)) as result
but also here ADS already starts to protest on the 2nd select.

So I am not sure I can use ADS SQL to create e.g. a spreadsheet with complex conditions and lookups in other tables while summing totals for the same field from 2 different databases (each with their own condition: like the current year's entries from the journal dbf and the Jan 1 values from the begin balance dbf)

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5948

  wriedmann's Avatar wriedmann Away Posts: 1573
Hi Dick,

IMHO ADS does not supports all the SQL features, specially when it comes to subselects. These are depending also on the SQL dialect - some things that will work on Oracle will not work on MySQL or on SQL server.

So split up the queries in blocks that ADS can understand and build the data then in your VO code.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5954

  ic2's Avatar Topic Author ic2 Offline Posts: 499
Hello Wolfgang,

I think the ADS SQL option is great but underdeveloped. I've had many compatibility issues and now after lots of experimenting it seems that I can add one option but I get an error when I add the second and the other way around. E.g. I can show data from 2 databases with a JOIN but then the WHERE clause doesn't work. Or the SUM line. I think my query is built the same way as the ones in chapter 14 of the ADS developers guide but I keep getting errors. So I suspect that it's simply buggy.

Now my next idea is to work out the query into my (now) dbf reading routines (hoping it leaves some of the speed gain...). But I don't know how to do that (only done that using the VO SQL classes).

I have a result SELF:oSQLServer (cursor I think it's called) back from my query and I can easily show that in a bBrowser like this:

SELF:oDCBrowser:ServerType:=#SQL
SELF:oDCBrowser:Use(SELF:oSQLServer)

Do you perhaps have any suggestion how I read the values of the recordfields of this query in VO/X#?

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5959

  ic2's Avatar Topic Author ic2 Offline Posts: 499
To add one more sample which IMO means that ADS SQL is totally not working:

This works:

select j.keynr , sum(CASE WHEN j.debcred='D'THEN j.amt ELSE j.amt*-1 END) from jpost j group by 1

and as soon as I one (existing) field, it doesn't matter which:
select j.keynr , j.debcred, sum(CASE WHEN j.debcred='D'THEN j.amt ELSE j.amt*-1 END) from jpost j group by 1

I get

ERROR IN SCRIPT: poQuery: Error 7200: AQE Error: State = HY000; NativeError = 2196; [SAP][Advantage SQL Engine]Column not found in GROUP BY clause: debcred in SELECT or HAVING clause.

How could column 1 in the GROUP BY now 'not found' by just inserting 1 field??
Interesting enough I get the same Group by error if I remove Group by! Or it suddenly works if I make it:
select j.keynr,j.debcred from jpost j
(without group by).

Does anyone using ADS recognize the illogical errors?

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5960

  PaulB's Avatar PaulB Offline Posts: 15
Dick,

Just copy the SQL results to a DBF file, then open that DBF with bBrowser:

FUNCTION CopySQLToDBF(nhCursor,cTargetFile)
LOCAL nResult AS DWORD

nResult := AdsConvertTable ( nhCursor,ADS_RESPECTFILTERS,Cast2Psz(cTargetFile), ADS_CDX )

RETURN nResult

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5962

  wriedmann's Avatar wriedmann Away Posts: 1573
Hi Dick,

you are right: ADS does not fully implement the SQL language, but also other SQL databases are doing this.
Therefore you need to simplify your statement as possible until it works, and do the rest in your code, maybe using an array or an intermediate DBF table, if the data becomes too large.

To read ADS by SQL, there is a class ADSSQLServer. You can use it like this:
AX_SetConnectionHandle( oApp:hADSHandle )
oServer := ADSSQLServer{ cSelect,,, "AXSQLCDX" }
oServer:GoTop()
while ! oServer:EoF
  _cUniqueID := AllTrim( oServer:FieldGet( #UniqueID ) )
  oServer:Skip()
end
oServer:Close()

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5966

  robert's Avatar robert Offline Posts: 1001
Dick,

(As far as I know) in all SQL languages, you need to include the colums from the SELECT clause that are not aggregate calculations into the group by clause. Try this:

select j.keynr , j.debcred, sum(CASE WHEN j.debcred='D' THEN j.amt ELSE j.amt*-1 END) from jpost j group by j.keynr , j.debcred

I am not sure what you wanted to achieve in the first query:

select j.keynr , sum(CASE WHEN j.debcred='D'THEN j.amt ELSE j.amt*-1 END) from jpost j group by 1

How many rows does that return? What does the "group by 1" do ?
I would expect the following group by clause:
group by j.keynr

devzone.advantagedatabase.com/dz/webhelp..._group_by_clause.htm

Robert
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

Last edit: by robert.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #5969

  ic2's Avatar Topic Author ic2 Offline Posts: 499
Hello Robert,

YES! That's it; thank you so much. I thought I checked for the specific error in all my documentation but did not find the one you sent as a link do ADS Devzone. Indeed it works and once you know the error message seems to explain it enough;).

What I want to achieve is a quick overview from journal entries: General Ledger from jpost, description of the GL from a description database and the total amount booked taking into debit or credit. This now works.

@Paul, Wolfgang: I can already present that in a bBrowser with just the few lines I showed (without the need to convert it first to DBF) but I may need to have access it via DBF or otherwise; that depends a bit on what I can do more with the SQL statement. I will check your solutions for that.

Fact is that many 1000's of journal records indeed return the required table with totals in seconds instead of in minutes.

I'll let you know the end result!

Dick

Please Log in or Create an account to join the conversation.

Speed consideration VO & codeblocks vs X# and IF's 9 months 3 weeks ago #6025

  ic2's Avatar Topic Author ic2 Offline Posts: 499
I added an extra option in Financial Reporting of our program to create a quick overview using an SQL statement via ADS. We already had a window to run ADS SQL queries, assign these to a bBrowser which can export the content to Excel and use that.This works, as Wolfgang predicted, very fast. I may use the suggestions to use the SQL cursor as a faster start to make the current reports as well. The browser data output has some limitations compared to the existing report but for now as a fast alternative it is adequate.

For future readers I translated below some of the fields/data in the query I use now which Robert helped me finalize. The query below sums all amounts (+ for debit,minus for credit) within the date range from journal entries (jpost.dbf) and add the last end balance amounts for balance accounts (subbal.dbf). The output is the GL number, the description from a description database (coded "RS" as type of description) and the total of the 2 values. It's still in our VO based program but I think this option will have the highest impact on the customer's speed experience for now.

select reknr as account, desc as description, sum(total) as amount from (select j.gl,sum(CASE WHEN debcred='D' THEN amount ELSE amount*-1 END) total from jpost j where j.datein>='2017-01-01' AND j.datein<='2017-12-31' group by j.gl UNION all select s.gl,sum(CASE WHEN LEFT(gl,1)>='4' then 0 WHEN debcred='D' THEN amount ELSE amount*-1 END) total from subbal s where s.datein='2015-12-31' group by s.gl) t join desc on gl=desk.glnr and desc.typeofdesc='RS' group by gl,desc

Please Log in or Create an account to join the conversation.

  • Page:
  • 1