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

creativefusionAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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.
creativefusionAuthor Commented:
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 MatthewsCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

creativefusionAuthor Commented:
Thanks.

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

So, what is your suggestion here Patrick?

CF
Patrick MatthewsCommented:
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.
creativefusionAuthor Commented:
Thanks. Enclosed is a small sample db for you.
test.mdb
Patrick MatthewsCommented:
In order to make the most of indexes you may have set up, I would have you create the following three queries, and then use VBA to call those queries:


UPDATE MT_SHIPPING_RATES INNER JOIN 
    MT_EXCHANGE_RATES ON (MT_SHIPPING_RATES.CURRENCY_CODE_ORIGIN_PSC = MT_EXCHANGE_RATES.CurrencyCode) AND 
    (MT_SHIPPING_RATES.DESTINATION_STATE_CODE = MT_EXCHANGE_RATES.StateCode) 
SET MT_SHIPPING_RATES.ORIGIN_PSC = [MT_SHIPPING_RATES]![ORIGIN_PSC]/[MT_EXCHANGE_RATES]![ExchangeRate], 
    MT_SHIPPING_RATES.SHIPPING_COST = [MT_SHIPPING_RATES]![SHIPPING_COST]/[MT_EXCHANGE_RATES]![ExchangeRate], 
    MT_SHIPPING_RATES.SPOT_SHIPPING_COST = [MT_SHIPPING_RATES]![SPOT_SHIPPING_COST]/[MT_EXCHANGE_RATES]![ExchangeRate], 
    MT_SHIPPING_RATES.DEST_PSC = [MT_SHIPPING_RATES]![DEST_PSC]/[MT_EXCHANGE_RATES]![ExchangeRate], 
    MT_SHIPPING_RATES.DOCUMENT_FEE_DEST = [MT_SHIPPING_RATES]![DOCUMENT_FEE_DEST]/[MT_EXCHANGE_RATES]![ExchangeRate]
WHERE (((MT_EXCHANGE_RATES.ExchangeRate)>0));

Open in new window


UPDATE MT_SHIPPING_RATES INNER JOIN 
    MT_EXCHANGE_RATES ON (MT_SHIPPING_RATES.CURRENCY_CODE_ORIGIN_PSC = MT_EXCHANGE_RATES.CurrencyCode) AND 
    (MT_SHIPPING_RATES.DESTINATION_STATE_CODE = MT_EXCHANGE_RATES.StateCode) 
SET MT_SHIPPING_RATES.ORIGIN_PSC = 0, 
    MT_SHIPPING_RATES.SHIPPING_COST = 0, 
    MT_SHIPPING_RATES.SPOT_SHIPPING_COST = 0, 
    MT_SHIPPING_RATES.DEST_PSC = 0, 
    MT_SHIPPING_RATES.DOCUMENT_FEE_DEST = 0
WHERE (((MT_EXCHANGE_RATES.ExchangeRate)=0));

Open in new window


UPDATE MT_SHIPPING_RATES LEFT JOIN 
    MT_EXCHANGE_RATES ON (MT_SHIPPING_RATES.CURRENCY_CODE_ORIGIN_PSC = MT_EXCHANGE_RATES.CurrencyCode) AND 
    (MT_SHIPPING_RATES.DESTINATION_STATE_CODE = MT_EXCHANGE_RATES.StateCode) 
SET MT_SHIPPING_RATES.ORIGIN_PSC = 0, 
    MT_SHIPPING_RATES.SHIPPING_COST = 0, 
    MT_SHIPPING_RATES.SPOT_SHIPPING_COST = 0, 
    MT_SHIPPING_RATES.DEST_PSC = 0, 
    MT_SHIPPING_RATES.DOCUMENT_FEE_DEST = 0
WHERE MT_EXCHANGE_RATES.ExchangeRate Is Null;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
creativefusionAuthor Commented:
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
Patrick MatthewsCommented:
I had more in mind the Execute method, as described by long-time Access MVP Allen Browne:

http://allenbrowne.com/ser-60.html
creativefusionAuthor Commented:
Excellent help as always. Thanks Patrick.
Patrick MatthewsCommented:
Glad to help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.