The fastest way to update or add records

[bebubo]

Hi all,
I have a table with about 1,2million records. I get an Excel list of about 35000 weekly, using which the table has to be updated: the records, that exist get fields updated, the ones that are new have to be added.
What is the fastest way to do this?
I'm trying with this code, but it's awfully slow, even if I read the update ranges into memory:

    Set cn = CurrentProject.Connection
    Set Rs = New ADODB.Recordset
    Rs.Open "Select * from tbl_TTextract", cn, adOpenDynamic, adLockOptimistic
    
    i = 2
    Do While i < lr_2 + 1 'update
        With Rs
            On Error Resume Next 'if empty, .movefirst errors out
            .MoveFirst
            On Error GoTo 0
            'MsgBox .Fields("CustomerNr").Value
            .Find "[ID]='" & myupdaterng(i, 1) & "'"
            If (.BOF = True) Or (.EOF = True) Then 'not found
                .AddNew
                    .Fields("ID") = myupdaterng(i, 1)
					'etc, other fields
                .Update
            Else 'found -> update
                    .Fields("Status") = myupdaterng(i, 5)
					'etc, other fields
                .Update
            End If
            
        End With
        i = i + 1
    Loop

Any ideas? (I'm a beginner with Access.)

 

[namliam]

using update and insert queries will always be fster than using code

 

[bebubo]

Can you show an example please?

 

[namliam]

No not really... simplest way to play with it is to go into your database, go to the tab "Create"
Query wizard or query design

and see if you can make things happen

However, your code seems to be utilizing a spreadsheet in a subfunction updaterng???
You will first want to import your excel sheet into your database so you are "playing" with two tables.

 

[bebubo]

It has to happen at the push of a button.

So what you're suggesting is to read the whole Excel data into a table and run an executable query that uses this table to update the other one?

I didn't paste the whole code, the first part includes how the array myupdaterng is defined (opens an Excel App, does this and that and assigns the values in a certain range to this array.)

 

[namliam]

Yes I am suggesting to read the excel file into access table and run "action" queries (as they are called) to update and insert your records as required.

Does your myupdaterng open a (new) excel every time it is called? Then that is a place you can win a lot by opening the excel file only once and run thru it in one go.
I doubt however that your function would open a new excel session, get the value, close the file?

 

[bebubo]

No, it's a simple string array. The Excel is opened only once, read once, closed once.

I'll try your idea and come back if it won't do what I'd like. Thanks.

 

[bebubo]

Wow, 11 seconds for the whole thing, thanks again! Learned sthg new today too .

I can sort out the updateable tickets from the new ones, create two tables for these, then:

   strTable = "tbl_update"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, myPath, True, "update_tickets$"
    strTable = "tbl_import"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, myPath, True, "new_tickets$"

    t = Timer
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tbl_TTextract RIGHT JOIN tbl_update ON tbl_TTextract.ID = tbl_update.ID SET tbl_TTextract.Status = [tbl_update].[Status], ...other fields... ;"
    DoCmd.RunSQL "INSERT INTO tbl_TTextract ( ID, ...other fields... ) SELECT tbl_import.ID, ...other fields... FROM tbl_import;"
    DoCmd.SetWarnings True
    MsgBox "Updated in " & Timer - t & " seconds."

 

[namliam]

or you can just load them in one table and work from there, have access figure out what is new and what is existing...

Plus I dont really get the RIGHT JOIN, that should be INNER JOIN I think?

Remember to delete your temp table(s)... and remember that even after deleting them, the space isnt really made free untill you compact & repair.

 

[bebubo]

I couldn't get it faster done, if I loaded them into just one table, because then I'd have to loop through. I couldn't find a one-step SQL for that.
Right join: I need all from the new table but only update the matching ones from the existing. Is inner join different?
Deleting is already there, compacting is too.

 

[namliam]

It is still a 2 step SQL, one update and one insert, but it saves you the time of sorting thru if it is new or existing manually.

Inner join will require both tables to match on both sides, should return the same result... but in theory it should be (a little) faster.

Considering you have a growing master table, your compact will take longer and longer.
Temporary tables like this should be stored in a seperate (backend) database that will not grow huge and will be lightning fast to compact. All you then need to do is link the tables in this database.

 

[bebubo]

How would you do update or insert if it were all in one table?

 

[namliam]

By instead of doing a straight select from your tbl_import, you do a "unmatched" query
aka left join with your tbl_TTextract

Something like so:

FROM tbl_import
Left outer Join tbl_TTextract on tbl_import.id = tbl_TTextract.id 
Where tbl_TTextract.id  is null

Or (I believe this to be slightly less efficient) using a not in clause...

FROM tbl_import
Where tbl_import.id NOT IN ( select tbl_TTextract.id from tbl_TTextract )

Obviously this is the insert statement, for the update statement you would use an inner join (like you already are, or should be per my advice ) or use an IN clause instead of a NOT IN

 

[gemma-the-husky]

I think what namliam mentioned in #9 is that you can use a single query to both add new stuff and update existing - I tend to use 2 separate queries, though, as I am not familiar enough with the query structure for the single query.

The other thing that can cause problems is if you need to see what has changed in the existing records ...

 

[bebubo]

It's working perfectly with one table too .
I don't need to see the update dates luckily, just a current stand.

 

[namliam]

Dave, once TABLE, not ONE query.... One query for update and insert isnt possible as far as I know.

Another alternative may be to delete the incomming records from your target table and bulk insert all incomming.... but that is still two queries.