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:
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.
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.
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:
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)
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.
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:
To add one more sample which IMO means that ADS SQL is totally not working:
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
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?
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:
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 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