I just tested a summary file generation function. It reads 567
records and summarizes them by GroupCode. Processing the file is
taking about 18 seconds on my development machine. The old SB3
version of that same routine takes 2.5 seconds on the same machine.
Now the old version uses REPORT ON.. GROUP...AFTER GROUP...AFTER
SELECT etc. and is very efficient. I also know I may have a major
problem in the my SIMPOL logic and I will be looking to optimize
that, but i wanted to ask if we can expect much performance
improvement in sbme file processing. I think I saw in the docs that
emptying a large sbme1table can be slower than deleting and recreating
a new sbme1file and associated table, but that should improve at some
point.
As a workaround, I may want to generate these summary files using SB3
logic and then serve them up via PPCS to the SIMPOL logic modules.
However this means having several programs running on a single
machine:
1. The SB3 main program
2. The PPCS program
3. SIMPOL modules
Two questions spring to mind with this approach:
a. How do I tell the PPCS server to unload a file and load a newly
generated copy?
b. How do I get feedback from the SIMPOL modules to the SB3 program
regarding user interaction on the SIMPOL dialogs or windows?
Any thoughts on that? Also can I launch/call a SB3 program from
SIMPOL? I have seen the docs for calling SIMPIL from SB3 , but don't
recall being able to reverse the relationship.
This performance issue is particularly important to me when I develop
my web-enabled products, where I will be displaying html pages with
summary data that must be generated on-the-fly.
|
Dan Jinks wrote:
> I just tested a summary file generation function. It reads 567
> records and summarizes them by GroupCode. Processing the file is
> taking about 18 seconds on my development machine. The old SB3
> version of that same routine takes 2.5 seconds on the same machine.
> Now the old version uses REPORT ON.. GROUP...AFTER GROUP...AFTER
> SELECT etc. and is very efficient. I also know I may have a major
> problem in the my SIMPOL logic and I will be looking to optimize
> that, but i wanted to ask if we can expect much performance
> improvement in sbme file processing. I think I saw in the docs that
> emptying a large sbme1table can be slower than deleting and recreating
> a new sbme1file and associated table, but that should improve at some
> point.
I would be very curious to see the code. The sbme tests that I have done
show single file access to be superior to Superbase single file access
by a wide margin.
> As a workaround, I may want to generate these summary files using SB3
> logic and then serve them up via PPCS to the SIMPOL logic modules.
> However this means having several programs running on a single
> machine:
> 1. The SB3 main program
> 2. The PPCS program
> 3. SIMPOL modules
>
> Two questions spring to mind with this approach:
> a. How do I tell the PPCS server to unload a file and load a newly
> generated copy?
Up to you really. You could spawn a server on a socket and use TCP/IP to
speak to it. You could just create a special database table and put
commands in it a records, etc.
> b. How do I get feedback from the SIMPOL modules to the SB3 program
> regarding user interaction on the SIMPOL dialogs or windows?
Depends on how they are called. If you call them as DLL calls, just use
the return value. Alternatively, you could use a common database table,
stuff everything in a small file, etc.
> Any thoughts on that? Also can I launch/call a SB3 program from
> SIMPOL? I have seen the docs for calling SIMPIL from SB3 , but don't
> recall being able to reverse the relationship.
I am unsure of this. We haven't really tested anything like this. It
probably is possible using !execute().
> This performance issue is particularly important to me when I develop
> my web-enabled products, where I will be displaying html pages with
> summary data that must be generated on-the-fly.
Web pages on our site get created very quickly, and we are using PPCS to
access the data.
|
I was able to make substantial improvements:
The slow functions are accululaterec() which also uses
acculatefld() and getfields()
The faster approach is accumulaterec2(), which does not use additional
functions. In this function, I also introduced a new array, aYearOn
that is either 1 or 0, but I do not think it causes any performance
issues). .
After the above changes, I am now able to build a summary file close
to the speed of my old SB3 REPORT-GROUP-SELECT logic. My grid build
reoutine is now the bottleneck. It uses some of the same techniques I
used earlier, particularly getfields(). I will concentrate next on
optimizing that code.
-Dan
function accumulaterec(sbme1record rSumm, sbme1record r, \
sbme1record rVwParms, boolean bLastRecord)
string s , sYN ,sFldName
integer i
array iOn
sbme1field fld
// Accumulate 12 fields together
i=1
s = ""
while i <=12
sFldName = "Losses_Pd_" + STR(i,"00")
accumulatefld(r,rSumm,sFldName,"Losses_Paid")
sFldName = "Expense_Pd_" + STR(i,"00")
accumulatefld(r,rSumm,sFldName,"Expenses_Paid")
sFldName = "Total_Incurr_" + STR(i,"00")
accumulatefld(r,rSumm,sFldName,"Total_Incurred")
sFldName = "Cur_Outstand_" + STR(i,"00")
accumulatefld(r,rSumm,sFldName,"Cur_Outstanding")
i = i + 1
end while
end function s
function accumulatefld(sbme1record rSource, sbme1record rTarget,
string sSourceFldName, string sTargetFldName)
number nTargetVal, nSourceVal, nTargetValNew
sbme1field fldSource, fldTarget
fldSource =@ getfields(rSource,sSourceFldName)
nSourceVal = rSource.get(fldSource)
fldTarget =@ getfields(rTarget,sTargetFldName)
nTargetVal = rTarget.get(fldTarget)
if nTargetVal == .nul or nTargetVal == .inf
nTargetVal = 0
end if
nTargetValNew = nTargetVal + nSourceVal
rTarget.put(fldTarget,nTargetValNew)
end function
function getfields(sbme1record r, string sFldName)
sbme1field fld
boolean bFldFound
string smyfld
fld =@ r.table.firstfield
while
// Find required field
smyfld = fld.name
if .like1(smyfld, sFldName)
bFldFound = .true
else
fld =@ fld.next
end if
end while ((bFldFound) or (fld =@= r.table.firstfield))
if not bFldFound
fld =@= .nul
end if
end function fld
function accumulaterec2(sbme1record rSumm, sbme1record r, sbme1record
rVwParms, array aYearOn)
string s , stemp
s = ""
rSumm!Losses_Paid = rSumm!Losses_Paid + \
(r!Losses_Pd_01 * aYearOn[01]) + (r!Losses_Pd_02 * aYearOn[02]) +
\
(r!Losses_Pd_03 * aYearOn[03]) + (r!Losses_Pd_04 * aYearOn[04]) +
\
(r!Losses_Pd_05 * aYearOn[05]) + (r!Losses_Pd_06 * aYearOn[06]) +
\
(r!Losses_Pd_07 * aYearOn[07]) + (r!Losses_Pd_08 * aYearOn[08]) +
\
(r!Losses_Pd_09 * aYearOn[09]) + (r!Losses_Pd_10 * aYearOn[10]) +
\
(r!Losses_Pd_11 * aYearOn[11]) + (r!Losses_Pd_12 * aYearOn[12])
rSumm!Expenses_Paid = rSumm!Expenses_Paid + \
(r!Expense_Pd_01 * aYearOn[01]) + (r!Expense_Pd_02 * aYearOn[02])
+ \
(r!Expense_Pd_03 * aYearOn[03]) + (r!Expense_Pd_04 * aYearOn[04])
+ \
(r!Expense_Pd_05 * aYearOn[05]) + (r!Expense_Pd_06 * aYearOn[06])
+ \
(r!Expense_Pd_07 * aYearOn[07]) + (r!Expense_Pd_08 * aYearOn[08])
+ \
(r!Expense_Pd_09 * aYearOn[09]) + (r!Expense_Pd_10 * aYearOn[10])
+ \
(r!Expense_Pd_11 * aYearOn[11]) + (r!Expense_Pd_12 * aYearOn[12])
rSumm!Total_Incurred = rSumm!Total_Incurred + \
(r!Total_Incurr_01 * aYearOn[01]) + (r!Total_Incurr_02 *
aYearOn[02]) + \
(r!Total_Incurr_03 * aYearOn[03]) + (r!Total_Incurr_04 *
aYearOn[04]) + \
(r!Total_Incurr_05 * aYearOn[05]) + (r!Total_Incurr_06 *
aYearOn[06]) + \
(r!Total_Incurr_07 * aYearOn[07]) + (r!Total_Incurr_08 *
aYearOn[08]) + \
(r!Total_Incurr_09 * aYearOn[09]) + (r!Total_Incurr_10 *
aYearOn[10]) + \
(r!Total_Incurr_11 * aYearOn[11]) + (r!Total_Incurr_12 *
aYearOn[12])
rSumm!Cur_Outstanding = rSumm!Cur_Outstanding + \
(r!Cur_Outstand_01 * aYearOn[01]) + (r!Cur_Outstand_02 *
aYearOn[02]) + \
(r!Cur_Outstand_03 * aYearOn[03]) + (r!Cur_Outstand_04 *
aYearOn[04]) + \
(r!Cur_Outstand_05 * aYearOn[05]) + (r!Cur_Outstand_06 *
aYearOn[06]) + \
(r!Cur_Outstand_07 * aYearOn[07]) + (r!Cur_Outstand_08 *
aYearOn[08]) + \
(r!Cur_Outstand_09 * aYearOn[09]) + (r!Cur_Outstand_10 *
aYearOn[10]) + \
(r!Cur_Outstand_11 * aYearOn[11]) + (r!Cur_Outstand_12 *
aYearOn[12])
end function s
|