Report From ResultSet

[eolsyn]

Hi All,

The code I pasted below is querying records from a linked access table with and writing to a local alpha table. I am using the local table as a record set for a report. This script is running very slowly, approx 5 minutes to process 1000 records. Does anyone have any suggestions on how I might get this data to the report more quickly. I would be willing to eliminate the local table, just not sure how to go about it. The WHERE clause is populated by form variables.


Dim cn as sql::connection
'Opening a Connection
flag = cn.open("::Name::conBMSII")

'Executing a Command
dim sqlCommand as c

dim dtQueryStart as D
dtQueryStart = "01/01/" + year(dtStart)

sqlCommand = "Select Account, Date, Type, Reference, Amount, VendorID, Level1, Desc1, Level2, Desc2" \
", Level3, Desc3, Level4, Desc4, Level5, Desc5, Level6, Desc6 From vw_BudgetDetail" \
" WHERE Date >= #" + dtQueryStart + "#" \
" AND Account >= '" + strComboStartAcc + "' AND Account <= '" + strComboEndAcc + "'" \
" Order By Account, Date, Type"


flag = cn.execute(sqlCommand)

dim rs as sql::ResultSet
rs = cn.ResultSet

'The sql_ResultSet_preview() function is a built-in Xbasic function for getting a quick view of a ResultSet.

'sql_ResultSet_preview(rs)

'We are now positioned on the first row


dim strAccount as c
dim dblBudgetYTD as N
dim dblModificationsYTD as N
dim dblPaidYTD as N
dim dblEncumbranceYTD as N
dim dblPaidCurr as N


dblBudgetYTD = 0
dblModificationsYTD = 0
dblPaidYTD = 0
dblEncumbranceYTD = 0
dblPaidCurr = 0

dim ctr as N
ctr = 0
'We are now positioned on the first row

flag = rs.nextRow()
ctr = ctr + 1

while flag
'txt = txt + "Lastname: " + rs.data("Lastname") + crlf()
'when there are no more records in the ResultSet, executing .nextRow()
'will return .f., so flag will be .fl and the while loop will end

Select 'Case rs.data("Type")

Case rs.data("Type") = "AA" 'Adopted Amendments
dblModificationsYTD = dblModificationsYTD + rs.data("Amount")

Case rs.data("Type") = "AB" 'Adopted Budget
dblBudgetYTD = dblBudgetYTD + rs.data("Amount")

Case rs.data("Type") = "ABTF" 'Account Balance Transfer From
dblBudgetYTD = dblBudgetYTD - rs.data("Amount")

Case rs.data("Type") = "ABTT" 'Account Balance Transfer To
dblBudgetYTD = dblBudgetYTD + rs.data("Amount")

Case rs.data("Type") = "AJ" 'Adjustments
dblPaidYTD = dblPaidYTD + rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

Case rs.data("Type") = "AT" 'Adopted Transfers
dblModificationsYTD = dblModificationsYTD + rs.data("Amount")

Case rs.data("Type") = "BO" 'Budget Offsets
dblModificationsYTD = dblModificationsYTD + rs.data("Amount")

Case rs.data("Type") = "BR" 'Budget Refunds
dblPaidYTD = dblPaidYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr - rs.data("Amount")
End If

Case rs.data("Type") = "CAD" 'Contract Disbursements Allocated
dblPaidYTD = dblPaidYTD + rs.data("Amount")
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

Case rs.data("Type") = "CAR" 'Contract Refunds
dblPaidYTD = dblPaidYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr - rs.data("Amount")
End If

Case rs.data("Type") = "CC" 'Contracts Canceled
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

Case rs.data("Type") = "CO" 'Change Orders
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "CR" 'Contract Refund
dblPaidYTD = dblPaidYTD - rs.data("Amount")
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr - rs.data("Amount")
End If

Case rs.data("Type") = "DC" 'Disbursements by Check
dblPaidYTD = dblPaidYTD + rs.data("Amount")
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

Case rs.data("Type") = "EC" 'Contracts Encumbered
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "LB" 'Lapse Balance
dblModificationsYTD = dblModificationsYTD - rs.data("Amount")

Case rs.data("Type") = "OB" 'Opening Balance
dblBudgetYTD = dblBudgetYTD + rs.data("Amount")

Case rs.data("Type") = "PC" 'Purchase Orders Canceled
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "PO" 'Purchase Orders
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "PV" 'Purchase Orders Voided
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "PY" 'Payroll
dblPaidYTD = dblPaidYTD + rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

