How can I optimise this code?

[oni29]

Hi,

Am gradually teaching myself VBA. Below is some of the code I've written. I'm looking for any suggestions around optimising it.

Private Sub cmd_Update_tblOrders_local_Click()

    Dim intButtonPressed As Integer
    
    ' Check that the user does want to proceed with this action
    intButtonPressed = MsgBox("This will add all new orders to the default project. Please press okay to proceed", vbOKCancel, "Assign new orders")
    If intButtonPressed = 2 Then GoTo Exit_cmd_Update_tblOrders_local_Click
    
    Dim con As ADODB.Connection
    Dim recSet As ADODB.Recordset
    Dim tempSet As ADODB.Recordset
    Dim localSet As ADODB.Recordset
    Dim strSQL As String
    Dim tempSQL As String
    Dim ordNum As String
    
    strSQL = "SELECT ORDER_NUMBER FROM tblOrders"
    Set con = CurrentProject.Connection
    Set recSet = New ADODB.Recordset
    Set localSet = New ADODB.Recordset
    Set tempSet = New ADODB.Recordset
    recSet.Open strSQL, con
    localSet.Open "tblOrders_local", con, adOpenDynamic, adLockOptimistic
        
    Do Until recSet.EOF
        ordNum = recSet.Fields("ORDER_NUMBER")
        tempSQL = "SELECT * FROM tblOrders_local WHERE ORDER_NUMBER = " & ordNum
        tempSet.Open tempSQL, con
        Debug.Print ordNum & " " & tempSet.RecordCount
        If tempSet.EOF Then
            varfields = Array("ORDER_NUMBER", "PROJECT_NUMER")
            varvalues = Array(ordNum, 2)
            localSet.AddNew varfields, varvalues
        End If
        tempSet.Close
        recSet.MoveNext
    Loop
    
    ' close the record set & connection objects
    recSet.Close
    localSet.Close
    con.Close
    Set recSet = Nothing
    Set con = Nothing
        
    MsgBox ("Its done!")

Exit_cmd_Update_tblOrders_local_Click:
    Exit Sub
    
Error_cmd_Update_tblOrders_local_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_cmd_Update_tblOrders_local_Click

End Sub

What it accomplishes is:

* I have two tables, tblOrders & tblOrders_local
* the contents of tblOrders are refreshed each day. The records for this table are extracted from an Oracle one via ODBC (and inserted via a SQL INSERT statement in Access)
* tblOrders_local contains the local order (information not listed in the Oracle database) that I want to store. Unfortunately I'm not allowed to add this information back into the Oracle database.
* the Sub checks if an order number in tblOrders exists in tblOrders_local. If a matching record isn't found in tblOrders_local, the Sub inserts one.

The Sub at the moment seems to work. I'm guessing the code can be cleaned up a lot though.

 

[modest]

First, the correct word is "optimize".

Next, please format your code so that it is easily readible. That is, tab sequential lines inside loops and conditional operators - this will be the first place to start so that others may help.

 

[boblarson]

First, the correct word is "optimize".

Next, please format your code so that it is easily readible. That is, tab sequential lines inside loops and conditional operators - this will be the first place to start so that others may help.

