PDA

View Full Version : VDF/Webap 18A2



FrankValcarcel
26-Feb-2014, 09:55 AM
Whole lot of work done!!!! Way more than expected between A1 and A2.

Questions:

1. It would be nice if there were some images, and why they exist, for some of the new features in the doc like the Split Button, commandLinkButton and the pbShield for those of use who are slow. Maybe pointers to the examples...

2. Are the new functions - SQLFetchRowValues & SQLFetchResultsetValues the speed improvements that are noted. A definitive statement that these are faster than looping in DF would save test cycle. I'd believe you.

3. Why a new Timer class?

4. pbReadOnly is fantastic!

5. Our main App (StreamV & WebApp18A1) compile and run under A2.

fv

Bob Cergol
26-Feb-2014, 10:09 AM
FYI... In the Order Entry example there is a Demo Menu with a Button Samples option.
Bob

Martin Moleman
26-Feb-2014, 03:34 PM
2. Are the new functions - SQLFetchRowValues & SQLFetchResultsetValues the speed improvements that are noted. A definitive statement that these are faster than looping in DF would save test cycle. I'd believe you.
Besides the new functions SQLFetchRowValues and SQLFetchResultsetValues, the existing functions SQLColumnValue and SQLGetData should also be much faster.

Below are the results of one of our tests:


SqlOld.pkg
SQLColumnValue 47.0 sec
Sql.pkg
SQLColumnValue 5.8 seconds
SQLFetchRowValues 4.3 seconds
SQLFetchResultValues 4.1 seconds


This test was done on a result set of approximately 20.000 rows with 50 columns, so about 1 million columns retrieved in total.

The SQLColumnValue test is one where all column data is retrieved with SQLColumnValue. No code changes for existing code will be needed for this.
SQLFetchRowValues will return each row as an array of strings.
SQLFetchResultValues will return the complete result set as a 2 dimensional array.

If you have a chance to run some tests with the new sql.pkg, we would appreciate some feedback.

matthewd
26-Feb-2014, 06:09 PM
Martin,

I just did a simple query that returned all columns in a table: 256 columns * 61311 records = 15695616 values fetched.

Using GetSQLColumnValue:

17.1: 508.31 seconds, 30878 values/second (120 records/sec)

18.0: 85.119 seconds, 184360 values/second (720 records/sec)

Using SQLFetchRowValues:

18.0: 38.932 seconds, 403,154 values/second (1575 records/sec)

Very impressive!

I assume that the correct way to detect that the fetch is completed is to check the number of elements in the array that is returned, when it is zero, the fetch is done (as opposed to checking the return value from SQLFetch, which is no longer needed when using GetSQLFetchValue). EDIT: Nevermind, I saw there's the piFetchResult property for this.

However, using GetSQLFetchValue, I noticed that there is no date conversion done at all (no conversion from SQL military date/time format to DataFlex date-only format and no detection of dummy zero date value).

I added a line to SQL.PKG to do the date conversion:


Call_Driver 0 sDrvrId Function FUNC_SQLCOLUMNVALUE Callback 0 Passing sResult sEmpty iCol Result iVoid
If (aSQLColumns[iCol].iSQLType=SQL_TYPE_TIMESTAMP) Get SQLDateToDFDate sResult to sResult // convert dummy date to blank

Move sResult to asValues[iCol - 1]


And the same test took 52 seconds, 13 seconds more. That seemed like an awful large increase (33% longer to handle only 13 date fields/record) so I tried:


Call_Driver 0 sDrvrId Function FUNC_SQLCOLUMNVALUE Callback 0 Passing sResult sEmpty iCol Result iVoid
If (aSQLColumns[iCol].iSQLType=SQL_TYPE_TIMESTAMP) Get CLISQLDateToDFDate of hoCLIHandler (psDRiverID(Self)) sResult to sResult

Move sResult to asValues[iCol - 1]
Basically moving the code from SQLDatetoDFDate into the SQLFetchRowValues procedure, and the time went down to 49.5 seconds, a little bit of an improvement...
So then I tried this:

If (aSQLColumns[iCol].iSQLType=SQL_TYPE_TIMESTAMP) Begin
If (sResult=sDummyDateValue) Move '' to sResult
Else Begin
Set_Attribute DF_DATE_FORMAT to DF_DATE_MILITARY
Set_Attribute DF_DATE_SEPARATOR to (Ascii('-'))
Move sResult to dDfDate
Set_Attribute DF_DATE_FORMAT to iOrgDateFmt
Set_Attribute DF_DATE_SEPARATOR to iOrgDateSep
Move dDfDate to sResult
End
End
Basically moving the code in CLISQLDateToDFDate into SQLFetchRowValues, and the time went down to 45 seconds....

There might be some reasons to not do the date format conversion (although it should be noted in the docs as the behavior is different from SQLColumnValue), or to continue to use the existing procedures, but I thought this information might be helpful.