Case rs.data("Type") = "RF" 'Restricted Funds
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

'TA => Amend Temporary Budget & TB => Temporary Budget are calculated only when
'tranasaction date is in current fiscal year and adopted budget accounts using
'temporary budget
'TA => Amend Temporary Budget
'Case rs.data("Type") = Is = "TA" 'Temporary Budget Amendment
'If mblnUseTempBudget = True And rsAccounts!UAB = True Then
'If mdatYearBeginDate <= CDate(txtFromDate) And mdatYearEndDate >= CDate(txtFromDate) Then
'If mdatYearBeginDate <= CDate(txtToDate) And mdatYearEndDate >= CDate(txtToDate) Then
'Account_Amendment = Account_Amendment + rs.data("Amount")
'End If
'End If
'End If
'TB => Temporary Budget
'Case rs.data("Type") = Is = "TB" 'Temporary Budget
'If mblnUseTempBudget = True And rsAccounts!UAB = True Then
'If mdatYearBeginDate <= CDate(txtFromDate) And mdatYearEndDate >= CDate(txtFromDate) Then
'If mdatYearBeginDate <= CDate(txtToDate) And mdatYearEndDate >= CDate(txtToDate) Then
'Account_Budget = rs.data("Amount")
'End If
'End If
'End If

Case rs.data("Type") = "TEF" 'Transfer encumberence From
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

Case rs.data("Type") = "TET" 'Transfer encumberence To
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "VC" 'Contracts Voided
dblEncumbranceYTD = dblEncumbranceYTD + rs.data("Amount")

Case rs.data("Type") = "VD" 'Voided Disbursements
dblPaidYTD = dblPaidYTD + rs.data("Amount")
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

Case rs.data("Type") = "WT" 'Wire Transfers
dblPaidYTD = dblPaidYTD + rs.data("Amount")
dblEncumbranceYTD = dblEncumbranceYTD - rs.data("Amount")

If (convert_type(rs.data("Date"),"D") >= dtStart) .and. (convert_type(rs.data("Date"),"D") <= dtEnd) then
dblPaidCurr = dblPaidCurr + rs.data("Amount")
End If

End Select



dim tbl as p
tbl = table.open("tblBudget")
tbl.add_blank_records(1)
tbl.change_begin()

tbl.account = rs.data("Account")
tbl.lev1 = rs.data("Level1")
tbl.lev2 = rs.data("Level2")
tbl.lev3 = rs.data("Level3")
tbl.lev4 = rs.data("Level4")
tbl.lev5 = rs.data("Level5")
tbl.lev6 = rs.data("Level6")
tbl.desc1 = rs.data("Desc1")
tbl.desc2 = rs.data("Desc2")
tbl.desc3 = rs.data("Desc3")
tbl.desc4 = rs.data("Desc4")
tbl.desc5 = rs.data("Desc5")
tbl.desc6 = rs.data("Desc6")
tbl.transdate = rs.data("Date")
tbl.ytd_budget = dblBudgetYTD
'tbl.ytd_transfer = dblTransferYTD
'tbl.ytd_offset = dblOffsetYTD
'tbl.ytd_ammendment = dblAmmendmentYTD
'tbl.ytd_balancetransfer = dblBalanceTransferYTD
'tbl.ytd_refund = dblRefundYTD
tbl.ytd_modifications = dblModificationsYTD
tbl.ytd_paid = dblPaidYTD
tbl.ytd_encumbrance = dblEncumbranceYTD
tbl.curr_paid = dblPaidCurr

tbl.change_end(.t.)
tbl.close()

dblBudgetYTD = 0
'dblTransferYTD = 0
'dblOffsetYTD = 0
'dblAmmendmentYTD = 0
'dblBalanceTransferYTD = 0
'dblRefundYTD = 0
dblModificationsYTD = 0
dblPaidYTD = 0
dblEncumbranceYTD = 0
dblPaidCurr = 0

flag = rs.nextRow()

ctr = ctr + 1

if mod(ctr,1000) = 0 then
ui_msg_box("Row Count:","Record: " + ctr)
end if

end while

[Stan Mathews]

Re: Report From ResultSet

You might try it as