And that includes using the code tags here on the forum (used by typing the word CODE within two square brackets [] and at the end typing /CODE between two square brackets.

 

[modest]

And that includes using the code tags here on the forum (used by typing the word CODE within two square brackets [] and at the end typing /CODE between two square brackets.

Usually I would fix it for you, but my mod powers have disappeared, they should be back soon. An alternative to actually typing is to highlight the code and in advanced mode click the button that has the pound symbol (#). This will place the code tags for you.

 

[oni29]

First, the correct word is "optimize".

Next, please format your code so that it is easily readible. That is, tab sequential lines inside loops and conditional operators - this will be the first place to start so that others may help.

That is the way it is spelt in Australia =)

I have added the code tags into my post. That look any better?

 

[RuralGuy]

Thanks oni29. It looks *much* better! Now if someone would just suggest a way to optimise (sp) it.

 

[The_Doc_Man]

First and foremost, is there a particular reason why you think your code is not the best it can be?

Second, you are in essence running a find-unmatched query in order to insert the matchinq order records, if that is what I see.

VBA is not always the most efficient of all ways to optimize (I'm from USA so excuse the USA-English spelling) any code. I might have taken a different approach.

Add a yes/no field to the child table saying "corresponds to a parent."

Now build a query that does an update of all child entries for which a parent exists. For instance, the IN operator (as in, this value appears in this field in this table.) Or you could do a JOIN knowing that it would be sparsely matched so that relational integrity is not possible. In any case, the idea would be to mark the ones for which you DON'T have to generate anything. Then one more query - selecting the records NOT marked - tells you which records need parents.

The difference here is that I don't need VBA to do this and can let Access run optimized queries based on the SQL pre-processing that occurs for stored SQL. That is pretty efficient.

The catch is that since VBA is semi-compiled, not fully compiled, it is very hard to optimize it.

 

[modest]

First, let me start off by saying I agree with the DOC Man.

This whole thing can be done by the use of Queries, which will run faster than VBA. However if you're intent on optimizing the VBA, there is very little code here, which means it practically is optimized as far as it can go.

I would say there are a few tweaks that you might be able to make to your database rather than just the code.
- I think DAO might be faster than ADO in terms of data retrieval (someone check me on this).
- I would however use stored queries wherever is possible, for instance the "SELECT ORDER_NUMBER FROM tblOrders".
- I would also make sure the tables are indexed (especially on Order Number).
- Finally I would check the data type and make sure their sizes are acceptable.

 

[The_Doc_Man]

Agree with modest regarding speed of DAO vs ADO, use of stored query, having indexes on tables, validating compatibility of data types. All very good points. In fact, if you DON'T use a stored query with indexes and compatible data types, you might not get back that much from doing it via queries. The speed comes from the ability of the Access query processor to look at indexes (indices?) and make decisions about the best way to do the query.

 

[tehNellie]

I think DAO might be faster than ADO in terms of data retrieval (someone check me on this).

If your module is calling a table/query/recordset/anything from within the same Access database you should use DAO, if you're calling something external to Access (Excel, SQL server, MySQL, another Access Database not via a linked table etc), use ADO.

I'll offer this up as a suggestion/discussion, perfectly happy to have it shot down in flames as I've been watching this thread for a little while today and it's not been mentioned:

If you must nest queries (and I do a lot) possibly consider using a "temporary" table. I've found that the point at which performance nose dives varies from database to database in Access. Step through your queries, and at the point at which you are going to get a cup of coffee or browsing your favourite forum waiting for results, have the previous query output its results into a temporary table (call it tbltemp or something, it's not a "proper" feature in Access as far as I know) and then carry on your queries on the temporary table.

 

[modest]

Comments on Indexes:
If Access handles indexes like Oracle - I believe all the common databases use the same approach - then what the database does is store index information in a metatable. This metatable is usually in the data dictionary cache (I believe). Indexes are good because they spare the processor from performing large-table, full-table scans. Instead of loading the full table into memory, the database can just store the indexed column in a separate table, and retrieve the value's memory address much quicker (It's easier to search a 3 column table then 30 column table).

Comments on Temporary Tables:
I typically use temporary tables for queries that use multiple joins (especially when deleting). Temporary tables can dramatically speed up the database. I've went through and optimized some's multiple join query that took 15 minutes to perform, to between 30sec and a minute - just by using a temporary table.

Comments on DAO/ADO:
It used to be that ADO was the preferred choice of working with external applications. This mainly was because ActiveX was much easier to work with and provided much greater functionality - in certain cases, DAO was not even an option. Since v3.5, I believe DAO has been updated and is a greater contender. You can learn more about these updates from Microsoft.com, or posts that concern "Pat" as she has provided a wealth of knowledge on the changes.

Comments on the thread:
When I originally looked at the post, I was think you were doing something a little different, compared to me reading it now. This is more of a database optimization question. You must first answer a few questions:
How many records are in the oracle table?
How many records are updated in your local table?

These questions are important because how you want to handle the solution depends on what is being done. If you are only updating a few records or the table is large, you will want to make sure you are doing server-side processing. Otherwise, it might be quicker (and better for the network) to copy the Oracle table locally and then perform the searches. All the previous optimization expressed, still apply though.