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
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
Comment