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

[ic2]

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

[PaulB]

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

[ic2]

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

[wriedmann]

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

[Terry]

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

[ic2]

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

[wriedmann]

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

[ic2]

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

[ic2]

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

[PaulB]

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