asked on

Function SQL Lag

Hi All,

I am having an issue with a function that performs an update of just 20,000 records inside an access 2002 database table.

The problem is, it lags significantly and in some cases it just hangs for hours on end and goes nowhere. If I run the SQL attached to the convertShippingCosts function directly in access query, it does not fail, however it still lags.

I have included the code I have written for the functions used to perform this update. Noting that the covertCurrencyFunction is shared accross the the entire application.

Can anyone help give me some ideas on how to speed it up?

Public Function convertShippingCosts()
On Error GoTo myErrorHandler
'CONVERTS ALL RATES INTO LOCAL CURRENCIES
'fields updated are:
    'a. Origin Port Service Charges
    'b. Shipping Cost
    'c. Spot Shipping Cost
    
    Dim rs As New ADODB.Recordset
    Dim Conn As Connection
    Set Conn = CurrentProject.Connection
    Dim strSQL As String
        
    strSQL = "UPDATE MT_SHIPPING_RATES SET MT_SHIPPING_RATES.ORIGIN_PSC = convertCurrency([ORIGIN_PSC],[DESTINATION_STATE_CODE],[CURRENCY_CODE_ORIGIN_PSC]), MT_SHIPPING_RATES.SHIPPING_COST = convertCurrency([SHIPPING_COST],[DESTINATION_STATE_CODE],[CURRENCY_CODE_SHIPPING_COST]), MT_SHIPPING_RATES.SPOT_SHIPPING_COST = convertCurrency([SPOT_SHIPPING_COST],[DESTINATION_STATE_CODE],[CURRENCY_CODE_SHIPPING_COST]), MT_SHIPPING_RATES.DEST_PSC = convertCurrency([DEST_PSC],[DESTINATION_STATE_CODE],[CURRENCY_CODE_DEST_PSC]), MT_SHIPPING_RATES.DOCUMENT_FEE_DEST = convertCurrency([DOCUMENT_FEE_DEST],[DESTINATION_STATE_CODE],[CURRENCY_CODE_DOC_FEE])"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    Set rs = Nothing
    
myErrorHandler:
If Err.Number = 0 Then
    Call addSourceUpdateLogFile("" & vUserID & "", 3, "Info! Converted all shipping costs into local currency in shipping rate table")
Else
    Call addSourceUpdateLogFile("" & vUserID & "", 6, "Error! Failed to convert all shipping costs into local currency in shipping rate table")
End If

Set rs = Nothing
End Function
Public Function convertCurrency(vAmount As Double, vStateCode As Integer, vFromCurrencyCode As String) As Double
'CONVERTS A COST INTO SPECIFIC CURRENCY
        
        Dim rs As New ADODB.Recordset
        Dim Conn As Connection
        Set Conn = CurrentProject.Connection
        Dim strSQL As String
        
        'PERFORMING VALIDATION ==>
        If (IsNull(vAmount) = True) Or (IsNull(vFromCurrencyCode) = True) Or (vAmount = 0) = True Then
            convertCurrency = 0
        Else
            'PERFORMING THE CONVERSION ==>
            strSQL = "SELECT MT_EXCHANGE_RATES.ExchangeRate FROM MT_EXCHANGE_RATES WHERE (((MT_EXCHANGE_RATES.StateCode)=" & vStateCode & ") " & _
            "AND ((MT_EXCHANGE_RATES.CurrencyCode)='" & vFromCurrencyCode & "'))"
            rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
            If rs.RecordCount > 0 Then
                rs.MoveFirst
                convertCurrency = vAmount / rs.Fields("ExchangeRate").Value
            Else
                convertCurrency = 0
            End If
        End If
    Set rs = Nothing
    
End Function

Open in new window

Patrick Matthews

Why bother with VBA for this at all? You can accomplish the same thing by simply writing an UPDATE query that refers to that MT_EXCHANGE_RATES table.

creativefusion

ASKER

Hi Patrick,

I have attached this to VBA as it is part of the application design which is a mult-user environment.

Each morning, the application runs a number of automated tasks for daily updates. If an update fails, an administrator is able to update the table using a command button form within a GUI that displays all the updates to each of the 100 odd tables.

CF

Patrick Matthews

OK, I can see using VBA to automate the query, and to do something if it fails. You still need not:

1) Use ADO nor
2) Use a UDF to handle the currency conversion

creativefusion

ASKER

Thanks.

Can you tell me what UDF stands for. Is it User Defined Function?

So, what is your suggestion here Patrick?

CF

Patrick Matthews

Yes, UDF is user defined function :)

I am suggesting handling the update using a straight SQL statement, with no UDF call. You could use VBA to run the query.

I'll need a smallish sample database to work on the SQL statement.

creativefusion

ASKER

Thanks. Enclosed is a small sample db for you.
test.mdb

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

creativefusion

ASKER

Hi Patrick,

Thanks so much for this. Just to confirm, you mentioned previously not to use ADO. What do I need here to remove ADO? Do I simply declare the strSQL as string only and then run the function that contains all three of these SQLs one after the other?

CF

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

creativefusion

ASKER

Excellent help as always. Thanks Patrick.

Patrick Matthews

Glad to help :)