dim tbl as p
 tbl = table.open("tblBudget",FILE_RW_EXCLUSIVE)
 tbl.enter_begin()

 tbl.account = rs.data("Account")
 tbl.lev1 = rs.data("Level1")
 tbl.lev2 = rs.data("Level2")
 tbl.lev3 = rs.data("Level3")
 tbl.lev4 = rs.data("Level4")
 tbl.lev5 = rs.data("Level5")
 tbl.lev6 = rs.data("Level6")
 tbl.desc1 = rs.data("Desc1")
 tbl.desc2 = rs.data("Desc2")
 tbl.desc3 = rs.data("Desc3")
 tbl.desc4 = rs.data("Desc4")
 tbl.desc5 = rs.data("Desc5")
 tbl.desc6 = rs.data("Desc6")
 tbl.transdate = rs.data("Date")
 tbl.ytd_budget = dblBudgetYTD 
 'tbl.ytd_transfer = dblTransferYTD
 'tbl.ytd_offset = dblOffsetYTD
 'tbl.ytd_ammendment = dblAmmendmentYTD
 'tbl.ytd_balancetransfer = dblBalanceTransferYTD
 'tbl.ytd_refund = dblRefundYTD
 tbl.ytd_modifications = dblModificationsYTD
 tbl.ytd_paid = dblPaidYTD
 tbl.ytd_encumbrance = dblEncumbranceYTD
 tbl.curr_paid = dblPaidCurr

 tbl.enter_end(.t.) 
 tbl.close()

Actually you should probably open the table before the while loop and close it after.

dim tbl as p
tbl = table.open("tblBudget",FILE_RW_EXCLUSIVE)
while flag

'stuff here

end while
tbl.close()

[Ted Giles]

Re: Report From ResultSet

Alpha using SQL is notoriously slow.
To speed things up you could import the whole linked table as a dbf and then process it, or
Export from the Access table to a CSV or Excel (very quick) and import to Alpha and then process.

[DVSOL005]

Re: Report From ResultSet

Ted,
The actions as you describe, take time too!
Wouldn't it be better to use an active link table.

[Ted Giles]

Re: Report From ResultSet

Everything takes time Jo. As I said and have experienced, SQL over Alpha Linked tables is very slow.
An export from Access can be done with one click once it's set up. An import to Alpha just the same, and the data management script can be added to the import script so it's automatic.
At 5 mins/10000 recs, that's tedious and should probably be done overnight if you stick with the link and sql. I know it's only 0.3 secs per record, but that is a pain nowadays.

[GGandhi]

Re: Report From ResultSet

Forgive me if I am far off in another land.
You said linked access table in your first post and talking about active linked table with Ted, so you are using active linked tables as far as I can see. If this is correct why do you need to connect using sql connection, then convert to alpha table to produce a report? If I understand correctly the advantage of active linked tables are that you can simply open with table open method, correct? Then you can build report directly without converting to alpha table, am I not right?

If you are not using active linked tables, then you can query access table using connection property and create a report without an intermediary alpha table using sql data source as source for the report, that I do when I create a report with MySQL it is the linked table that I am not sure of. Let me if I am wrong in my assumption.

Edit: the first post and last but one are under two different user names, I mentioned as a single user.

[eolsyn]

Re: Report From ResultSet

Thank you all for your suggestions. I would like to eliminate my intermediary table (tblBudget), but I am unsure of the syntax to either send a filtered record set, based on an active link table, to my report or the syntax for using sql data source without the need for the intermediary table. Any code examples of either or both scenarios would be greatly appreciated. Thanks again.


PS - Basically, I am looking for a way to call the report from my form and pass it the form variables to be used as a report filter, but I am unsure of this syntax. I do not want to use the ask command as I have 2 populated combo boxes on my form.

[Stan Mathews]

Re: Report From ResultSet

Basically, I am looking for a way to call the report from my form and pass it the form variables to be used as a report filter, but I am unsure of this syntax.

report.PRINT( Layout_Name as C [, Filter as C [, Order as C [, Argument as SQL::Arguments ]]] )

Similar for preview.

[eolsyn]

Re: Report From ResultSet

Thanks All for he help, this is the solution that your suggestions created, I populated the report filter the report designer through the select records icon:
SelectRecords1.gifSelectRecords2.gif

Where ACCOUNT is a table field and strComboStartAcc and strComboEndAcc are both global variables populated on the form that calls the report. It populates the report much more quickly than the result set I had been using.

(ACCOUNT >= Var->strComboStartAcc ) .and. (ACCOUNT <= Var->strComboEndAcc )