FrankValcarcel
26-Feb-2014, 06:55 PM
Very helpful.
May I ask why you did not try the option to bring back the whole result set in one call?

matthewd
26-Feb-2014, 07:23 PM
Frank,

I just forgot about testing that one; but the test I just ran for SQLFetchResultSetValues aren't that much different from SQLFetchRowValues, 39 seconds to fetch the entire result set.

I've got a switch now in my test program to select between the SQLColumnValue and SQLFetchRowValues to make it easier to test the same query with both methods. The result set is actually loaded into a tDataSourceRow array to go into a cCJGrid, so getting the result into a 2D string array isn't helpful in this situation. I don't know if loading SQL results directly into CJ grids is commonly done, but if so maybe it would be a good idea to add a third function to SQL.PKG that returns a tDataSourceRow array.

FrankValcarcel
26-Feb-2014, 08:27 PM
The quick way to test if it is needed (since you already have the rig setup) is to see how long it takes to move the data from the result set array to the tDataSourceRow.

Does sound like a good idea. Especially if DAC continues to enhance the tDataSourceRow then we won't have to do it.

Michael Mullan
26-Feb-2014, 08:41 PM
add a third function to SQL.PKG that returns a tDataSourceRow array.

+1

matthewd
26-Feb-2014, 08:47 PM
I forgot to point out that if DAW doesn't provide a function to fetch results to a tDataSourceRow, it is a piece of cake to simply subclass the cSQLStatement class and add your own based on SQLFetchResultsetValues.

In the test query that I ran, it added about 8 seconds (47 vs. 39) to copy the result set from a 2-D string array to a tDataSourceRow array.

FrankValcarcel
26-Feb-2014, 09:01 PM
I can not find the cSQLStatement class???
I also cannot find SQLFetchResultsetValues in any packages, I suspect it is a runtime call.

Stephen W. Meeley
26-Feb-2014, 09:05 PM
They are in sql.pkg. But only in 18.0 Alpha 2.

Martin Moleman
27-Feb-2014, 03:45 AM
Matt,

Thanks for the feedback.

I intended to look at the date conversion routines, but this did not make it into the Alpha 2. As you showed there is room for improvement there.

I am not sure SQLFetchResultSet should do date conversions. From a performance perspective it just takes time. As far as I know SQLColumnValue never did date conversions either.

Personally I prefer to do as much work as possible by the SQL server and let the SQL Query return dates in the right format.
For example:

Select
Order_Number
,Customer_Number
,Convert(varchar(10),Order_Date,101)
,Terms
,Ship_Via
,Ordered_By
,SalesPerson_ID
,Order_Total
,Last_Detail_Num
from OrderHea

The SQL convert function will return the date in US date format.

Dennis Kirkeby
27-Feb-2014, 10:04 AM
Some SQL test of mine:

Testing the new SQLColumnValue
Old SQL.pkg
5000 records - 14 columns: 3.198 sec.
new SQL.pkg
5000 records - 14 columns: 0.749 sec.

If I use the new SQLFetchResultsetValues:
5000 records - 14 columns: 0.281 sec.

A major performance boost!

Same test but with 100000 records and 14 columns.
old SQL.pkg
100000 records - 14 columns: 67.018 sec
new SQL.pkg
100000 records - 14 columns: 15.344 sec

With the new SQLFetchResultsetValues:
100000 records - 14 columns: 5.491 sec.


So the new SQLFetchResultsetValues is really interesting.

phvwijk
27-Feb-2014, 10:50 AM
Yes should be there.

Peter

matthewd
27-Feb-2014, 12:16 PM
As far as I know SQLColumnValue never did date conversions either.You are correct, I was mistaken. I stopped directly using SQLColumnValue (I put in an abstraction layer to get the SQL results that takes care of automatically calling the date function SQLDatetoDFDate on date fields) and forgot that I'd done this.

Your solution of using CONVERT to get the date in the correct format is much better, I'm going to start using that instead.

EDIT: This may be even better:


CREATE FUNCTION DummyZeroDate() returns varchar(10)
AS
BEGIN
return '01/01/1753'
end
go

CREATE FUNCTION SQLtoDFDate(@date datetime) RETURNS varchar(10)
AS
BEGIN
RETURN (CASE when convert(varchar(10),@date,101) = dbo.DummyZeroDate() then ''
else convert(varchar(10),@date,101) end )
END
GOThen you can do this:


select dbo.SQLToDFDate(date_term), acct_num, first_name, last_name from prempl;

And get have the server handle the format conversion as well as the blank date detection.

FrankValcarcel
27-Feb-2014, 12:28 PM
OK, found it. Problem was that when I upgraded from 18A1 to 18A2 and did the search files it picked up the old paths from 18A1 (which I think it should not have)
fv

Focus
27-Feb-2014, 04:49 PM
At last ;) That's more like it. We are now in the same division as other products. What did you find ? .... just curious